Post reply

Include null period in crosstab query

  • 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?

  • 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

  • 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"

  • 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.

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

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