Combine two queries and calcualte total of each and only display is over X amount...

The two queries below work perfectly independently. The goal is to calculate the totals of each query (Dollars + Chargeamount for the specific matter number) and if that total is over 500 display a new column with the combined amount, display… Otherwise do not display…


QUERY1
select clinum,sum(Dollars) from tabtime
INNER JOIN
            tabMatters ON tabMatters.MatKey = tabTime.MatKey
INNER JOIN
            tabClients on TabClients.CliKey = tabMatters.CliKey
where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
AND
 dateworked between
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
 AND
 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
Group By Clinum

QUERY2
select Clinum,sum(ChargeAmount) from tabcosts
INNER JOIN
            tabMatters ON tabMatters.MatKey = tabcosts.MatKey
INNER JOIN
            tabClients on TabClients.CliKey = tabMatters.CliKey
where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
AND
 PrtDate between
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
 AND
 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
Group By Clinum
plucenkoAsked:
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.

Ephraim WangoyaCommented:
try
with Query1
as
(
	select clinum, sum(Dollars) [Dollars] from tabtime
	INNER JOIN
				tabMatters ON tabMatters.MatKey = tabTime.MatKey
	INNER JOIN
				tabClients on TabClients.CliKey = tabMatters.CliKey
	where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
	AND 
	 dateworked between 
	DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
	 AND 
	 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
	Group By Clinum
),
Query2 
as
(
	select Clinum,sum(ChargeAmount) [ChargeAmount] from tabcosts
	INNER JOIN
				tabMatters ON tabMatters.MatKey = tabcosts.MatKey
	INNER JOIN
				tabClients on TabClients.CliKey = tabMatters.CliKey
	where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
	AND 
	 PrtDate between 
	DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
	 AND 
	 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
	Group By Clinum
)

select A.clinum, [Dollars], [ChargeAmount], 
		case 
			when [Dollars] + [ChargeAmount] > 500 then 
				[Dollars] + [ChargeAmount] 
			else
				Null
		end [combined amount]
from Query1 A
inner join Query2 B on A.Clinum = B.clinum

Open in new window

0
plucenkoAuthor Commented:
That would work but is there anyway of NOT showing the NULL Values at all?
0
Ephraim WangoyaCommented:

You could do a convert to varchar and show an empty string
with Query1
as
(
	select clinum, sum(Dollars) [Dollars] from tabtime
	INNER JOIN
				tabMatters ON tabMatters.MatKey = tabTime.MatKey
	INNER JOIN
				tabClients on TabClients.CliKey = tabMatters.CliKey
	where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
	AND 
	 dateworked between 
	DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
	 AND 
	 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
	Group By Clinum
),
Query2 
as
(
	select Clinum,sum(ChargeAmount) [ChargeAmount] from tabcosts
	INNER JOIN
				tabMatters ON tabMatters.MatKey = tabcosts.MatKey
	INNER JOIN
				tabClients on TabClients.CliKey = tabMatters.CliKey
	where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
	AND 
	 PrtDate between 
	DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
	 AND 
	 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
	Group By Clinum
)

select A.clinum, [Dollars], [ChargeAmount], 
		case 
			when [Dollars] + [ChargeAmount] > 500 then 
				CAST([Dollars] + [ChargeAmount] AS VARCHAR(32))
			else
				''
		end [combined amount]
from Query1 A
inner join Query2 B on A.Clinum = B.clinum

Open in new window

0
plucenkoAuthor Commented:
When I run this, I still see Combined amount - even though BLANK - the Dollars and Chargeamount have data in it - Is there anyway to simply HIDE those entries all together?

0023466      14080.50      9.44      
005442      2688.00      150.70      
0053339      1774.50      48.30      
00620      4959.50      50.18      
007320      102453.50      378.99      102832.49
0
Ephraim WangoyaCommented:
I'm not sure I understand your question. If you only want to display records where the combination is greater than 500, you add it to the where clause
with Query1
as
(
	select clinum, sum(Dollars) [Dollars] from tabtime
	INNER JOIN
				tabMatters ON tabMatters.MatKey = tabTime.MatKey
	INNER JOIN
				tabClients on TabClients.CliKey = tabMatters.CliKey
	where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
	AND 
	 dateworked between 
	DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
	 AND 
	 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
	Group By Clinum
),
Query2 
as
(
	select Clinum,sum(ChargeAmount) [ChargeAmount] from tabcosts
	INNER JOIN
				tabMatters ON tabMatters.MatKey = tabcosts.MatKey
	INNER JOIN
				tabClients on TabClients.CliKey = tabMatters.CliKey
	where WipStatus <> 'DEL' AND WipStatus<> 'DELOPEN' AND WipStatus <> 'DELREJECT' AND WipStatus <> 'OPEN' and WipStatus <>'released'
	AND 
	 PrtDate between 
	DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-2,getdate())), 0)
	 AND 
	 dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+0, 0))
	Group By Clinum
)

select A.clinum, [Dollars] + [ChargeAmount] [combined amount]
from Query1 A
inner join Query2 B on A.Clinum = B.clinum
where [Dollars] + [ChargeAmount] >= 500

Open in new window

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
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
Microsoft SQL Server

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.