I have one table with three columns : product _no , week_no , outlet_no. data looks like this 345 , 201522 , 98 567 , 201521 , 76 and so on.. I need to calc the no of outlets for each product every week. but they should be uniqe...e.g If a product was sold in outlets A and B in first week the no of stores in first week should be 2 , if it is sold in B , C , D next week the total should be 4 for 2nd week , if it is sold in A and D in third week the total should be still 4 for 3rd week. How do I do this...Any help will be greatly appreciated.
This isn't very nice, but I Think it works... create table #theTable(product_id int, week_no int, outlet_no int); insert #thetable(product_id,week_no,outlet_no) values (1,201501,1), (2,201501,1), (1,201501,2), (1,201502,3), (1,201503,4), (1,201504,1); WITH CTE_product as ( select distinct product_id from #thetable ), CTE_week_no as ( SELECT distinct week_no FROM #thetable ), CTE_outlet_no as ( SELECT distinct outlet_no from #thetable ),CTE_all as ( SELECT product_id, week_no, outlet_no from CTE_product CROSS JOIN CTE_week_no CROSS JOIN CTE_outlet_no ), CTE_IsInOutletForWeek as( select CTE_ALL.*, CASE WHEN t.Product_ID IS NULL THEN 0 ELSE 1 END as ProductIsInOutletForWeek from CTE_ALL LEFT JOIN #theTable t ON CTE_ALL.Product_ID = t.Product_ID AND CTE_ALL.outlet_no = t.outlet_no AND CTE_ALL.week_no = t.week_no ),CTE_IsInOutletForAnyPreviousWeek AS( SELECT product_id, week_no, outlet_no, CASE WHEN (SUM(ProductIsInOutletForWeek) OVER(PARTITION BY product_id, outlet_no ORDER BY week_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))>0 THEN 1 ELSE 0 END AS IsInOutletThisOrPreviousWeek FROM CTE_IsInOutletForWeek )select product_id, week_no, SUM(case when IsInOutletThisOrPreviousWeek=0 then 0 else 1 end) from CTE_IsInOutletForAnyPreviousWeek GROUP BY product_id, week_no order by week_no, product_id; DROP TABLE #theTable;
Thanks for the answers , David , yes I want a cumulative count until the week but I don't want to count the same stores again. Basically customer needs to know how long does it take for a product to reach /sell in all the stores across the country. Magnus , thanks for the answer but its nearly impossible to create the table with weeks and sequential numbers since I have more than three years of data and increasing every week.