CODEWITHSUNDEEP
sqlsql-server
Brad
Brad

Reputation: 635

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.

Upvotes: 4

Views: 239

Answers (6)

daniloquio
daniloquio

Reputation: 3902

Try this. Maybe there are some syntax errores, but I think it goes on the right direction.

SELECT T1.Date, T1.Country, SUM(1)
FROM CallsTable T1
GROUP BY DATEADD(hh, DATEDIFF(hh,0,T1.Date),0), T1.Country
HAVING T1.Country IN (SELECT TOP 2 T2.Country
                      FROM CallsTable T2
                      WHERE DATEADD(hh, DATEDIFF(hh,0,T2.Date),0) = DATEADD(hh, DATEDIFF(hh,0,T1.Date),0)
                      GROUP BY T2.Country
                      ORDER BY SUM(1) DESC                    
                      )

Explanation:

DATEADD(hh, DATEDIFF(hh,0,T1.Date),0) truncates minutes and seconds, in order to group by hour.

The main select groups by hour and countries. The inner select is a filter so only the two top calling country of an specified hour are shown.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460108

Using a cte and over clause it's relatively easy and readable:

WITH CTE AS(
      SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)AS DateHour
    , Country
    , Count(*) OVER(Partition By Country, DATEPART(HOUR, Date))as GrpCount
    FROM @mytable
    WHERE date between @StartDate and @EndDate
)
SELECT DateHour AS Date, Country, GrpCount AS Calls
FROM CTE
GROUP BY Country, DateHour, GrpCount
ORDER BY DateHour

Here's your sample data:

declare @myTable table(date datetime, country varchar(20));
insert into @myTable values(convert(datetime,'2012-04-02 08:00:59',102),'United States');
insert into @myTable values(convert(datetime,'2012-04-02 08:13:42',102),'Canada');
insert into @myTable values(convert(datetime,'2012-04-02 08:13:56',102),'United States');
insert into @myTable values(convert(datetime,'2012-04-02 08:14:07',102),'Mexico');
insert into @myTable values(convert(datetime,'2012-04-02 08:18:09',102),'Canada');
insert into @myTable values(convert(datetime,'2012-04-02 08:19:50',102),'United States');
insert into @myTable values(convert(datetime,'2012-04-02 08:34:34',102),'Mexico');

Declare @StartDate datetime, @EndDate datetime;
    set @StartDate = '20120401 00:00:00';
    set @EndDate = '20120430 23:59:59';

Result:

Date                      Country           Calls
2012-04-02 08:00:00.000   Canada            2
2012-04-02 08:00:00.000   Mexico            2
2012-04-02 08:00:00.000   United States     3

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56725

This should do it:

Declare @StartDate datetime, 
    @EndDate datetime
    set @StartDate = '20120401 00:00:00'
    set @EndDate = '20120430 23:59:59'

;WITH
  mytablePlusHours As
(
    SELECT *,
        DATEADD(HOUR, DATEPART(HOUR, [date]), DATEDIFF(DAY, 0, [date]))     AS [dateHour]
    FROM    [mytable]    
)
, mytableHourGroups As
(
    SELECT  dateHour, 
            country,
            COUNT(*)        As [countryCount],
            ROW_NUMBER() OVER(PARTITION BY dateHour ORDER BY countryCount) 
                            As [countryRank]
    FROM        mytablePlusHours
    GROUP BY    dateHour, country
)
SELECT  
        dateHour     AS [date],
        country,
        countryCount
FROM    mytableHourGroups
WHERE   [date] between @StartDate and @EndDate
  AND   countryRank <= 2
ORDER BY [date], countryRank

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Untested:

SELECT date(yourdate) as mday, 
       datepart(hour, yourdate) as Yourhour, 
       country, 
       count(*) as calls, 
       ROW_NUMBER() OVER(PARTITION BY date(YourDate) & "-" & Datepart(hour,YourDate) & ORDER BY count(*) DESC) myCount
FROM yourTableName
GROUP BY date(yourdate), datepart(hour, yourdate)
having myCount <3
Order by count(*), mday, yourhour

Somehow Row_NUMBER() Over (partiion By... seems like it will get you what you want' but I can't test it. See MSFT ARTICLE for use and why I think it's what your after.

In essence, you want to create different partitions on the day and hour, order by the count of calls within that partition set and assign a number starting at 1 increasing by 1 for each record/count. Then you want to limit that record/count to the top 2 of each day/hour grouping.

Upvotes: 0

AdamV
AdamV

Reputation: 594

Adding to Darren Davies answer, you could further define the table output to resolve by date and hour:

SELECT 
  DATEADD(dd, 0, DATEDIFF(dd, 0, [Date])) as Date, 
  DATEPART(HOUR, [Date]) as Hour, 
  Country, 
  SUM(Calls)
FROM YourTable
GROUP BY 
  DATEADD(dd, 0, DATEDIFF(dd, 0, [Date])),
  DATEPART(HOUR, [Date]), 
  Country

Upvotes: 0

LosManos
LosManos

Reputation: 7692

I believe the simplest solution is in two steps.

First you have to convert the datetimes to even hours. Do it by copying data from one table to another or through a view or adding a calculated field to the table.

Then you do something like 

select date time, country, count(*) calls
from mytable
group by date, country

I am not sure that it is "count(*)" - someone please correct me.

Upvotes: 0

Related Questions