Friday, May 10, 2013

[SQL Server Data Warehousing] How to avoid primary key violation and insert rows based on following scenario??


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

No comments:

Post a Comment

Search This Blog