How to get sequence numbers for a particular group based on consecutive values

Database
Enthusiast

How to get sequence numbers for a particular group based on consecutive values

Hi All,

I have a requirement to generate sequence numbers for a particular group based on consecutive values.

For example, I have my source data as below which gives the audit details conducted for each department by manager John sorted by date.

ManagerName  VisitDate(YYYY-MM-DD)      DeptName

John                2010-01-10                       Finance

John                2010-02-10                       Finance

John                2010-03-10                       Finance

John                2010-04-10                       Marketing

John                2010-05-10                       Production

John                2010-06-10                       Finance

John                2010-09-10                       Production

Now, I need to generate a sequence numbers in my query as like below

ManagerName  VisitDate(YYYY-MM-DD)      DeptName      Sequence

John                2010-01-10                       Finance           1

John                2010-02-10                       Finance           1

John                2010-03-10                       Finance           1

John                2010-04-10                       Marketing        2

John                2010-05-10                       Production       3

John                2010-06-10                       Finance           4

John                2010-09-10                       Production       5

It indirectly means, have to increment the sequence value by 1 whenever there is a change in my DeptName colum

Please help me to get the output as mentioned below

5 REPLIES
Senior Apprentice

Re: How to get sequence numbers for a particular group based on consecutive values

Hi Sri,

you need a DENSE_RANK, this is not supported before TD14.10.

But there's a workaround:

Missing Functions: DENSE_RANK

Enthusiast

Re: How to get sequence numbers for a particular group based on consecutive values

In teradata 14.20 dense_rank is available.

Teradata Employee

Re: How to get sequence numbers for a particular group based on consecutive values

Hi Dieter,

I have same requirement in my project as mentioned by Sri in beginning. I am working on TD 13.10 and Missing Functions: DENSE_RANK logic is not working for me as expected.

Sample Data:

ManagerName VisitDate DeptName
John 2010-10-01 Finance
John 2010-10-02 Finance
John 2010-10-03 Finance
John 2010-10-04 Marketing
John 2010-10-05 Production
John 2010-10-06 Finance
John 2010-10-07 Production

and i am using following query:-

SEL t1.deptname,t1.visitdate
,dt.rnk AS rnk
FROM
dep1 AS t1
INNER JOIN
(
SELECT DeptName
,VisitDate
,RANK() OVER (PARTITION BY DeptName ORDER BY VisitDate) AS rnk
FROM dep1
GROUP BY 1,2
) dt
ON t1.deptname=dt.deptname
AND t1.visitdate=dt.visitdate ;

and i am getting following result which is not same as expected:-

DeptName      VisitDate     rnk
Finance 2010-10-01 1
Finance 2010-10-02 2
Finance 2010-10-03 3
Marketing 2010-10-04 1
Production 2010-10-05 1
Finance 2010-10-06 4
Production 2010-10-07 2

i am looking for following result:-

DeptName    VisitDate     rnk
Finance 2010-10-01 1
Finance 2010-10-02 1
Finance 2010-10-03 1
Marketing 2010-10-04 2
Production 2010-10-05 3
Finance 2010-10-06 4
Production 2010-10-07 5



Please suggest me how to get above answerset.

Regards,

Javed

Senior Apprentice

Re: How to get sequence numbers for a particular group based on consecutive values

Hi Javed,

in fact this is not a dense rank, but a similar problem:

select dt.*,
sum(flag) over (partition by ManagerName
order by VisitDate
rows unbounded preceding)
from
(
select
d.*,
case when min(DeptName)
over (partition by ManagerName
order by VisitDate
rows between 1 preceding and 1 preceding)
= DeptName then 0 else 1 end as flag
from dep as d
) as dt
Teradata Employee

Re: How to get sequence numbers for a particular group based on consecutive values

perfect solution.This is what i am looking for.

Thanks a lot Deiter.