Good day Community,
Please kindly assist with the following request in Teradata 16.
In the above HR request I need to calculate headcount with built in exceptions.
For example, scenario 3, I have to count 'Filled' positions however in the case were the "Position" is an 'Expat' (this "Position" is derived by adding an 'E' prefix on the 'Permanent' value "Position" - E123654 is Expat and 123654 is Permanent), I have to check if the 'Permanent' "Position" = 'Filled', if it is true, then I do not count the 'Expat' "Position".
Please see Headcount Rules in the image attached for the different scenario's and the rule exceptions/conditions.
The issue is with building the 'check rule' and extracting the numeric equivalent for 'Permanent', searching the set and returning an answer.
If anyone can assist, it would be much appreciated.