vendredi 24 juillet 2015

SQL - Creating a Grouped 'range' set

I have a table of support tickets - with time opened and time closed. I would like to create a table of ranges, as such:

 ticket count   |    time to close 
----------------------------------
        30      |        up to 2 hours
        25      |         2 - 4 hours
        10      |         4 - 6 hours

what i have so far gives me the range (using a CASE with DATEDIFF), but i cant figure out how to group the eventual range. When trying to GROUP on the new openTimeRange computed column, the error of course is that its an unknown column.

SELECT COUNT([tblTickets].*),  DATEDIFF(hh,[dateOpened],[closeDate]) AS OpenTime

, case when  DATEDIFF(hh,[dateOpened],[closeDate]) between 0 and 2 then '0-2'
     when  DATEDIFF(hh,[dateOpened],[closeDate]) between 3 and 4 then '3-4'
     when  DATEDIFF(hh,[dateOpened],[closeDate]) between 5 and 6 then '4-6'
end as openTimeRange

FROM  [tblTickets]
WHERE closeDate is not null 
GROUP BY  [dateOpened],[closeDate] 

Using MSSQL 2005 SP4

Thanks!

Aucun commentaire:

Enregistrer un commentaire