根据第一个月售出的物品计数 [英] Counting Items based on First Month Sold
本文介绍了根据第一个月售出的物品计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个订单列表，以及每个订单中售出的商品列表.
I have a list of orders, along with a list of items that were sold in each order.
但是，我只想计算在出售商品的第一个月内售出的商品.这通常可以通过查看项目出现的最短日期来计算.
However, I only want to count items sold during the first month the items were for sale. This would typically be figured out by looking at the minimum date in which an item appeared.
下面是一些示例代码
DECLARE @sales table(
itemnumber int,
saledate date,
ordid int)
INSERT INTO @sales VALUES(43029, '2011-26-03', 1)
INSERT INTO @sales VALUES(43029, '2011-26-03', 2)
INSERT INTO @sales VALUES(43029, '2011-26-03', 3)
INSERT INTO @sales VALUES(43029, '2011-26-03', 4)
INSERT INTO @sales VALUES(43029, '2011-26-03', 4)
INSERT INTO @sales VALUES(43029, '2011-26-04', 4)
INSERT INTO @sales VALUES(43029, '2011-26-04', 5)
INSERT INTO @sales VALUES(43029, '2011-26-04', 5)
INSERT INTO @sales VALUES(43030, '2011-26-04', 5)
INSERT INTO @sales VALUES(43030, '2011-26-04', 6)
INSERT INTO @sales VALUES(43030, '2011-26-04', 7)
INSERT INTO @sales VALUES(43030, '2011-26-04', 8)
INSERT INTO @sales VALUES(43030, '2011-26-04', 8)
INSERT INTO @sales VALUES(43030, '2011-26-04', 8)
INSERT INTO @sales VALUES(43030, '2011-26-05', 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', 19)
INSERT INTO @sales VALUES(43030, '2011-26-05', 19)
INSERT INTO @sales VALUES(43030, '2011-26-05', 25)
INSERT INTO @sales VALUES(43030, '2011-26-05', 25)
INSERT INTO @sales VALUES(43030, '2011-26-05', 25)
INSERT INTO @sales VALUES(43030, '2011-26-05', 27)
INSERT INTO @sales VALUES(43030, '2011-26-05', 27)
INSERT INTO @sales VALUES(43030, '2011-26-05', 27)
INSERT INTO @sales VALUES(43050, '2011-26-05', 28)
INSERT INTO @sales VALUES(43050, '2011-26-05', 29)
INSERT INTO @sales VALUES(43050, '2011-26-05', 39)
INSERT INTO @sales VALUES(43050, '2011-26-05', 30)
INSERT INTO @sales VALUES(43050, '2011-26-06', 31)
INSERT INTO @sales VALUES(43050, '2011-26-06', 31)
INSERT INTO @sales VALUES(43090, '2011-26-07', 61)
INSERT INTO @sales VALUES(43090, '2011-26-07', 61)
INSERT INTO @sales VALUES(43090, '2011-26-07', 61)
INSERT INTO @sales VALUES(43090, '2011-26-07', 61)
INSERT INTO @sales VALUES(43090, '2011-26-08', 71)
INSERT INTO @sales VALUES(43090, '2011-26-08', 71)
输出将如下表所示，只计算该商品第一个月销售额的订单
The output would look like the table below, only counting orders from first months worth of sales for that item
ItemNo | MonthYear | Count
-------------------------
43029 | 03-2011 | 5
43030 | 04-2011 | 6
43050 | 05-2011 | 4
43090 | 07-2011 | 4
我可以通过 ITEM NO 获得总计数并显示第一个订单日期，并计算当天售出的商品数量，但无法汇总该月/年的所有商品，因为该商品可能会在同月的多个订单/日期内销售
I'm able to get total counts by ITEM NO along with showing the first ord date, and count the number of items sold on the day itself, but am not able to count all items for the month/year in aggregate, as the item may be sold over multiple orders/dates in the same month
任何帮助都会很棒！
推荐答案
试试这个
DECLARE @sales table(
itemnumber int,
saledate date,
saleid int)
INSERT INTO @sales VALUES(1234, '2015-01-10', 1)
INSERT INTO @sales VALUES(1234, '2015-01-11', 2)
INSERT INTO @sales VALUES(1234, '2015-01-12', 3)
INSERT INTO @sales VALUES(1234, '2015-01-13', 4)
INSERT INTO @sales VALUES(1234, '2015-01-14', 5)
INSERT INTO @sales VALUES(1234, '2015-02-10', 6)
INSERT INTO @sales VALUES(1234, '2015-02-11', 7)
INSERT INTO @sales VALUES(1234, '2015-03-10', 8)
INSERT INTO @sales VALUES(1234, '2015-04-10', 9)
INSERT INTO @sales VALUES(1234, '2015-04-16', 10)
INSERT INTO @sales VALUES(1256, '2015-02-10', 11)
INSERT INTO @sales VALUES(1256, '2015-02-11', 12)
INSERT INTO @sales VALUES(1256, '2015-02-12', 13)
INSERT INTO @sales VALUES(1256, '2015-02-13', 14)
INSERT INTO @sales VALUES(1256, '2015-02-14', 15)
INSERT INTO @sales VALUES(1256, '2015-03-10', 16)
INSERT INTO @sales VALUES(1256, '2015-03-11', 17)
INSERT INTO @sales VALUES(1256, '2015-04-10', 18)
INSERT INTO @sales VALUES(1256, '2015-04-10', 19)
INSERT INTO @sales VALUES(1256, '2015-04-16', 20)
INSERT INTO @sales VALUES(1259, '2015-02-10', 21)
INSERT INTO @sales VALUES(1259, '2015-02-11', 22)
INSERT INTO @sales VALUES(1259, '2015-03-12', 23)
INSERT INTO @sales VALUES(1259, '2015-03-13', 24)
INSERT INTO @sales VALUES(1259, '2015-03-14', 25)
INSERT INTO @sales VALUES(1259, '2015-03-10', 26)
INSERT INTO @sales VALUES(1259, '2015-03-11', 27)
INSERT INTO @sales VALUES(1259, '2015-04-10', 28)
INSERT INTO @sales VALUES(1259, '2015-04-10', 29)
INSERT INTO @sales VALUES(1259, '2015-04-16', 30)
INSERT INTO @sales VALUES(1255, '2015-03-10', 31)
INSERT INTO @sales VALUES(1255, '2015-03-11', 32)
INSERT INTO @sales VALUES(1255, '2015-03-12', 33)
INSERT INTO @sales VALUES(1255, '2015-03-13', 34)
INSERT INTO @sales VALUES(1255, '2015-03-14', 35)
INSERT INTO @sales VALUES(1255, '2015-03-10', 36)
INSERT INTO @sales VALUES(1255, '2015-03-11', 37)
INSERT INTO @sales VALUES(1255, '2015-03-10', 38)
INSERT INTO @sales VALUES(1255, '2015-04-10', 39)
INSERT INTO @sales VALUES(1255, '2015-04-16', 40)
SELECT s.itemnumber,
right(convert(varchar, fd.firstsaledate, 103), 7) AS firstsale,
COUNT(*) as firstmonthsales FROM @sales s
INNER JOIN
(SELECT itemnumber, Min(saledate) as firstsaledate FROM @sales
GROUP BY itemnumber) as fd
ON MONTH(s.saledate) = MONTH(fd.firstsaledate)
AND YEAR(s.saledate) = YEAR(fd.firstsaledate)
AND s.itemnumber = fd.itemnumber
GROUP BY s.itemnumber, fd.firstsaledate
结果
itemnumber firstsale firstmonthsales
1234 01/2015 5
1255 03/2015 8
1256 02/2015 5
1259 02/2015 2
这篇关于根据第一个月售出的物品计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
