I need help in the query to insert/update the summary count to the TableAgentSummary table
after daily incremental load.
Note: CustomerTable contains 500 plus millions records and have nearly 500 plus rules.
Here is the tables with sample data.
TableRules:
BusinessRuleId BRName Rule
1 Indicator1 region = 'East' then 1 Else 0
2 Indicator2 region = 'West' and Country = 'US' then 1 Else 0
3 Indicator3 Country = 'US' and Priorty= 'High' then 1 Else 0
CustomerTable
AgentId Name Region Country Priorty TimetoResolvedIssue(Mins) createdDate
1 Anne East US High 10 01/20/2021
1 Anne West US Low 15 02/21/2021
1 Anne South UK Medium 15 04/21/2021
The output to load into TableAgentSummary:
AgentId Name TotalTime Indicator1 Indicator2 Indicator3
1 Anne 25 1 1 0
Got the expected output for initial load.
insert into TableAgentSummary (Agentid,Name,Indicator1,Indicator2,Indicator2
Select Agentid,Name,
Indicator1 = Case when region = 'East' then 1 Else 0 end,
Indicator2 = Case When region = 'West' and Country = 'US' then 1 Else 0 end,
Indicator3 = Case When Country = 'US' and Priorty= 'High' then 1 Else 0 end
Thanks for your help in advance !!