CODEWITHSUNDEEP
sql-server
TheGeneral
TheGeneral

Reputation: 81493

Return the count of all records per day of week by clientId

I'm trying to write a query that will return the daily count of clients records (for the last 7 days) by client.

Given a table with the following significant fields:

ClientId,    ProcessTime,

I'd like to produce a table with the clientId and the last 7 days as column headers 0 - 7 (including today) and the records processed in that day:

ClientId, 0, 1, 2, 3, 4, 5, 6, 7

I have come up with the following query which gets me close:

Select ClientId, Count(Id) as [Count], max(DATEDIFF(DAY,ProcessTime,GETDATE())) as [Day]
from CallRecords
WHERE DATEDIFF(DAY,ProcessTime,GETDATE()) <= 7
group by ClientId, Dateadd(day, Datediff(day, 0, ProcessTime), 0)
order by 1, 2

which produces:

ClientId Count Day 
1        60    0
1        4707  1
1        11613 2

However, how can I manipulate those results into a format where I get a list of clients and the last 7 days of results as columns?

Upvotes: 0

Views: 1579

Answers (3)

conkman
conkman

Reputation: 131

Pivot is the way to go for this scenario. Here is a link to Microsofts pivot documentation, Microsoft PIVOT. To find the day of the week you should use the DatePart function, Microsoft DatePart Function

select ClientId as 'Client', [1], [2], [3], [4], [5], [6], [7] from (
    select ClientId, Id, CAST(DatePart(dw,ProcessTime) as int) as [DayOfWeek]
    from CallRecords
) as SourceTable
PIVOT (
    COUNT(Id)
    FOR [DayOfWeek] IN ([1], [2], [3], [4], [5], [6], [7])
) as pvt

Checkout this SQLFiddle

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

You want what is termed a pivot, which SQLServer natively supports:

select ClientId, [0], [1], [2], [3], [4], [5], [6], [7]
from (select ClientId, DATEDIFF(DAY,ProcessTime,GETDATE()) days
    from CallRecords
    where DATEDIFF(DAY,ProcessTime,GETDATE()) <= 7) t
pivot (count(days) for days in ([0], [1], [2], [3], [4], [5], [6], [7])) pt

SQLFiddle

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

SELECT ClientID 
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 1 THEN 1 ELSE NULL END) AS [Sunday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 2 THEN 1 ELSE NULL END) AS [Monday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 3 THEN 1 ELSE NULL END) AS [Tuesday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 4 THEN 1 ELSE NULL END) AS [Wednesday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 5 THEN 1 ELSE NULL END) AS [Thursday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 6 THEN 1 ELSE NULL END) AS [Friday]
      ,COUNT(CASE WHEN DATEPART(WEEKDAY,ProcessTime) = 7 THEN 1 ELSE NULL END) AS [Saturday]
FROM CallRecords
WHERE ProcessTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 7, 0) 
GROUP BY ClientID

Upvotes: 1

Related Questions