We help IT Professionals succeed at work.

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
Comment
Watch Question

Ephraim WangoyaSoftware Engineer

Commented:
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

Author

Commented:
That would work but is there anyway of NOT showing the NULL Values at all?
Ephraim WangoyaSoftware Engineer

Commented:

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

Author

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
Software Engineer
Commented:
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