How do I include every month when using Datepart when some months have null data?

advertisements

How do I include all months when using Datepart when some months have null data?

I have sales and refund data for the last year, but some months have NULL data. How do I include those months, and have ROW_NUMBER increment?

SELECT
    ROW_NUMBER() OVER (ORDER BY DATEPART(Year, SalesDate), DATEPART(MONTH, SalesDate)) AS 'RowNumber',
    LEFT(datename(MONTH, SalesDate),3) AS 'Month',
    DATEPART(Year, SalesDate) AS 'Year',
    SUM(Refunds) 'Refunds',
    COUNT(Sales),
    SUM(Refunds) / COUNT(Sales) AS 'Percent Refunds'

FROM Sales_Table
WHERE SalesDate BETWEEN '10/01/2012' AND '12/31/2013'

GROUP BY DATEPART(Year, SalesDate), DATEPART(Month, SalesDate), DATENAME(month, SalesDate)

The output looks like this:

But as you can see Dec 2012 is missing. I have tried using ISNULL and CASE WHEN NULL on the counts and sums to no avail.

Thanks in advance!


Create tmp table, like so:

create table #tmpTable (SalesDate dateTime,Refunds int,Sales int)
-- Populate
INSERT INTO #tmpTable VALUES ( '1/1/2013',0,0 )
INSERT INTO #tmpTable VALUES ( '2/1/2013',0,0 )
INSERT INTO #tmpTable VALUES ( '3/1/2013',0,0 )
INSERT INTO #tmpTable VALUES ( '4/1/2013',0,0 )

Now, tweak your code

   FROM ( select sales_date,sales,refunds from Sales_Table
          union
          select * from #tmpTable  xx
          left join Sales_table st on month(xx.sales_date)=month(st.sales_date) and
                                      year(xx.sales_date)=year(st.sales_Date)
          WHERE st.sales_date is null
        ) Sales_table
      WHERE SalesDate BETWEEN '10/01/2012' AND '12/31/2013'

Don't have sample data and typed in on the fly, but should get your started