I have a situation here .. kindly guide me..
There are 2 tables, Case table (summary table) and Task table (detailed table)
The structure of tables:
- Trans_CT, à(transaction count could be 1,2,3 ..any numeric value)
Case_Rec_ID (major cols required)
A work group can contain multiple cases.
I need to find out the # of ready_to_work transactions per case and roll it up to work group level.
The transaction Count is given at Case level (it is predefined in the source application;need not bother about how the value arrived;) if we do a join between case and task table , trans_ct appears for all the tasks. But there is a business rule for calculating the # of transactions(summation).
A case contains several tasks like for ex. & corresponding ready to work indicator like below
Case1 – Task 1 Y
Case2 - Task 1 Y
If the RTW indicator is N for the last or recent task within the case then it can be ignored.
If the RTW indicator is Y for the last or recent task then we need consider the “trans_ct” value as # of RTW transaction for that case.
So Case 2 shall be ignored.
In Case 1, you can find the RTW flag has switched from Y-N-Y. If there is a switch of RTW flag within the case from Y to N then to Y and the last or recent tasks RTW flag is Y then we need to account for the number of switches & consider the corresponding “trans_ct” value. (# of switches * trans_ct)
Let’s say the trans_ct is 3 for Case 1. Now in this scenario, the # of RTW transactions would be (2*3=6 ).
Now I need to develop a query that can handle this scenario and roll up to work group level.
Tried the analytic functions lag,lead and first value, last value…but still trying to figure out the logic to handle this case1.
FYI- The Tasks need to be ordered by last_upd_ts
Please can you help me with the query.