All Forums Database
Nits 10 posts Joined 05/06
08 May 2006
Something in Teradat as is CONNECT BY in Oracle

I am working on the design phase of a project. The coding for this project needs to be done in Teradata. The logic is as below: Item Sales Cumulative Percentage Service level Classification Item1 10 10 AItem2 9 19 AItem3 8 27 B The criteria of assigning service level A is that those items contributing first 15% of sales are eligible for it. Since Item2 is partly contributing to top 15% the service level assigned is A to Item2. Seconds Case: Item Sales Cumulative Percentage Service level Classification Item1 15 15 AItem2 4 19 BItem3 3 22 B Since item1 is consuming the 15% sales criteria, it is only one to set level A.So, to decide the service level classification for an item, the cumulative percentage for preceding item has to be seen and checked with service level criteria. It means that to assign service level for item 2, the cumulative %age of item 1 has to be checked. If for item 1 the Cumulative %age is >= 15% (Service Level Criteria), item 2 would get next class i.e B, otherwise item 2 would get A. To implement this logic, I am looking for similar function in Teradata as CONNECT BY in Oracle. Please tell me if you have any inputs to solve this problem.Please give valuable inputsRegardsNits

Sunil Agrawal 12 posts Joined 07/04
08 May 2006

select item , sales , sum(sales) over () total_sum , sum(sales) over (rows unbounded preceding) as cum_sum , (cum_sum-sales)*100/total_sum as start_cum_perc , (case when start_cum_perc < 15 then 'A' else 'B' end) as service_levelfrom tablename;Hope this helps.

You must sign in to leave a comment.