Danimov82 Danimov82 - 3 months ago 7
SQL Question

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!

Answer

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
Comments