Danimov82 Danimov82 - 3 months ago 9
SQL Question

Counting by Item & Channel by First Month Sold

Following up on my last question, Counting Items based on First Month Sold, I need to count items by the first month they were sold but also need to group by channel along with item.

See sample code below

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

INSERT INTO @sales VALUES(43029, '2011-26-03', Channel1, 1)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 2)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel1, 3)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 4)
INSERT INTO @sales VALUES(43029, '2011-26-03', Channel2, 4)
INSERT INTO @sales VALUES(43029, '2011-26-04', Channel2, 4)
INSERT INTO @sales VALUES(43029, '2011-26-04', Channel1, 5)
INSERT INTO @sales VALUES(43029, '2011-26-04', Channel1, 5)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel1, 5)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 6)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel1, 7)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 8)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 8)
INSERT INTO @sales VALUES(43030, '2011-26-04', Channel2, 8)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 9)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 19)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 19)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 25)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 25)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel1, 25)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 27)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 27)
INSERT INTO @sales VALUES(43030, '2011-26-05', Channel2, 27)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel1, 28)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel2, 29)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel1, 39)
INSERT INTO @sales VALUES(43050, '2011-26-05', Channel2, 30)
INSERT INTO @sales VALUES(43050, '2011-26-06', Channel1, 31)
INSERT INTO @sales VALUES(43050, '2011-26-06', Channel1, 31)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61)
INSERT INTO @sales VALUES(43090, '2011-26-07', Channel2, 61)
INSERT INTO @sales VALUES(43090, '2011-26-08', Channel1, 71)
INSERT INTO @sales VALUES(43090, '2011-26-08', Channel1, 71)


The output would look like the following

ITEMNO| CHANNELTYPE | YEARMONTH | COUNT
---------------------------------------
43029 | Channel1 | 03-2011 | 2
43029 | Channel2 | 03-2011 | 3
43030 | Channel1 | 04-2011 | 2
43030 | Channel2 | 04-2011 | 4
43050 | Channel1 | 05-2011 | 2
43050 | Channel2 | 05-2011 | 2
43090 | Channel1 | 07-2011 | 0
43090 | Channel2 | 07-2011 | 4


A count would only appear if it was sold during the same month as 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.

I tried the following code but I'm getting the first time a product was ordered for the given channel vs only showing the first order year / month of the item which is then broken out by channel for only that month

SELECT s.ITEMNO, s.OrdChannelType, RIGHT(CONVERT(varchar, fd.firstsaledate, 103), 7) AS firstsale, COUNT(*) AS firstmonthsales
FROM dbo.OrderItems AS s INNER JOIN (SELECT ITEMNO, OrdChannelType, MIN(ORDDATE) AS firstsaledate
FROM dbo.OrderItems
GROUP BY ITEMNO, OrdChannelType) AS fd ON MONTH(s.ORDDATE) = MONTH(fd.firstsaledate) AND YEAR(s.ORDDATE) = YEAR(fd.firstsaledate) AND s.ITEMNO = fd.ITEMNO AND s.OrdChannelType = fd.OrdChannelType
GROUP BY s.ITEMNO, s.OrdChannelType, fd.firstsaledate


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