Database
Enthusiast

Sql query

Hi !

I have a table on teradata with 4 columns. The ratio may/may not be 100%.
I want to assign the teams as per the ratio. E.g 90 tasks have been assigned to all teams with Hierarchy 1.
Team red and Blue have been assigned teh hierarchy of 1. And the distribution ration between team red
and blue is 10 and 20. So 90X(1/3) = 30 tasks will go to Team red. And 90X(2/3) = 60 tasks will go to Team Blue.
All the 4 columns can change and even then the code should be flexible enough to handle this.
I was thinking of stored procedures with a for loop.
Can you please guide me as to how this logic can be acheived? Any help is appreciated.

Team Hierarchy Ratio TotalCount
Red 1 10 90
Blue 1 20 90
Yellow 2 30
Orange 3 40

Thanks!
2 REPLIES
Junior Contributor

Re: Sql query

What about team yellow/orange?

select
tab.*,
TotalCount * Ratio / sum(Ratio) over (partition by Hierarchy)
from tab

Dieter
Enthusiast

Re: Sql query

This was just an example. The population of thsi tables is controlled by the users. So whatever the data, I should use the Ratio and the Hierrachy to decide the tasks.
In this eg. Yellow and Orange, all the tasks (30to yellow/40 to Orange) should be assigned .