Solved

SQL Pivot Table

Posted on 2014-03-11
5
211 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
[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
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2008 how to table join 2 31
Re-appearing SQL Server Agent jobs 7 43
SSIS Standard Template for Reuse by Business Units 12 54
Split string into 3 separate fields 5 22
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 …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

697 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