• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

SQL Pivot - All Dates

I currently have SQL that creates a pivot based on PERIOD, but excludes dates (INS_DATE) where no data exists.  How can I get it to show all dates whether data exists or not?

WITH BASEQUERY AS(
SELECT INS_DATE, CONVERT(VARCHAR(5),INSERT_PERIOD,108) as PERIOD, BOOKINGS
FROM SUMMARY
WHERE CATEGORY IN('WEB')
)
SELECT * FROM BASEQUERY
pivot (SUM(BOOKINGS) for PERIOD in (
[00:00],[00:30],[01:00],[01:30],[02:00],[02:30],
[03:00],[03:30],[04:00],[04:30],[05:00],[05:30],
[06:00],[06:30],[07:00],[07:30],[08:00],[08:30],
[09:00],[09:30],[10:00],[10:30],[11:00],[11:30],
[12:00],[12:30],[13:00],[13:30],[14:00],[14:30],
[15:00],[15:30],[16:00],[16:30],[17:00],[17:30],
[18:00],[18:30],[19:00],[19:30],[20:00],[20:30],
[21:00],[21:30],[22:00],[22:30],[23:00],[23:30])) AS SUMBOOKINGSPERPERIOD
ORDER BY 1 ASC

Open in new window

0
Cody Vance
Asked:
Cody Vance
  • 4
  • 3
  • 3
1 Solution
 
YZlatCommented:
Can you post a sample of original data?
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
I cannot unfortunately..
0
 
YZlatCommented:
which data is missing BOOKINGS or INSERT_PERIOD?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
YZlatCommented:
What you can do is replace the null values with a string:

WITH BASEQUERY AS(
SELECT INS_DATE, CONVERT(VARCHAR(5),INSERT_PERIOD,108) as PERIOD, ISNULL(BOOKINGS, 'NULL') as BOOKINGS
FROM SUMMARY
WHERE CATEGORY IN('WEB')
)
SELECT * FROM BASEQUERY
pivot (SUM(BOOKINGS) for PERIOD in (
[00:00],[00:30],[01:00],[01:30],[02:00],[02:30],
[03:00],[03:30],[04:00],[04:30],[05:00],[05:30],
[06:00],[06:30],[07:00],[07:30],[08:00],[08:30],
[09:00],[09:30],[10:00],[10:30],[11:00],[11:30],
[12:00],[12:30],[13:00],[13:30],[14:00],[14:30],
[15:00],[15:30],[16:00],[16:30],[17:00],[17:30],
[18:00],[18:30],[19:00],[19:30],[20:00],[20:30],
[21:00],[21:30],[22:00],[22:30],[23:00],[23:30])) AS SUMBOOKINGSPERPERIOD
ORDER BY 1 ASC

Open in new window

0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
Here is an example, as you can see it starts at 2/12/13 since there is no data for 1/1/13-2/11/13.  I need it to show these dates, even if it shows NULL all the way across.

Data
0
 
PortletPaulCommented:
With respect to the dates that are not present is the results:

You need a table of dates first, then left join your data to that.

This table of dates could be a permanent feature or something temporary (e.g. a recursive CTE)

----
posting data: >>"I cannot unfortunately.."

but you could post "sample data" (or "representative data") that is stripped of anything private.
0
 
PortletPaulCommented:
here is an article discussing creation of a date table
http://www.experts-exchange.com/Database/MS-SQL-Server/A_12267-Date-Fun-Part-One-Build-your-own-SQL-calendar-table-to-perform-complex-date-expressions.html

& here is sample code using a recursive CTE
-- establish a date range
DECLARE @fromdate datetime
      , @todate datetime
SELECT
        @fromdate = '20130901' 
      , @todate   = '20131001'

;WITH
        DateCTE (theDate)
        AS (
                SELECT
                        @fromdate AS theDate
                UNION ALL
                        SELECT
                                DATEADD(DAY, 1, theDate)
                        FROM DateCTE
                        WHERE theDate < dateadd(day,-1,@todate)
                )
SELECT
        *
FROM DateCTE
left join SUMMARY on DateCTE.theDate = SUMMARY.INS_DATE
...

Open in new window

0
 
YZlatCommented:
Did you try my solution?
0
 
PortletPaulCommented:
YZlat, the question as I understand it is about missing rows.

>>"How can I get it to show all dates whether data exists or not?"
0
 
Cody VanceSr. Analyst - ERPAuthor Commented:
This worked perfectly, thank you!!!!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now