Dying on this query

Hulicat

Hi All, 1st off what is the best T-SQL book I can get for a novice? obviously I have much to learn. If I run the query below as is I get this: Msg 8120, Level 16, State 1, Line 12 Column 'job_ticket.LOCATION_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Okay so I add it to the "group by" and it still does not work. If I removed the piece in bold the query executes, but does not break down by "client". Also the AVG should be the AVG of @startdate and @enddate; I know it's incorrect now but I cant get it to work the way I need it to. I needed help to get where I am now....Thanks Roy Table Job_ticket Status_type_id varchar Location_id varchar Table Location Location_ID varchar Location_name varchar SELECT Opened, [Total Open at Start], [Total Open at End], [Total Open at End] - [Total Open at Start] as [Total Closed], Client, [Average days open] FROM (SELECT count(*) AS 'Opened', SUM(CASE WHEN status_type_id LIKE '[1,2,6,7]' THEN 1 ELSE 0 END) AS 'Total Open at Start', (select count(*) from dbo.job_ticket as b where j.location_id = b.location_id and status_type_id like '[3,4,5]' and last_updated between @startdate and @enddate) AS 'Total Open at End', l.location_name AS 'Client', AVG(datediff(d,report_date,getdate() )) AS [Average days open] FROM job_ticket j JOIN location l on l.location_id = j.location_id WHERE (report_date BETWEEN @startdate AND @enddate) GROUP BY l.location_name) as X

Regards, Hulicat

Krish KP

report_date is from which table? also not getting what you are exactly trying for Is that want location wise 1. count number of rows falls in a date range 2. count number of rows having status_type_id 1, 2, 6 or 7 3. count number of rows having status_type_id 3, 4 or 5 is it some thing like this

Regards KP

andyharman

I think what you're after is:

SELECT L.location_name AS Client,
   TotalOpenAtStart,
   TotalOpenAtEnd,
   (TotalOpenAtEnd - TotalOpenAtEnd) AS TotalClosed,
   AvgDaysOpen
FROM location l
LEFT OUTER JOIN (
   SELECT j.location_id,
      SUM(CASE WHEN j.status_type_id IN (1,2,6,7) THEN 1 ELSE 0 END) AS TotalOpenAtStart,
      SUM(CASE WHEN j.status_type_id IN (3,4,5) THEN 1 ELSE 0 END) AS TotalOpenAtEnd,
      AVG(DATEDIFF(d, j.report_date, GETDATE())) AS AvgDaysOpen
   FROM job_ticket j
   WHERE j.report_date BETWEEN @startdate AND @enddate)
   GROUP BY j.location_id
) x
ON x.location_id = l.location_id
ORDER BY L.location_name

Regards Andy

Hulicat

Thanks Andy it does not like the group by "Msg 156, Level 15, State 1, Line 19 Incorrect syntax near the keyword 'GROUP'." If it helps the report_date is in the job_ticket table datetime. thanks for taking the time to help me...It's greatly appreciated. I tried tweaking it a bit and no luck........ SELECT L.location_name AS Client, TotalOpenAtStart, TotalOpenAtEnd, (TotalOpenAtEnd - TotalOpenAtEnd) AS TotalClosed, AvgDaysOpen FROM location l LEFT OUTER JOIN ( SELECT j.location_id, SUM(CASE WHEN j.status_type_id IN (1,2,6,7) THEN 1 ELSE 0 END) AS TotalOpenAtStart, SUM(CASE WHEN j.status_type_id IN (3,4,5) THEN 1 ELSE 0 END) AS TotalOpenAtEnd, AVG(DATEDIFF(d, j.report_date, GETDATE())) AS AvgDaysOpen FROM job_ticket j WHERE j.report_date BETWEEN @startdate AND @enddate) GROUP BY j.location_id ) x ON x.location_id = l.location_id ORDER BY l.location_name

Regards, Hulicat

Michael Potter

I am sure there is a better way but, without knowing your data - try this:

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

Hulicat

Thanks Michael, I got this when I ran it: Msg 207, Level 16, State 1, Line 14 Invalid column name 'Total Open at End'. Msg 207, Level 16, State 1, Line 15 Invalid column name 'Total Open at End'. I tried to troubleshoot it with no luck.... I really appreciate the help. Regards

Regards, Hulicat

andyharman

Remove the bracket that follows the @enddate parameter.

Hulicat

Thanks I got it to execute, however the math did not come out correct. Although, I think I got enought here to figure it out from here. Thanks to everyone that replied and helped.

Regards, Hulicat

Michael Potter

Whoops, that is the problem with not having data to test with. Try this:

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

Hulicat

Bingo!!! muchos gracias

Regards, Hulicat