Include null period in crosstab query

• genova11

  SSC-Addicted

  Points: 434

  More actions

  October 12, 2018 at 7:56 am

  #104477

  I have a query that spits out totals for a given month. My column headings in the query are the periods. 

  Ex.

  201804            201805              201806         201808
  $556.66          $6689.00              $667            $833

  I want my query to include 201807 even if it does not have a dollar amount. 
  This is the SQL line for the period column header:   PIVOT [dbo_RX Reporting].Period
  I have tried PIVOT Nz([dbo_RX Reporting].Period) but that did not work. Is there a way to do this?

• Thom A

  SSC Guru

  Points: 99137

  More actions

  October 12, 2018 at 8:27 am

  #2009385

  Sounds like you need a Calendar Table: http://www.sqlservercentral.com/articles/calendar/145206/

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• genova11

  SSC-Addicted

  Points: 434

  More actions

  October 12, 2018 at 1:28 pm

  #2009431

  I changed the Column Headings value in the property sheet of the query to this and it worked.  

  "201804", "201805", "201806", "201807", "201808"

• pietlinden

  SSC Guru

  Points: 63578

  More actions

  October 12, 2018 at 1:42 pm

  #2009434

  the other option is to OUTER join to your Calendar table.
  ...
  FROM Calendar c LEFT JOIN Sales s
     ON c.CalendarDate = s.SaleDate

  because that forces a value for every date in the date range, whether is has sales or not.