Solved

SQL Pivot Table

Posted on 2014-03-11
5
205 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 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