How to combine two queries?

Hello Experts.

I need a query that displays all records from one table and a sum of expenses for each of those records that comes from another table.  The following queries work well apart, but they don't work when I combine them.  When I combine them I only get the records for which there is an expense.  

This query provides the list I need, and it works as needed:
SELECT DISTINCT
	DP.POL_NO,
	DP.POL_EFF_DT,
	DP.POL_EXP_DT
FROM DIM_POLICY DP
WHERE DP.MSTR_POL_NO = '517242'

Open in new window


It returns the following:
________________________________________________________
51724200    |          2007-05-11    |         2008-05-11
51724201    |        2008-05-11    |         2009-05-11
51724202    |        2009-05-11    |         2010-05-11
51724203    |        2010-05-11    |         2011-05-11
51724204    |        2011-05-11    |         2012-05-11
51724205    |        2012-05-11    |         2013-05-11
51724206    |        2013-05-11    |         2014-05-11
51724207    |        2014-05-11    |         2015-05-11
________________________________________________________


This query provides the sum of losses if any exist, and it works as intended:
SELECT DP.POL_NO, SUM(VFWHLT.TRANS_AMT)
FROM V_FACT_WIND_HAIL_LOSS_TRAN VFWHLT
INNER JOIN DIM_POLICY DP
ON DP.POLICY_ID=VFWHLT.POLICY_ID
WHERE DP.MSTR_POL_NO = '517242' AND VFWHLT.TRANS_ALLOC_CD NOT IN ( 'EXP','MISC','SALV','SUBR'  )
GROUP BY DP.POL_NO

Open in new window


It returns the following:
____________________
51724201   |   $100000
____________________

How can I combine the two queries so that I get the full list of numbers and the sum of expenses for each?  The above should be displayed as:

___________________________________________________________________________
POL_NO      |           POL_EFF_DT        |         POL_EXP_DT      |  LOSS_SUM
51724200    |          2007-05-11          |         2008-05-11          |         
51724201    |        2008-05-11          |         2009-05-11          |     100000
51724202    |        2009-05-11          |         2010-05-11          |         
51724203    |        2010-05-11          |         2011-05-11          |         
51724204    |        2011-05-11          |         2012-05-11          |         
51724205    |        2012-05-11          |         2013-05-11          |         
51724206    |        2013-05-11          |         2014-05-11          |         
51724207    |        2014-05-11          |         2015-05-11          |         
___________________________________________________________________________

Any help would be greatly appreciated.

Thanks,
J
ferguson_jeraldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
SELECT DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT, SUM(VFWHLT.TRANS_AMT)
FROM DIM_POLICY DP 
	LEFT JOIN V_FACT_WIND_HAIL_LOSS_TRAN VFWHLT ON DP.POLICY_ID=VFWHLT.POLICY_ID
WHERE DP.MSTR_POL_NO = '517242' AND VFWHLT.TRANS_ALLOC_CD NOT IN ( 'EXP','MISC','SALV','SUBR'  )
GROUP BY DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT

Open in new window

0
ferguson_jeraldAuthor Commented:
Thanks for such  a quick reply Vitor.  Unfortunately it doesn't provide all of the POL-NO, just the one with a loss payment.  Here are the results from your suggestion:

POL_NO       |           POL_EFF_DT       |         POL_EXP_DT      |  LOSS_SUM
51724201    |        2008-05-11          |         2009-05-11          |       100000

Any other suggestions.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
It may have with the WHERE clause? This part VFWHLT.TRANS_ALLOC_CD NOT IN ( 'EXP','MISC','SALV','SUBR' ) maybe don't let to return the other rows? Try to run without this part.
SELECT DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT, SUM(VFWHLT.TRANS_AMT)
FROM DIM_POLICY DP 
	LEFT JOIN V_FACT_WIND_HAIL_LOSS_TRAN VFWHLT ON DP.POLICY_ID=VFWHLT.POLICY_ID
WHERE DP.MSTR_POL_NO = '517242'
GROUP BY DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ferguson_jeraldAuthor Commented:
That's it!  Any suggestion on how I can exclude those types of transactions?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you exclude it will be back to the previous version.
You can try to add them to your SELECT columns:
SELECT DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT, VFWHLT.TRANS_ALLOC_CD, SUM(VFWHLT.TRANS_AMT)
FROM DIM_POLICY DP 
	LEFT JOIN V_FACT_WIND_HAIL_LOSS_TRAN VFWHLT ON DP.POLICY_ID=VFWHLT.POLICY_ID
WHERE DP.MSTR_POL_NO = '517242'
GROUP BY DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT, VFWHLT.TRANS_ALLOC_CD 

Open in new window

This makes sense to you?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ferguson_jeraldAuthor Commented:
I got it.  I was able to change that part of the WHERE clause to include only certain payment types instead of excluding some and it worked.  You're help was invaluable this morning...Thanks!!!

Here's what I ended up with:

SELECT DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT, SUM(VFWHLT.TRANS_AMT)
FROM DIM_POLICY DP 
	LEFT JOIN V_FACT_WIND_HAIL_LOSS_TRAN VFWHLT ON DP.POLICY_ID=VFWHLT.POLICY_ID
WHERE DP.MSTR_POL_NO = '517242' AND (VFWHLT.TRANS_ALLOC_CD IS NULL OR VFWHLT.TRANS_ALLOC_CD IN ('LOSS', 'RCC'))
GROUP BY DP.POL_NO, DP.POL_EFF_DT, DP.POL_EXP_DT

Open in new window

0
ferguson_jeraldAuthor Commented:
Your last query works as well.  It just gives the details, which is very helpful.  Thanks again!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see. You only included those ones that you want. Clever ;)
Cheers.
0
Mike EghtebasDatabase and Application DeveloperCommented:
I hope you don't mind me adding this with temp table. Alternate solution but missing some additional filters.
Select POL_NO, POL_EFF_DT, POL_EXP_DT
, (Select Sum(TRANS_AMT) From #V Where POL_NO = t.POL_NO) As Loss
From #DP t

-- outputs:
51724200	2007-05-11	2008-05-11	2100
51724201	2008-05-11	2009-05-11	7000
51724202	2009-05-11	2010-05-11	NULL
51724203	2010-05-11	2011-05-11	NULL
51724204	2011-05-11	2012-05-11	NULL
51724205	2012-05-11	2013-05-11	NULL
51724206	2013-05-11	2014-05-11	NULL
51724207	2014-05-11	2015-05-11	9000

Open in new window


create table #DP(POL_NO int, POL_EFF_DT date, POL_EXP_DT date);
insert #DP(POL_NO, POL_EFF_DT, POL_EXP_DT) values
(51724200    ,          '2007-05-11'    ,         '2008-05-11')
,(51724201    ,        '2008-05-11'    ,         '2009-05-11')
,(51724202    ,        '2009-05-11'    ,         '2010-05-11')
,(51724203    ,        '2010-05-11'    ,         '2011-05-11')
,(51724204    ,        '2011-05-11'    ,         '2012-05-11')
,(51724205    ,        '2012-05-11'    ,         '2013-05-11')
,(51724206    ,        '2013-05-11'    ,         '2014-05-11')
,(51724207    ,        '2014-05-11'    ,         '2015-05-11')
select * from #DP

create table #V(POL_NO int, TRANS_AMT int);
insert #V(POL_NO, TRANS_AMT) values
(51724200    ,  1000)
,(51724200    , 1100 )
,(51724201    , 3000 )
,(51724201    , 4000 )
,(51724207    ,  9000);
select * from #V;

Open in new window


Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.