All Forums Database
sunny.j 90 posts Joined 10/10
09 Jul 2012
Cummulative Counting records with a condition

Hi ,

 

I have a set of records as follows :

customer unique_cust_code creation_date_by_mail     last_closed_date

100                xbcnm             15/05/2012                         15/05/2012

100                xbcnm             15/05/2012                      18/05/2012

 

I'm trying to write a query to count the number of  tickets created for the last 30 days from the date of closure.

Expected report :

customer   unique_cust_code last_closed_date         count_tkt

100          xbcnm                     15/05/2012                  1 ------> there are no tickets from the date of closure

100          xbcnm                     18/05/2012                   2 ------> there is a ticket created within the 30 days

 

ulrich 816 posts Joined 09/09
09 Jul 2012

Can you have multiple rows per key with the same last_closed_date?

Someting like 

 

100                xbcnm             15/05/2012                      18/05/2012

100                xbcnm             16/05/2012                      18/05/2012

?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sunny.j 90 posts Joined 10/10
09 Jul 2012

no the last_closed_date will not be the same ,  we will have only one record in that case

 

 

100                xbcnm                     18/05/2012    2  ---- count should be 2

 

dnoeth 4628 posts Joined 11/04
09 Jul 2012

It's easy to write, but hard for the optimizer (= product join, but acceptable if the number of rows per customer is not too high):

SELECT t1.customer, t1.unique_cust_code, t1.last_closed_date, COUNT(*)
FROM dropme AS t1 JOIN dropme AS t2
ON t2.customer=t1.customer 
 AND t2.last_closed_date <= t1.last_closed_date
 AND t2.creation_date_by_mail >= t1.last_closed_date - 30
GROUP BY 1,2,3

Untested, but i think i got the logic right.

In TD13 you might write the same using a Scalar Subquery within the SELECT instead of the join, but the plan will probably be worse.

There might be other solutions involving OLAP functions, too.

Dieter

Dieter

ulrich 816 posts Joined 09/09
09 Jul 2012

Hi sunny, my question was related to the input table.

The output result is clear. Its related to dieters remark. In case of unique last_closed_date OLAP functions might be an option. Otherwise additional aggregations would be required.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sunny.j 90 posts Joined 10/10
09 Jul 2012

@ thanks for your solution  Dnoeth , i will try this query

 

@ Ulrich :

 

yes ,The Input table may contains the Last_closed_date same for different creation_dates like this

100                xbcnm             15/05/2012                      18/05/2012

100                xbcnm             16/05/2012                      18/05/2012

 

then the output must be :

100                xbcnm                     18/05/2012    2  ---- count should be 2

 

ulrich 816 posts Joined 09/09
10 Jul 2012

 

In this case you need to modify Dieters SQL a bit to dedup the closed dates.

create table sunny_test (id integer, code varchar(10), create_dt date, closed_dt date) primary index (id);

insert into sunny_test (1,'xxx','2012-05-15', '2012-05-18');
insert into sunny_test (1,'xxx','2012-05-16', '2012-05-18');
insert into sunny_test (1,'xxx','2012-05-11', '2012-05-12');
insert into sunny_test (1,'yyy','2012-05-11', '2012-05-12');
insert into sunny_test (1,'yyy','2012-02-11', '2012-02-28');
insert into sunny_test (1,'yyy','2012-02-11', '2012-05-12');

select s2.id,
       s2.code, 
       s2.closed_dt,
       count(*)
from
      sunny_test s1
      join
      (select id, code, closed_dt from sunny_test group by 1,2,3) s2
        on s1.id = s1.id
           and s1.code = s2.code
           and s1.create_dt >= s2.closed_dt - 30
           and s1.create_dt <= s2.closed_dt
group by 1,2,3
order by 1,2,3

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.