Hi All,
Appreciate all your advice and comments on below issue pls,
Thanks .
Mira.
what I am going to achieve here is to combine rows in two tables , based on policy_id and monthkey and get SUM of columns of netpaid, GST , AND a specific value from another table base on value on ‘TYPE’ column.
Then I need to insert values into a factTable, which has columns as below:
Policy_id (pk), monthkey(pk), netPaid(decimal), GST(decimal), claim_amount(decimal)
I am using a stored procedure to generate two temp tables as below, which then combine using a UNION clause before Merge statement use to insert data into factTable.
Temp table names are (#Summary_Cancel , #Summary_Active ) produces following outputs ..
#Summary_Cancel
Policy_id | Monthkey | netPaid | GST | Type |
150 | 201303 | 415.00 | 41.50 | 99 |
151 | 201303 | 350.00 | 35.00 | 99 |
174 | 201304 | 110.00 | 11.00 | 99 |
#Summary_Active
Policy_id | Monthley | netPaid | GST | Type |
150 | 201303 | 2500.00 | 250.00 | 1 |
151 | 201301 | 3000.00 | 300.00 | 1 |
174 | 201304 | 5500.00 | 550.00 | 1 |
Then I am using UNION statement as follows:
Select policy_id, Monthkey, netPaid, GST, Type
Into # FinalSummary
From #Summary_Cancel
UNION ALL
Select policy_id, Monthkey, netPaid, GST, Type
From #Summary_Active
Then I use Merge Statement Target as factTable, to insert values , in here I use value of ‘Type’ column in the tables to select a value from another table (cm) using a CASE statement,
something like CASEWHEN FinalSummary.Type = 99 THEN 0
ELSE cm.outstanding_amnt
END AS outstanding_amnt
I expect to see final output in factTable as below:, i.e. what I want to group rows on policy_id and Monthkey and do SUM function to add relative columns and insert values into fact Table, which is not happening now with UNION ALL clause, and when I am getting "Violation of PRIMARY KEY constraint" error message.
Main issue is "Type" column which does not need to 'SUM' its merely used as to find
value from other Table ( Using CASE )
Policy_id | Monthkey | netPaid | GST | Type |
150 | 201303 | 2915.00 | 291.50 | It will get if from cm |
151 | 201303 | 350.00 | 35.00 | It will get if from cm |
151 | 201301 | 3000.00 | 300.00 | It will get if from cm |
174 | 201304 | 5610.00 | 561.00 | It will get if from cm |
.
social.technet.microsoft.com/Forums
