Cummulative Counting records with a condition

Database
Enthusiast

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

6 REPLIES
Supporter

Re: Cummulative Counting records with a condition

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


?

Enthusiast

Re: Cummulative Counting records with a condition

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

Senior Apprentice

Re: Cummulative Counting records with a condition

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

Supporter

Re: Cummulative Counting records with a condition

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.

Enthusiast

Re: Cummulative Counting records with a condition

@ 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

Supporter

Re: Cummulative Counting records with a condition

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