|
|
|
Junior Member
 
Group: Forum Members
Last Login: 7/17/2006 1:31:00 AM
Posts: 10,
Visits: 1
|
|
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 A
Item2 9 19 A
Item3 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 A
Item2 4 19 B
Item3 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 inputs
Regards
Nits
Regards
Nits
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 5/11/2006 7:20:00 PM
Posts: 24,
Visits: 1
|
|
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_level
from tablename;
Hope this helps.
|
|
|
|