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 |
divan
Posting Yak Master
153 Posts |
Posted - 2013-06-24 : 12:10:10
|
Friends;I have two tables E0025_0604 which has the following rowsPOLICY_NUMBER TAIL_PREMTX 10063 8763.00 TX 10063 19469.00TX 10063 22982.00TX 10063 22982.00AND PREMIUM_DETAILPOLICY_NUMBER PREMIUMTX 10063 8763.00TX 10063 19469.00TX 10063 22982.00TX 10063 22984.00of course there are a lot more records and a lot more fields in each table but i just select one policy..I have written the following script SELECT DISTINCT POLICY_NUMBER, TAIL_PREMINTO #TEMP1FROM E0025_0604 WHERE TAIL_PREM <> 0 AND POLICY_NUMBER = 'TX 10063' AND POLICY_NUMBER NOT LIKE 'Q%' SELECT * FROM #TEMP1 ORDER BY POLICY_NUMBER, TAIL_PREMSELECT DISTINCT P.POLICY_NUMBER,P.PREMIUM_LOB,P.PREMIUM,P.BILLED_PREMIUM,P.WRITTEN_PREMIUM,P.ANNUALIZED_PREMIUM,P.FULL_TERM_PREM FROM PREMIUM_DETAIL PINNER JOIN #TEMP1 T ON P.POLICY_NUMBER = T.POLICY_NUMBER WHERE P.PREMIUM_LOB LIKE 'T%' ORDER BY P.POLICY_NUMBER,P.PREMIUM_LOB, P.PREMIUMCan someone please help..ThanksWhat I am trying to get from this script to select/display all records that are not in either table for a particular policy.. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 12:28:33
|
Show us what will your expected output for the posted data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-06-24 : 13:13:09
|
I am expecting the following from premium_detail to show upTX 10063 22984.00 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 13:30:18
|
ok. so this?SELECT POLICY_NUMBER, TAIL_PREMFROM(SELECT POLICY_NUMBER, TAIL_PREM, 1 AS CatFROM E0025_0604WHERE POLICY_NUMBER = 'TX 10063' UNION ALLSELECT POLICY_NUMBER, PREMIUM,2FROM PREMIUM_DETAILWHERE POLICY_NUMBER = 'TX 10063' )tGROUP BY POLICY_NUMBER, TAIL_PREMHAVING COUNT(DISTINCT Cat) = 1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-06-24 : 14:12:06
|
Thanks... It works great |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-25 : 00:51:38
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|