Skip to content
Advertisement

Assignation counting up to maximum

I have the below 2 tables:

aux1:

PC A MAXI
PC1 A1 1
PC1 A2 2
PC2 A1 1
PC2 A2 2

aux:

VK D PC
VK1 8 PC1
VK2 7 PC1
VK3 6 PC2
VK4 5 PC2
VK5 4 PC1
VK6 3 PC1
VK7 2 PC2
VK8 1 PC2

I would like to obtain the following output:

VK D PC A ORDER_A_PC ORDER_PC
VK1 8 PC1 A1 1 1
VK2 7 PC1 A2 1 2
VK3 6 PC2 A1 1 1
VK4 5 PC2 A2 1 2
VK5 4 PC1 A2 2 3
VK6 3 PC1 NA NA NA
VK7 2 PC2 A2 2 3
VK8 1 PC2 NA NA NA

The logic for the columns is:

A: ordering aux by D desc and partitioning by PC, assign A (A1 or A2) until the count over partition reaches the maxi configured in table aux1 for each PC and A.
ORDER_A_PC: ordered count over partition by A and PC until the count exceeds the maxi for that PC, A in table aux1
ORDER_PC: same as ORDER_A_PC but partitioned by just PC.

Advertisement

Answer

Nice little problem. You can distribute the assets preprocessing the ranges and then joining by range.

For example, you can do:

Result:

See running example at db<>fiddle.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement