SQL Pivot Table

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?
LVL 6
CaptainGibletsAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
CaptainGibletsAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
Hi,

Is it working for you ? Is there anything else I need to do / clarify / help ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.