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