I have written a query that selects the data based on log time. It is as follows:
SELECT DISTINCT method, COUNT(method),
WHERE log_time IN ( SELECT log_time FROM applog WHERE DATEPART(YEAR,log_time) = '2009' AND DATEPART(MONTH,log_time) = '07' AND DATEPART(DAY,log_time)='06' AND DATEPART(HH,log_time)='12')
GROUP BY method
The result of a single query is
METHOD, COUNT(METHOD) where hour = 12
What I want is:
METHOD, COUNT(METHOD) where hour = 12, COUNT(METHOD) where hour =13, etc..
Where the leftmost method column has all the method names in the log and if a particular method is not called in the time frame of the select a '0' is inserted for that method in the count.
How do I script this?
I know that I will need to loop on: DATEPART(HH,log_time)='12'), but I don't know how to do the rest.