All Forums Database
slakshmipathy 10 posts Joined 02/14
14 Feb 2014
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

Thanks, Sri
dnoeth 4628 posts Joined 11/04
14 Feb 2014

Hi Sri,
you need a DENSE_RANK, this is not supported before TD14.10.
But there's a workaround:
Missing Functions: DENSE_RANK

Dieter

kumar_abhilash 15 posts Joined 04/14
27 Apr 2014

In teradata 14.20 dense_rank is available.

javed.ansari2 2 posts Joined 04/15
03 Jun 2015

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

dnoeth 4628 posts Joined 11/04
03 Jun 2015

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

 

Dieter

javed.ansari2 2 posts Joined 04/15
04 Jun 2015

perfect solution.This is what i am looking for.
Thanks a lot Deiter.

You must sign in to leave a comment.