Solved

SQL Pivot Table

Posted on 2014-03-03
4
496 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now