Horizontal Axis Month Sales

• paul.farnell

  SSC Eights!

  Points: 806

  More actions

  January 22, 2015 at 4:45 pm

  #294643

  Hi

  When you have a horizontal axis looking at sales by month on a graph, how can you show an month with no sales ?

  Paul

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  January 22, 2015 at 5:30 pm

  #1772445

  You may need to force the existence of all dates in the range. (This would probably be better with a CTE to generate dates in a range, but anyway...)

  -- create calendar table

  CREATE TABLE Sales.Calendar(

  calendardate datetime

  CONSTRAINT pkCalendar PRIMARY KEY (calendarDate));

  -- populate the calendar table

  DECLARE @theDate datetime = '2007-02-01';

  WHILE @theDate<'2007-02-28'

  BEGIN

  INSERT INTO Sales.Calendar(calendarDate) VALUES (@theDate);

  SET @theDate = DATEADD(d,1,@theDate)

  END

  -- outer join calendar table to sales to force existence of all dates in the range.

  SELECT c.CalendarDate

  , COUNT(o.OrderID) SalesCount

  , COALESCE(SUM(o.Freight),0) TotalFreight

  FROM Sales.Calendar c LEFT JOIN Sales.Orders o

  ON c.calendardate = o.orderdate

  GROUP BY c.calendardate;