October 12, 2018 at 7:56 am
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
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
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy