Windows Aggregate - Deriving the Sequence Number for Occurences

Database
Enthusiast

Windows Aggregate - Deriving the Sequence Number for Occurences

Got a scenario where we need to assign sequence to records based on its occurence and timestamp. I am not able to get it just with FIRST_VALUE and not sure if we need to do a self join on this.

 

Here is a simple case made:

I have a table with columns PName, PSeqNo, PTimestamp (image attached). We need to generate a new sequence number (called PDerivedSeqNo) based on the occurence of Name. If the subsequent entry of PName is after its last occurence without having any other PName between them then the two entries are sequenced same. If we have a new PName entry in between the similar names then the sequence should be incremented by 1.TD-Logic.PNG  

 

 

 


Accepted Solutions
Junior Contributor

Re: Windows Aggregate - Deriving the Sequence Number for Occurences

You need two steps.

  1. Calculate a flag based on the previous row's value, 0 if it's equal to the current vrow otherwise 1.
  2. Calculate a SUM(flag) OVER (ORDER by timestamp)
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Windows Aggregate - Deriving the Sequence Number for Occurences

You need two steps.

  1. Calculate a flag based on the previous row's value, 0 if it's equal to the current vrow otherwise 1.
  2. Calculate a SUM(flag) OVER (ORDER by timestamp)
Enthusiast

Re: Windows Aggregate - Deriving the Sequence Number for Occurences

Thank You, Dieter