Solved

SQL Pivot Table

Posted on 2014-03-11
5
203 Views
Last Modified: 2014-04-23
I asked a question a few days back which I need to try and expand on (original question here http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28378788.html)

So this works great for the one date, however I want to compare different statuses against different dates. so for example I want my table to look like this

                                                Today         Yesterday           Week             Month
StatusA (date recorded)
StatusB (date updated)
StatusC (Date Sent Out)
StatusD (Date Live)

I have a column for each of these dates, and one column called Status.

Is this possible?
0
Comment
Question by:CaptainGiblets
  • 4
5 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39922364
Yes, that should be possible.

You would need to express actual dates (or counts) as "today", "yesterday", "week", "month"

so you end up pivoting something like :

select *
from   (SELECT status, case when <date calcs> true then 'Yesterday'
                            when <date calcs> true then 'Today'
                            etc
                       end as Period,
                       some_aggregate as amount
        FROM #table ) src
pivot
       (sum(amount) for Period in ([Today],[Yesterday],[Week],[Month]) ) pvt

Open in new window


Now the REAL challenge is all in the definition of what is to be aggregated in those columns and might need to become quite a different looking "case" such that your initial "Select *" also gets involved in some aggregations. e,g.

select Status, [today] as [Today],
       [yesterday] as [Yesterday,
       [prior_this_week] + [today] + [yesterday] + [rest of week] as [Week],
       [prior_this_month] + [prior_this_week] + [today] + [yesterday] + [rest of week] + [rest_of_month] as [Month]
from
      (select statement) src
pivot
      (pivot statement) pvt

Open in new window


I,ll now go back and have a look at your original question.

In the meantime, it will help if you can elaborate on the "rules" for those aggregates
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39922462
Right... Just looked at the other question.

I can now see the different dates periods of the status as part of the status column.

So, if you want to see (as example) status "closed" regardless of fitting into the date (column) criteria, then that is a little different. Basically resulting in a static "x-axis" list of status - does that sound right ?

At this point, I would strongly recommend a full description of your requirement (including definitions for week and month - and what happens if "yesterday" is prior week or month). ideally include a mock up spreadsheet.
0
 
LVL 6

Author Comment

by:CaptainGiblets
ID: 39922846
I have attached a spreadsheet showing a small mock up of what i am after.

The full database can have up to 10 different statuses and 10 different date columns, so i need to be able to specify which date column is calculated depending on what the status is.

This month is the calendar month, this week can be monday - sunday or sunday - saturday as long as it has the work week in there. Yesterday is the whole of the previous day and today is any time between 00:00 to 23:59 on the current day.


Hopefully i have explained a bit better.
date-example.xlsx
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 39923347
OK, got it...

You need to decide which of the three dates to use (corresponding to status) for the date calcs...

So, going back to Patrick's solution... We simply need to decide which date to use for those date calcs.

If dates are either NULL or have a legit value corresponding to status, then you could simply swap [DATE] with coalesce([date_recorded],[date_exported],[date_completed])

But given your comment above about other variables, then maybe best to code it so it can be expanded.

The easiest way to do that is via a subquery or a CTE (common table expression)

;WITH cte_status AS 
(
  SELECT [Internal_Status] as [Status]
        ,CASE WHEN [Internal_Status] = 'Complete' then [Date_Completed]
              WHEN [Internal_Status] = 'Exported' then [Date_Exported]
              WHEN [Internal_Status] = 'Recorded' then [Date_Recorded]
         END as [Date]	
  FROM   [#SomeTable] s
)
SELECT [Status],
    SUM(CASE WHEN DATEDIFF(day, [Date], GETDATE()) = 0 THEN 1 ELSE 0 END) AS [Today],
    SUM(CASE WHEN DATEDIFF(day, [Date], GETDATE()) = 1 THEN 1 ELSE 0 END) AS [Yesterday],
    SUM(CASE WHEN DATEDIFF(week, [Date], GETDATE()) = 0 THEN 1 ELSE 0 END) AS [ThisWeek],
    SUM(CASE WHEN DATEDIFF(month, [Date], GETDATE()) = 0 THEN 1 ELSE 0 END) AS [ThisMonth]
FROM cte_status
GROUP BY [Status] 

Open in new window


or as a subquery...

SELECT [Status],
    SUM(CASE WHEN DATEDIFF(day, [Date], GETDATE()) = 0 THEN 1 ELSE 0 END) AS [Today],
    SUM(CASE WHEN DATEDIFF(day, [Date], GETDATE()) = 1 THEN 1 ELSE 0 END) AS [Yesterday],
    SUM(CASE WHEN DATEDIFF(week, [Date], GETDATE()) = 0 THEN 1 ELSE 0 END) AS [ThisWeek],
    SUM(CASE WHEN DATEDIFF(month, [Date], GETDATE()) = 0 THEN 1 ELSE 0 END) AS [ThisMonth]
FROM (SELECT [Internal_Status] as [Status]
            ,CASE WHEN [Internal_Status] = 'Complete' then [Date_Completed]
                  WHEN [Internal_Status] = 'Exported' then [Date_Exported]
                  WHEN [Internal_Status] = 'Recorded' then [Date_Recorded]
             END as [Date]	
      FROM   [#SomeTable]) s
GROUP BY [Status] 

Open in new window



And we could still use the PIVOT if needed, but think the group by query works fine for now. Might be different with your other variables.

I have written a couple of Articles about pivot which you might find interesting...

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39933276
Hi,

Is it working for you ? Is there anything else I need to do / clarify / help ?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

15 Experts available now in Live!

Get 1:1 Help Now