Count distinct customers between dates

Reply
Occasional Contributor
Posts: 6

Count distinct customers between dates

Hi everyone,

So my doubt is probably simple to everyone here (but probably not so simple as it appears in the subject): I have a table with 3 columns: CustomerNumber,DateofPurchase, OrderID. Dates are between 25DEC2011 to 31DEC2012 and can, obviously, repeat according to the # of different customers and orders on that date. Same reasoning to Customer ID (he can have bought more than 1 product on the same date).

What I need: I want to have a table with 366 rows (corresponding to 366 days of 2012) with the number of distinct customers that bought at least a product from us on that week (rule is something as a count distinct between that date and that date minus 7 days). But I want this on a daily basis. In other words:

- For the row of 01JAN2012 I would like to have the # of distinct customers that bought between 26DEC2011 and 01JAN2012.

- For the row of 02JAN2012 I would like to have the # of distinct customers that bought between 27DEC2011 and 02JAN2012.

and so on so on up to 31DEC2012.

Tks,

Stu

Respected Advisor
Posts: 3,124

Re: Count distinct customers between dates

Of course not tested, something along the line with the following may send you onto the right track:

proc sql;

create table want as

   select a.DateofPurchase, count(distinct  b.CustomerNumber)  from have a

      left join have b

     on 0<= a.DateofPurchase - b.DateofPurchase <=6

  group by a.DateofPurchase

;

quit;

Haikuo

Super User
Posts: 9,681

Re: Count distinct customers between dates

You'd better post some sample data .

proc sql;

create table want as

select a.DateofPurchase,(select count(*) from have where DateofPurchase between a.DateofPurchase-7 and a.DateofPurchase )

  from (select distinct DateofPurchase from have) as a ;

quit;

Ksharp

Ask a Question
Discussion stats
  • 2 replies
  • 161 views
  • 0 likes
  • 3 in conversation