Counting Consecutive Days

[jonwolds]

Hello,

I have a table populated each day with account information. Each days data is identified by a 'reportdate' field.

What I need to do is to count the number of consecutive days an account has appeared in the table.

I cannot simply count the number of times the account appears in the table because it could have appeared for 50 days then not appeared for 30 days and then appear for 3 days upto today.

I want my query to return the figure 3 for this account not 53.

Any suggestions would be much appreciated.

Regards
Jon

 

[BillDHS]

Here are some hints that might start you in the right direction:

** Order the rows by date ascending within account.

** Add an sequntial index column to the table.

** Do a self join based on account and tabA.index1 = (tabB.index-1).

** Subtract the date on row TabA.index# from the date on row TabB.index#. This gives you the number of days between rows, so if # days > 1 you have a gap between the two rows.

SELECT t2.Acct, t2.Date, t3.Date, (t2.Date - t3.Date) as Diff

FROM TabA t2, TabB t3

WHERE t2.acct = t3.acct AND t2.index# = (t3.index# - 1)

GROUP BY t2.acct, t2.Date, t3.Date ;

** I'm not sure of the next step, but since this will identify the beginning and end of each span, using index number, there should be a way of computing the span range.

 

[dnoeth]

Looks like a scenario i use in my advanced SQL training classes ;-)

If an account had no report today, do you want to exclude it or use the last bunch of dates?

Anyway, if you run versions of Oracle/DB2/Teradata with SQL:1999 OLAP-function support, it's quite easy and efficient.
If not, you'll have rather complex queries with terrible performance so it's probably faster to use a cursor.

create table test(acc# int, reportdate date);

insert into test values(1,current_date);
insert into test values(1,current_date - 1);
insert into test values(1,current_date - 2);
insert into test values(1,current_date - 3);
insert into test values(1,current_date - 5);
insert into test values(1,current_date - 6);
insert into test values(1,current_date - 8);
insert into test values(1,current_date - 9);
insert into test values(1,current_date - 10);
insert into test values(1,current_date - 11);

insert into test values(2,current_date - 3);
insert into test values(2,current_date - 4);
insert into test values(2,current_date - 5);
insert into test values(2,current_date - 6);
insert into test values(2,current_date - 7);
insert into test values(2,current_date - 8);
insert into test values(2,current_date - 9);
insert into test values(2,current_date - 10);
insert into test values(2,current_date - 11);

SELECT
acc#,
COUNT(*) AS #days,
MAX(reportdate) AS LastReportDate
FROM
(
/** calculate groups of rows having consecutive reportdates **/
SELECT
acc#,
reportdate,
SUM(datediff - 1)
OVER (PARTITION BY acc#
ORDER BY reportdate DESC
ROWS UNBOUNDED PRECEDING) AS dategroup
FROM
(
/** calculate the number of days between two rows ordered by reportdate **/
SELECT
acc#,
reportdate,
COALESCE
(
MIN(reportdate)
OVER (PARTITION BY acc#
ORDER BY reportdate DESC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
- reportdate
, 1
) AS datediff
FROM test
) dt
) dt
/** only the most current group**/
WHERE dategroup = 0
GROUP BY acc#
/** do you want only today's reports? **/
HAVING MAX(reportdate) = current_date
;

Dieter

 

[r937]

that was the easy version?

i don't think i wanna see the complex queries, then


rudy

 

[jonwolds]

Thanks for that guys, we're taking a further look at the issue.

I'll let you know how I get on.
Ta
Jon