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