Complex SQL query


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 ?



Tags (2)
Junior Contributor

Re: Complex SQL query

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)

Re: Complex SQL query

It can be thus too:

select k.ref_id,k.seq_nbr,k.depth,sum( over( order by rows unbounded preceding) sm 


(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

Re: Complex SQL query

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