Click here to Skip to main content
15,850,103 members
Home / Discussions / Database
   

Database

 
GeneralRe: Query columns by name in stored procedures (SQL Server 2005) Pin
Tobias Schoenig25-Jun-07 21:00
Tobias Schoenig25-Jun-07 21:00 
GeneralRe: Query columns by name in stored procedures (SQL Server 2005) Pin
vimal_yet25-Jun-07 22:28
vimal_yet25-Jun-07 22:28 
GeneralRe: Query columns by name in stored procedures (SQL Server 2005) Pin
Tobias Schoenig26-Jun-07 0:10
Tobias Schoenig26-Jun-07 0:10 
GeneralRe: Query columns by name in stored procedures (SQL Server 2005) Pin
Tobias Schoenig26-Jun-07 20:20
Tobias Schoenig26-Jun-07 20:20 
AnswerRe: Query columns by name in stored procedures (SQL Server 2005) Pin
Arun.Immanuel25-Jun-07 22:00
Arun.Immanuel25-Jun-07 22:00 
Questionsolution wen server goes down Pin
zareee25-Jun-07 18:12
zareee25-Jun-07 18:12 
AnswerRe: solution wen server goes down Pin
originSH25-Jun-07 23:18
originSH25-Jun-07 23:18 
QuestionQuery from Hell Pin
Hulicat25-Jun-07 15:12
Hulicat25-Jun-07 15:12 
After getting much help from Michael and Andy I managed to tweak this thing to meet the requirements....However, I need to group by an addition column..since I have never done it this way I have floundered for a few days before asking for help with this.

I can make it work in a different query just not this one.

I need to group by an addditonal field named:
problem_type_name in the Problem_type table which is tied to the job_ticket.problem_type_id

This worked in the other query I used and then added in the "group by"

INNER JOIN
problem_type p
ON (p.problem_type_id = j.problem_type_id)


Here is the working query that I cant add p.problem_type_name to "group by"
I have the inner join there and it will execute fine....I just need to know how to get it into the group by with location_name...I have been trying with no success...

Thanks to all who have helped thus far; I am learning and hopefully I will be able to contribute to the forum @ some point.


SELECT
Total ,
[Total Remain Open],

(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id

AND
report_date between @startdate and @enddate) AS [Total Opened],
(SELECT
COUNT(*)
FROM
dbo.job_ticket
WHERE
location_id = Source.location_id
AND status_type_id like '[3,4,5]'
AND
report_date between @startdate and @enddate) AS [Total Closed],
Client,
[Average days open]
FROM
(SELECT
l.location_id,
l.location_name AS 'Client',
COUNT(*) AS 'Total',
SUM(CASE
WHEN status_type_id LIKE '[1,2,6,7]' AND
report_date between @startdate and @enddate THEN 1
ELSE 0
END) AS 'Total Remain Open',
AVG(datediff(d, report_date, close_date)) AS [Average Days Open]
FROM
job_ticket j
INNER JOIN
location l
ON (l.location_id = j.location_id)
INNER JOIN
problem_type p
ON (p.problem_type_id = j.problem_type_id)
WHERE
report_date BETWEEN @startdate AND @enddate
GROUP BY
l.location_id,
l.location_name) AS Source

Regards,
Hulicat

QuestionMySQL or SQL Server 2005 Pin
kani9825-Jun-07 13:45
kani9825-Jun-07 13:45 
AnswerRe: MySQL or SQL Server 2005 Pin
Christian Graus25-Jun-07 14:24
protectorChristian Graus25-Jun-07 14:24 
GeneralRe: MySQL or SQL Server 2005 Pin
Paul Conrad25-Jun-07 14:52
professionalPaul Conrad25-Jun-07 14:52 
AnswerRe: MySQL or SQL Server 2005 Pin
originSH26-Jun-07 0:27
originSH26-Jun-07 0:27 
QuestionDateTime Pin
seemamltn25-Jun-07 11:28
seemamltn25-Jun-07 11:28 
AnswerRe: DateTime Pin
Colin Angus Mackay25-Jun-07 12:32
Colin Angus Mackay25-Jun-07 12:32 
QuestionIs it possible to access a row in a datatable in constant time? Pin
Ryan Andrus25-Jun-07 10:34
Ryan Andrus25-Jun-07 10:34 
AnswerRe: Is it possible to access a row in a datatable in constant time? [modified] Pin
Colin Angus Mackay25-Jun-07 10:46
Colin Angus Mackay25-Jun-07 10:46 
GeneralRe: Is it possible to access a row in a datatable in constant time? Pin
Ryan Andrus25-Jun-07 11:29
Ryan Andrus25-Jun-07 11:29 
GeneralRe: Is it possible to access a row in a datatable in constant time? Pin
Arun.Immanuel25-Jun-07 22:05
Arun.Immanuel25-Jun-07 22:05 
GeneralRe: Is it possible to access a row in a datatable in constant time? Pin
Ryan Andrus26-Jun-07 4:46
Ryan Andrus26-Jun-07 4:46 
GeneralRe: Is it possible to access a row in a datatable in constant time? Pin
Ryan Andrus26-Jun-07 6:08
Ryan Andrus26-Jun-07 6:08 
QuestionHow to upload a picture in the database & how to make the search Case sentive Pin
Bahaa Hany25-Jun-07 7:40
Bahaa Hany25-Jun-07 7:40 
AnswerRe: How to upload a picture in the database & how to make the search Case sentive Pin
Colin Angus Mackay25-Jun-07 7:53
Colin Angus Mackay25-Jun-07 7:53 
AnswerRe: How to upload a picture in the database & how to make the search Case sentive Pin
Dave Kreskowiak25-Jun-07 8:14
mveDave Kreskowiak25-Jun-07 8:14 
QuestionNeed help on table design Pin
astv25-Jun-07 7:20
astv25-Jun-07 7:20 
AnswerRe: Need help on table design Pin
Colin Angus Mackay25-Jun-07 7:48
Colin Angus Mackay25-Jun-07 7:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.