按项目计数按前两个月销售的频道 [英] Counting by Item & Channel by First Two Months Sold
本文介绍了按项目计数按前两个月销售的频道的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
跟进我的最后一个问题，根据第一个月售出的商品计数，我需要按销售的前两个月计算商品，但还需要按渠道和商品分组，以及销售的商品总数.
Following up on my last question, Counting Items based on First Month Sold, I need to count items by the first two months they were sold but also need to group by channel along with item, along with total item qty sold as well.
查看下面的示例代码
DECLARE @sales table(
itemnumber int,
saledate date,
channeltype varchar,
ordid varchar,
orditemqty int)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 1, 5)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 2, 6)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 3, 2)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 4, 3)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 4, 1)
INSERT INTO @sales VALUES(43029, '2011-26-04', Channel2, 5, 5)
INSERT INTO @sales VALUES(43029, '2011-26-04', Channel2, 5, 7)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel1, 5, 8)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 6, 1)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel1, 7, 2)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 8, 4)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 8, 6)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 8, 1)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9, 8)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9, 10)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9, 11)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9, 1)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9, 4)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 19, 7)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 19, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 25, 10)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 25, 11)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 25, 10)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 27, 1)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 27, 4)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 27, 6)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel1, 28, 8)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel2, 29, 9)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel1, 39, 1)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel2, 30, 4)
INSERT INTO @sales VALUES(43050, '2011-26-06', Channel1, 31, 9)
INSERT INTO @sales VALUES(43050, '2011-26-06', Channel1, 31, 1)
INSERT INTO @sales VALUES(43050, '2011-26-07', Channel1, 45, 6)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61, 3)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61, 4)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61, 5)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61, 8)
INSERT INTO @sales VALUES(43090, '2011-26-08', Channel1, 71, 2)
INSERT INTO @sales VALUES(43090, '2011-26-08', Channel1, 71, 3)
INSERT INTO @sales VALUES(43090, '2011-26-09', Channel1, 76, 5)
输出如下
ITEMNO| CHANNELTYPE | YEARMONTH | COUNT | QTY
---------------------------------------------
43029 | Channel1 | 03-2011 | 0 | 0
43029 | Channel2 | 03-2011 | 7 | 29
43030 | Channel1 | 04-2011 | 11 | 84
43030 | Channel2 | 04-2011 | 9 | 39
43050 | Channel1 | 05-2011 | 4 | 19
43050 | Channel2 | 05-2011 | 2 | 13
43090 | Channel1 | 07-2011 | 2 | 5
43090 | Channel2 | 07-2011 | 4 | 20
只有在第一次订购商品的前两个月内售出时才会显示计数，其中可能是多个渠道之一(我使用 Channel1 和 2 作为示例，但可能有多个) 但将始终有一个频道.如果没有任何东西，在这种情况下数量也为 0，我想显示所有频道并显示 0 计数(例如，在第一个频道中出售的物品两个月，但不是第二个渠道).
A count would only appear if it was sold during the first two months within the first time the item was ordered, where it could be one of several channels (I'm using Channel1, and 2 as examples but there could be several) but will always have one channel.I'd want to display all channels and show a count of 0 if there isn't anything there with the quantity also being 0 in that case (example, an item sold in one channel during those first two months but not the second channel).
谢谢！
推荐答案
现在确实需要交叉连接！
This does need a cross join now!
DECLARE @sales table(
itemnumber int,
saledate date,
channeltype varchar(10),
ordid varchar,
orditemqty int)
INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2', 1, 5)
INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2', 2, 6)
INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2', 3, 2)
INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2', 4, 3)
INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2', 5, 1)
INSERT INTO @sales VALUES(43029, '2011-04-26', 'Channel2', 6, 5)
INSERT INTO @sales VALUES(43029, '2011-04-26', 'Channel2', 7, 7)
INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel1', 8, 8)
INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2', 9, 1)
INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel1', 10, 2)
INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2', 11, 4)
INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2', 12, 6)
INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2', 13, 1)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 14, 8)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 15, 9)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 16, 10)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 17, 11)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 18, 1)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 19, 4)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2', 20, 7)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2', 21, 9)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 22, 10)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 23, 11)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1', 24, 10)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2', 25, 1)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2', 26, 4)
INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2', 27, 6)
INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel1', 28, 8)
INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel2', 29, 9)
INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel1', 30, 1)
INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel2', 31, 4)
INSERT INTO @sales VALUES(43050, '2011-06-26', 'Channel1', 32, 9)
INSERT INTO @sales VALUES(43050, '2011-06-26', 'Channel1', 33, 1)
INSERT INTO @sales VALUES(43050, '2011-07-26', 'Channel1', 34, 6)
INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2', 35, 3)
INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2', 36, 4)
INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2', 37, 5)
INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2', 38, 8)
INSERT INTO @sales VALUES(43090, '2011-08-26', 'Channel1', 39, 2)
INSERT INTO @sales VALUES(43090, '2011-08-26', 'Channel1', 40, 3)
INSERT INTO @sales VALUES(43090, '2011-09-26', 'Channel1', 41, 5)
SELECT t.itemnumber, t.channeltype,
right(convert(varchar, firstsaledate, 106), 8) AS firstsale,
SUM(t.cnt) AS salecount, sum(t.qty) AS saleqty
FROM
(
SELECT fsdc.itemnumber, fsdc.firstsaledate, fsdc.channeltype,
CASE WHEN s.ordid IS NULL THEN 0 ELSE 1 END AS cnt,
COALESCE(s.orditemqty, 0) qty
FROM
(
SELECT fsd.itemnumber, fsd.firstsaledate, fsd.targetdate,
c.channeltype
FROM
(
SELECT mns.itemnumber, mns.firstsaledate, DATEADD(m, 2,
DATEFROMPARTS(YEAR(mns.firstsaledate), MONTH(mns.firstsaledate), 1))
as targetdate
FROM
(
SELECT itemnumber, Min(saledate) as firstsaledate FROM @sales
GROUP BY itemnumber
) mns
) fsd
CROSS JOIN
(
SELECT DISTINCT channeltype FROM @sales
) c
) fsdc
LEFT JOIN @sales s
ON s.itemnumber = fsdc.itemnumber and s.channeltype = fsdc.channeltype
AND s.saledate < fsdc.targetdate
) t
GROUP BY t.itemnumber, t.channeltype, t.firstsaledate
结果
itemnumber channeltype firstsale salecount saleqty
43029 Channel1 Mar 2011 0 0
43029 Channel2 Mar 2011 7 29
43030 Channel1 Apr 2011 11 84
43030 Channel2 Apr 2011 9 39
43050 Channel1 May 2011 4 19
43050 Channel2 May 2011 2 13
43090 Channel1 Jul 2011 2 5
43090 Channel2 Jul 2011 4 20
现在更改第一期涵盖的月数很简单.只需在 dateadd 函数中更改月数即可.
It is now a simple thing to change the number of months covered in the first period. Just change the number of months in the dateadd function.
这篇关于按项目计数按前两个月销售的频道的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
