Friday, April 20, 2012

SQL Server - Show Top entries in List

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