Writing a Store Procedure to create a numeric sequence

Analytics
Enthusiast

Writing a Store Procedure to create a numeric sequence

Hello, I've been a heavy Teradata user for a long time but thanks to the existence of PARTITIONS it seems like I never needed to use Store Procedures.

 

The thing is in this ocassion it seems that I need to write one, but I have not been able to find the documentation I need, so I'd be extremely grateful if you guys could help me.

 

This is the summary of what I need. I would truly be extremely grateful if you guys could help me. Thanks in advance.

 

 

duda_teradata.PNG


Accepted Solutions
Teradata Employee

Re: Writing a Store Procedure to create a numeric sequence

Seems I left out the most important clause in my first reply:

SUM(increment_value) OVER (PARTITION BY Register_Type ORDER BY ... ROWS UNBOUNDED PRECEDING AND CURRENT ROW)

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Writing a Store Procedure to create a numeric sequence

Modify your existing query slightly:

  CASE WHEN days_between_transactions < 5 THEN 0 ELSE 1 END AS increment_value

Then use an outer query to SUM(increment_value) OVER (PARTITION BY Register_Type ORDER BY ...)

Enthusiast

Re: Writing a Store Procedure to create a numeric sequence

Maybe I did not make myself clear enough Smiley Sad. If I understood your proposed solution correctly, that would in the case of the register type A, give a value equal to 4 in each of the 6 rows belonging to A. Because with your CASE, on register type A we would have [1,1,1,1,0,0], so then on the outer query making the partition on [A,B,C,D] we would always sum 4 in the case of the A type (see column on red).

 

Thanks a lot again!!

 

result_4.PNG

 

What I need for register type A is the following sequence: [1,2,3,4,4,4]

Enthusiast

Re: Writing a Store Procedure to create a numeric sequence

I thought a lot how to improve my requirement and make it more clear, and I think this is it!

 

Again THANKS A LOT!! I am really grateful for this.

 

clear.PNG

Teradata Employee

Re: Writing a Store Procedure to create a numeric sequence

Seems I left out the most important clause in my first reply:

SUM(increment_value) OVER (PARTITION BY Register_Type ORDER BY ... ROWS UNBOUNDED PRECEDING AND CURRENT ROW)

Enthusiast

Re: Writing a Store Procedure to create a numeric sequence

#RESPECTTTT!!!

 

THANKS it's a great solution, I was astonished when I read it. Thanks a lot for real.