Include null period in crosstab query

  • genova11

    SSC Enthusiast

    Points: 159

    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: 98136

    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.

  • genova11

    SSC Enthusiast

    Points: 159

    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: 62358

    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.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply