归还所有月份日期范围之间的年数-SQL [英] Return All Months & Years Between Date Range - SQL

问题描述

我对此有点困惑。

我有一个非常基本的查询，该查询当前按年份和月份返回每种产品的销售额。
按年/月分组，并对数量进行求和。
对于有销售的每种产品/年/月组合，这将返回一行。

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.

如果没有订单，那么我可以针对该产品/年/月返回0。

If there was no order, then I can return 0 for that product/year/month.

下面是我的示例查询。

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)

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

推荐答案

谢谢您的建议。

我设法用另一种方法使它起作用。

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

这篇关于归还所有月份日期范围之间的年数-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！