Include null period in crosstab query

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


    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?

  • Sounds like you need a Calendar Table:


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

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

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

  • 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