Solved

SQL Pivot Table

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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