SQL Pivot Table

CaptainGiblets
CaptainGiblets used Ask the Experts™
on
I have data that has 2 columns, a status and a date.

is it possible to build the following report in SQL and if so how would I do it

                  Today         Yesterday           Week             Month
StatusA
StatusB

So I would want a total sent today from statusA and B then yesterdays everything this week and finally everything this month (last 7 and 30 days)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
So, count of all StatusAs for today, count of all from yesterday, etc?
Top Expert 2010
Commented:
Assuming my assumption above is correct...

DECLARE @StDate datetime = 
    (SELECT MIN(x.Dt)
    FROM
        (SELECT CONVERT(date, DATEADD(day, 1 - DATEPART(WEEKDAY, GETDATE()), GETDATE())) AS Dt
        UNION
        SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS Dt) x)

SELECT s.[Status],
    SUM(CASE 
        WHEN DATEDIFF(day, s.[Date], GETDATE()) = 0 THEN 1
        ELSE 0 END) AS [Today],
    SUM(CASE 
        WHEN DATEDIFF(day, s.[Date], GETDATE()) = 1 THEN 1
        ELSE 0 END) AS [Yesterday],
    SUM(CASE 
        WHEN DATEDIFF(week, s.[Date], GETDATE()) = 0 THEN 1
        ELSE 0 END) AS [ThisWeek],
    SUM(CASE 
        WHEN DATEDIFF(month, s.[Date], GETDATE()) = 0 THEN 1
        ELSE 0 END) AS [ThisMonth]
FROM [SomeTable] s
WHERE s.[Date] >= @StDate
GROUP BY s.[Status]

Open in new window


Note: edited to make it more index-friendly

Author

Commented:
That works a treat, just a quick question before I close it off, if I wanted to use a different date field for 2 different statuses is this possible? From what I know I would have to create 2 different views with the different date field but just thought I would throw it out there.

For example 2 status  "Sent Out" and "Returned"  with 2 dates "date Sent Out" and "Date Returned"
Top Expert 2010

Commented:
Sounds to me that that approach could be extended to additional date columns...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial