Danimov82 Danimov82 - 3 months ago 14
SQL Question

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.

See sample code below

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)


The output would look like the following

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


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).

Thanks!

Answer

Along the lines of my previous one, just a little trickier. Unlike Gordon's suggestion, though, I have avoided cross joins. These could get expensive depending on the number of channels. A left join is however unavoidable I think!

    DECLARE @sales table(
    itemnumber int,
    saledate date,
    channeltype varchar(8),
    ordid int)

    INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel1',1)
    INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2',2)
    INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel1',3)
    INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2',4)
    INSERT INTO @sales VALUES(43029, '2011-03-26', 'Channel2',5)
    INSERT INTO @sales VALUES(43029, '2011-04-26', 'Channel2',6)
    INSERT INTO @sales VALUES(43029, '2011-04-26', 'Channel1',7)
    INSERT INTO @sales VALUES(43029, '2011-04-26', 'Channel1',8)
    INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel1',9)
    INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2',10)
    INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel1',11)
    INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2',12)
    INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2',13)
    INSERT INTO @sales VALUES(43030, '2011-04-26', 'Channel2',14)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',15)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',16)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',17)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',18)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',19)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',20)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2',21)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2',22)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',23)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',24)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel1',25)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2',26)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2',27)
    INSERT INTO @sales VALUES(43030, '2011-05-26', 'Channel2',28)
    INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel1',29)
    INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel2',30)
    INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel1',31)
    INSERT INTO @sales VALUES(43050, '2011-05-26', 'Channel2',32)
    INSERT INTO @sales VALUES(43050, '2011-06-26', 'Channel1',33)
    INSERT INTO @sales VALUES(43050, '2011-06-26', 'Channel1',34)
    INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2',35)
    INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2',36)
    INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2',37)
    INSERT INTO @sales VALUES(43090, '2011-07-26', 'Channel2',38)
    INSERT INTO @sales VALUES(43090, '2011-08-26', 'Channel1',39)
    INSERT INTO @sales VALUES(43090, '2011-08-26', 'Channel1',40)

SELECT itemnumber, channeltype, 
right(convert(varchar, firstsaledate, 106), 8) AS firstsale, 
SUM(itemcnt) AS firstmonthsales FROM 
(SELECT fdi.itemnumber, fdi.channeltype, fdi.firstsaledate, 
CASE WHEN sf.itemnumber IS NULL THEN 0 ELSE 1 END as itemcnt FROM
(SELECT DISTINCT si.itemnumber, si.channeltype, fdi.firstsaledate FROM @sales si
INNER JOIN
(SELECT itemnumber, Min(saledate) as firstsaledate FROM @sales 
GROUP BY itemnumber ) as fdi ON fdi.itemnumber = si.itemnumber) fdi
LEFT JOIN @sales sf 
ON sf.channeltype = fdi.channeltype
AND sf.itemnumber = fdi.itemnumber
AND MONTH(sf.saledate) = MONTH(fdi.firstsaledate)
AND YEAR(sf.saledate) = YEAR(fdi.firstsaledate)) AS cnt
GROUP BY itemnumber, channeltype,firstsaledate

Results

itemnumber  channeltype firstsale   firstmonthsales
43029       Channel1    Mar 2011    2
43029       Channel2    Mar 2011    3
43030       Channel1    Apr 2011    2
43030       Channel2    Apr 2011    4
43050       Channel1    May 2011    2
43050       Channel2    May 2011    2
43090       Channel1    Jul 2011    0
43090       Channel2    Jul 2011    4

Note that your example results was wrong for 43090 Channel2. Given the data you provided, the true count is 4 (not 2 as in your expected results).

To get first two months together use:

SELECT itemnumber, channeltype, 
right(convert(varchar, firstsaledate, 106), 8) AS firstsale, 
SUM(itemcnt) AS firsttwomonthsales FROM 
(SELECT fdi.itemnumber, fdi.channeltype, fdi.firstsaledate, 
CASE WHEN sf.itemnumber IS NULL THEN 0 ELSE 1 END as itemcnt FROM
(SELECT DISTINCT si.itemnumber, si.channeltype, fdi.firstsaledate,
DATEADD(m, 1, fdi.firstsaledate) as secondmonth FROM @sales si
INNER JOIN
(SELECT itemnumber, Min(saledate) as firstsaledate FROM @sales 
GROUP BY itemnumber ) as fdi ON fdi.itemnumber = si.itemnumber) fdi
LEFT JOIN @sales sf 
ON sf.channeltype = fdi.channeltype
AND sf.itemnumber = fdi.itemnumber
WHERE (MONTH(sf.saledate) = MONTH(fdi.firstsaledate)
AND YEAR(sf.saledate) = YEAR(fdi.firstsaledate))
OR (MONTH(sf.saledate) = MONTH(fdi.secondmonth)
AND YEAR(sf.saledate) = YEAR(fdi.secondmonth))
) AS cnt

GROUP BY itemnumber, channeltype,firstsaledate

Results

itemnumber  channeltype firstsale   firsttwomonthsales
43029       Channel1    Mar 2011    4
43029       Channel2    Mar 2011    4
43030       Channel1    Apr 2011    11
43030       Channel2    Apr 2011    9
43050       Channel1    May 2011    4
43050       Channel2    May 2011    2
43090       Channel1    Jul 2011    2
43090       Channel2    Jul 2011    4