# Return All Months & Years Between Date Range – SQL

## Return All Months & Years Between Date Range – SQL

I’m a bit stumped how I might go about this.
I have a very basic query, that currently returns sales for each product, by year and month.
It is grouping by year/month, and summing up the quantity.
This returns one row for each product/year/month combo where there was a sale.
If there was no sale for a month, then there is no data.
I’d like my query to return one row of data for each product for each year/month in my date range, regardless of whether there was actually an order.
If there was no order, then I can return 0 for that product/year/month.
Below is my example query.
Declare @DateFrom datetime, @DateTo Datetime
Set @DateFrom = ‘2012-01-01’
set @DateTo = ‘2013-12-31’

select
Convert(CHAR(4),order_header.oh_datetime,120) + ‘/’ + Convert(CHAR(2),order_header.oh_datetime,110) As YearMonth,
variant_detail.vad_variant_code,
sum(order_line_item.oli_qty_required) as ‘TotalQty’

From
variant_Detail
join order_line_item on order_line_item.oli_vad_id = variant_detail.vad_id
join order_header on order_header.oh_id = order_line_item.oli_oh_id

Where
(order_header.oh_datetime between @DateFrom and @DateTo)

Related:  Select the number of days a record is present within a certain year/month

Group By
Convert(CHAR(4),order_header.oh_datetime,120) + ‘/’ + Convert(CHAR(2),order_header.oh_datetime,110),
variant_detail.vad_variant_code

## Solutions/Answers:

### Solution 1:

You can generate this by using CTE.
You will find information on this article :
http://blog.lysender.com/2010/11/sql-server-generating-date-range-with-cte/

Especially this piece of code :

``````WITH CTE AS
(
SELECT @start_date AS cte_start_date
UNION ALL
SELECT DATEADD(MONTH, 1, cte_start_date)
FROM CTE
WHERE DATEADD(MONTH, 1, cte_start_date) <= @end_date
)
SELECT *
FROM CTE
``````

### Solution 2:

Thank your for your suggestions.

I managed to get this working using another method.

``````Declare @DateFrom datetime, @DateTo Datetime
Set @DateFrom = '2012-01-01'
set @DateTo = '2013-12-31'

select
YearMonthTbl.YearMonth,
orders.vad_variant_code,
orders.qty

From
(SELECT  Convert(CHAR(4),DATEADD(MONTH, x.number, @DateFrom),120) + '/' + Convert(CHAR(2),DATEADD(MONTH, x.number, @DateFrom),110) As YearMonth
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'
AND     x.number <= DATEDIFF(MONTH, @DateFrom, @DateTo)) YearMonthTbl

left join
(select variant_Detail.vad_variant_code,
sum(order_line_item.oli_qty_required) as 'Qty',
Convert(CHAR(4),order_header.oh_datetime,120) + '/' + Convert(CHAR(2),order_header.oh_datetime,110) As 'YearMonth'
FROM order_line_item
join variant_detail on variant_detail.vad_id = order_line_item.oli_vad_id
join order_header on order_header.oh_id = order_line_item.oli_oh_id
Where
(order_header.oh_datetime between @DateFrom and @DateTo)
GROUP BY variant_Detail.vad_variant_code,
Convert(CHAR(4),order_header.oh_datetime,120) + '/' + Convert(CHAR(2),order_header.oh_datetime,110)
) as Orders on Orders.YearMonth = YearMonthTbl.YearMonth
``````

### Solution 3:

This is what I put together. It will certainly need some debugging, but I think that this will lead you in the right direction. I broke up the queries into different parts in order to attempt to make it easier to read. Hope this helps.

``````DECLARE @dateFrom DATETIME, @dateTo DATETIME

SELECT @dateFrom = MIN(oh_datetime) FROM order_header
SELECT @dateTo = MAX(oh_datetime) FROM order_header

;WITH
y AS
(
SELECT YEAR(@dateFrom) AS [Year]
UNION ALL
SELECT [Year] + 1
FROM y
WHERE
[Year] < YEAR (GETDATE())
),
m AS
(
SELECT 1 AS [Month]
UNION ALL
SELECT [Month] + 1
FROM m
WHERE
[Month] < 12
),
dates AS
(
SELECT
CAST(y.[Year] AS nvarchar(4)) + N'/' + RIGHT(N'00' + CAST(m.[Month] AS nvarchar(2)), 2) AS YearMonth
FROM
y CROSS JOIN m
),
qty AS
(
SELECT
YEAR(oh.oh_datetime) + N'/' + MONTH(oh.oh_datetime) AS YearMonth,
v.vad_variant_code,
oli.oli_qty_required AS Qty
FROM
variant_Detail AS v
INNER JOIN order_line_item AS oli ON oil.oli_vad_id = v.vad_id
INNER JOIN order_header AS oh ON oh.oh_id = oli.oli_oh_id
)
SELECT
d.YearMonth,
qty.vad_variant_code,
SUM(qty.Qty) AS TotalQty
FROM
dates AS d LEFT OUTER JOIN qty
ON d.YearMonth = qty.YearMonth
GROUP BY
d.YearMonth,
qty.vad_variant_code
``````

## References

Related:  How return a count(*) of 0 instead of NULL