All Forums Database
06 Sep 2014
Complex SQL query

I am new to Teradata, and have this requirement at job. In below table i have three columns given (REF_ID,Seq_Nbr and depth). I need to create a new_column which should have values based on Depth.
Example: 1) From Seq_nbr 1 to 32 depth are (1,4,6,6) so this should be considedered as one BOM structure hence i need to assign 1 in new_column.
2) at Seq_nbr 123 again we have depth 1, which means its a different BOM structure, now i need to assign 2 in my new_column.
3) at Seq_nbr  126 again we have depth 1, which means its a different BOM strucure, now i need to assign 3 in my new_column.
4) at Seq_nbr 129 again i find depth 1 which is going til seq_nbr 133, now i need to assign 4 to both these seq_nbr in my new Column.
Note: Basically whenever Depth 1 comes, i need to generate a new number in my new_column.  

REF_ID	Seq_Nbr	Depth	New_Column
1	1	1	1
1	12	4	1
1	16	6	1
1	32	6	1
1	123	1	2
1	126	1	3
1	129	1	4
1	133	3	4

 
Please help me how can i write SQL query for this ?
 
Thanks,
Shaum

dnoeth 4628 posts Joined 11/04
06 Sep 2014

Hi Shaum,
your logic is based on ordering by ref_id and seq_nbr?

sum(case when depth = 1 then 1 else 0 end)
over (order by ref_id, seq_nbr 
      rows unbounded preceding)

 

Dieter

Raja_KT 1246 posts Joined 07/09
07 Sep 2014

It can be thus too:
select k.ref_id,k.seq_nbr,k.depth,sum(k.nr) over( order by k.nr rows unbounded preceding) sm 
from
(select ref_id,seq_nbr,depth, case when depth-coalesce(min(depth)over(order by depth rows between unbounded preceding and current row),0)=0 then 1 else 0 end nr from your_table) k order by 2,3,4
 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

10 Sep 2014

Thank you so much Dnoeth ... your solution helped me out, and working as expexted. 

You must sign in to leave a comment.