Counting consecutive dates

Database

Counting consecutive dates

I am trying to figure out how to count consecutive dates for and Employee where TRAN_TYP = 'A'

I have tried CSUM but could not get exactly what I wanted.

Any help would be appreciated.  Thanks.

select distinct TRAN_DT, TRAN_TYPE, EMP_NUM

from

MYTABLE

where TRAN_TYP = 'A'

4 REPLIES
Enthusiast

Re: Counting consecutive dates

Please show some  data and how exactly you need the result..

Junior Contributor

Re: Counting consecutive dates

Yes, some more info would be helpful indeed, but this is probably what you want:

select EMP_NUM, TRAN_DT,
ROW_NUMBER() OVER (PARTITION BY EMP_NUM, dummy ORDER BY TRAN_DT) as consecutive_days
from
(
select TRAN_DT, EMP_NUM,
TRAN_DT - ROW_NUMBER() OVER (PARTITION BY EMP_NUM ORDER BY TRAN_DT) as dummy
from
(
select distinct TRAN_DT, TRAN_TYPE, EMP_NUM
from MYTABLE
where TRAN_TYP = 'A'
) as dt
) as dt

Dieter

Re: Counting consecutive dates

dnoeth,

That is great.  It has basically gotten me on the right track.

Much Thanks!!!  Wish there was a thumbs up button for you.

Teradata Employee

Re: Counting consecutive dates

Thanks for the smart tip!!!