I have to sort, by hour, the number of times someone calls from a specific country. The list of countries increases on a monthly basis, so for example we can add Brazil. I am using SQL Server.
The Data Looks like This
2012-04-02 08:00:59 United States
2012-04-02 08:12:02 United States
2012-04-02 08:13:42 Canada
2012-04-02 08:13:56 United States
2012-04-02 08:14:07 Mexico
2012-04-02 08:18:09 Canada
2012-04-02 08:19:50 United States
2012-04-02 08:34:34 Mexico
etc.
How I would like to list the data is by top 2 countries by hour.
I would like it to display like:
Date Country Calls
2012-04-02 08:00:00 United States 24
2012-04-02 08:00:00 Canada 19
--hidden--
2012-04-02 08:00:00 Mexico 12
The Code that i tried (does not work):
Declare @StartDate datetime, @EndDate datetime
set @StartDate = '20120401 00:00:00'
set @EndDate = '20120430 23:59:59'
SELECT DATEADD(HOUR, DATEPART(HOUR, [date]), DATEDIFF(DAY, 0, [date])) as [date],
(SELECT COUNT([country]) FROM [mytable] WHERE [date] between @StartDate and @EndDate and [country] = 'United States' ) as [United_States]
,(SELECT COUNT([country]) FROM [mytable] WHERE [date] between @StartDate and @EndDate and [country] = 'Canada' ) as [Canada]
,(SELECT COUNT([country]) FROM [mytable] WHERE [date] between @StartDate and @EndDate and [country] = 'Mexico' ) as [Canada]
FROM [mytable]
WHERE [date] between @StartDate and @EndDate
GROUP BY DATEADD(HOUR, DATEPART(HOUR, [date]), DATEDIFF(DAY, 0, [date]))
ORDER BY [date]
Thank You.
No comments:
Post a Comment