Solved

SQL Pivot Table

Posted on 2014-03-03
4
500 Views
Last Modified: 2014-03-10
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)
0
Comment
Question by:CaptainGiblets
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39901701
So, count of all StatusAs for today, count of all from yesterday, etc?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39901752
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
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39903197
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"
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39915102
Sounds to me that that approach could be extended to additional date columns...
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question