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.

Below is some sample code

    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

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

Any help would be great!

回答1:

Try this

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

Results

itemnumber  firstsale   firstmonthsales
1234        01/2015     5
1255        03/2015     8
1256        02/2015     5
1259        02/2015     2

回答2:

Using sample data from Jonathan Willcock..

Since you are on 2012,you can use window functions as well like below..

;With cte
as
(select 
itemnumber,saledate,dense_rank() over (partition by  itemnumber order by cast(month(saledate) as varchar(2))+'/'+cast(year(saledate) as varchar(4)) ) as rownum
from
@sales
)
select itemnumber,cast(month(saledate) as varchar(2))+'/'+cast(year(saledate) as varchar(4)),sum(rownum) as salescount

from cte
where rownum=1
group by
itemnumber,cast(month(saledate) as varchar(2))+'/'+cast(year(saledate) as varchar(4))