x

sql for counting distinct values

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.

more ▼

asked Sep 14, 2016 at 01:16 AM in Default

avatar image

flysql
0 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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;
more ▼

answered Sep 14, 2016 at 10:57 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered Sep 14, 2016 at 08:47 AM

avatar image

David Wimbush
10.7k 31 34 44

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Sep 14, 2016 at 02:02 PM

avatar image

flysql
0 1

My code won't require sequential numbers. Did you test it on your table instead of #theTable?

Sep 14, 2016 at 09:39 PM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x53
x24

asked: Sep 14, 2016 at 01:16 AM

Seen: 98 times

Last Updated: Sep 14, 2016 at 09:39 PM

Copyright 2018 Redgate Software. Privacy Policy