Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cpeterson10
Starting Member
3 Posts |
 Posted - 2011-05-31 : 17:01:25
|
| Is it possible to combine the following three queries into one?select sum(cov_prem) as cov_prem, policy_no into #temp1 from LEGACY_TRANS_DETAILgroup by policy_noselect sum(premium_amount) as cov_prem, policy_no into #temp2 from legacy_fms_commissiongroup by policy_noselect a.cov_prem, b.cov_prem, b.policy_no from #temp1 a join #temp2 bon a.policy_no = b.policy_no where a.cov_prem<>b.cov_prem |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
cpeterson10
Starting Member
3 Posts |
Posted - 2011-05-31 : 17:30:26
|
| Sorry I should have explained. I wanted to bypass the step of creating two temp tables but just wanted to create one query that would aggregate the premium from both tables by policy and show only the rows where the amounts were different.regards,craig |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-06-01 : 03:20:51
|
| Try this :Select sum(cov_prem), sum(premium_amount) ,policy_no from ( Select sum(a.cov_prem) cov_prem, 0 premium_amount, a.policy_no from LEGACY_TRANS_DETAIL a group by a.policy_no union all Select 0 cov_prem, sum(b.premium_amount) premium_amount, b.policy_no from legacy_fms_commission b group by b.policy_no ) as tmp group by tmp.policy_no having sum(tmp.cov_prem) <> sum(tmp.premium_amount) Regards,BohraI am here to learn from Masters and help new bees in learning. |
|
|
|
cpeterson10
Starting Member
3 Posts |
Posted - 2011-06-01 : 10:49:39
|
| Thanks Bohra! That worked! |
|
|
|
|
|
|
|
|
Combine Queries