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 ?
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)
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
(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