question

Thos expected results don't make much sense for which outlets have sold only during that week. Do you want a cumulative count of outlets that have sold up to and including that week?

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.