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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joining multiple queries

Author  Topic 

krchome
Starting Member

2 Posts

Posted - 2013-02-03 : 22:00:21
Hi,

I have 3 T-SQL queries(SELECT statements) all operating on same set of two tables on SQL Server 2008 R2 but with different search criterias(WHERE clauses).
I wish to make a single query by suitably combining these queries so that it returns a table containing 4 columns.

The queries are :

--Lifetime Invoice
SELECT
a.NAME,
SUM((t.AMOUNT)) AS LifeTimeInvoice
FROM dbo.DR_ACCS a

JOIN dbo.DR_TRANS t

ON a.ACCNO = t.ACCNO
WHERE a.CustomerCode = 1326499 AND TRANSTYPE = 1
AND REF1 = 'invoice' AND NOT (t.INVNO LIKE 'CR%' OR REF2 LIKE 'T#%')
GROUP BY a.NAME

--Lifetime Payment
SELECT
a.NAME,
ABS(SUM((t.AMOUNT))) AS LifeTimePayment
FROM dbo.DR_ACCS a

JOIN dbo.DR_TRANS t

ON a.ACCNO = t.ACCNO
WHERE a.CustomerCode = 1326499 AND TRANSTYPE IN (4, 5) --Payment
AND REF1 <> 'Credit'
GROUP BY a.NAME

-- Lifetime Credit
SELECT
a.NAME,
SUM((t.AMOUNT)) AS LifeTimeCredit
FROM dbo.DR_ACCS a

JOIN dbo.DR_TRANS t

ON a.ACCNO = t.ACCNO
WHERE a.CustomerCode = 1326499
AND ((TRANSTYPE IN (4, 5) AND REF1 = 'Credit')
OR (TRANSTYPE = 1 AND (t.INVNO LIKE 'CR%' OR REF2 LIKE 'T#%')))
GROUP BY a.NAME


The output single query should return the 4 columns as Name, LifeTimeInvoice, LifeTimePayment, LifeTimeCredit from the above queries. This is then intended to be used in SSRS to generate a report.

Please help me with a solution.

Cheers

kaushik

Kaushik

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-03 : 22:56:40
Move all the conditions that are not common to all three into CASE expressions - like shown below.
SELECT a.NAME,
SUM(
CASE
WHEN TRANSTYPE = 1 AND REF1 = 'invoice' AND
NOT (t.INVNO LIKE 'CR%' OR REF2 LIKE 'T#%') THEN t.AMOUNT
ELSE 0
END) AS LifeTimeInvoice,
ABS(SUM(
CASE
WHEN TRANSTYPE IN (4, 5) --Payment
AND REF1 <> 'Credit' THEN t.AMOUNT
ELSE 0
END )) AS LifeTimePayment,
SUM(
CASE
WHEN (TRANSTYPE IN (4, 5) AND REF1 = 'Credit')
OR (TRANSTYPE = 1 AND (t.INVNO LIKE 'CR%' OR REF2 LIKE 'T#%'))
THEN t.AMOUNT
ELSE 0
END ) AS LifeTimeCredit
FROM dbo.DR_ACCS a
JOIN dbo.DR_TRANS t
ON a.ACCNO = t.ACCNO
WHERE a.CustomerCode = 1326499
GROUP BY
a.NAME
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-03 : 23:06:31
[code]
SELECT
a.NAME,
SUM(CASE WHEN TRANSTYPE = 1 AND REF1 = 'invoice' AND NOT (t.INVNO LIKE 'CR%' OR REF2 LIKE 'T#%') THEN t.AMOUNT ELSE 0 END) AS LifeTimeInvoice,
SUM(CASE WHEN TRANSTYPE IN (4, 5) AND REF1 <> 'Credit' THEN t.AMOUNT ELSE 0 END) AS LifeTimePayment,
SUM( CASE WHEN ((TRANSTYPE IN (4, 5) AND REF1 = 'Credit') OR (TRANSTYPE = 1 AND (t.INVNO LIKE 'CR%' OR REF2 LIKE 'T#%'))) THEN t.AMOUNT ELSE 0 END) AS LifeTimeCredit
FROM dbo.DR_ACCS a
JOIN dbo.DR_TRANS t
ON a.ACCNO = t.ACCNO
WHERE a.CustomerCode = 1326499
GROUP BY a.NAME
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

krchome
Starting Member

2 Posts

Posted - 2013-02-04 : 15:44:53
Hi James/Visakh,

I thank you both very much. Both of your replies are working fine and they follow the same approach which I thought should work but couldn't get the syntax right by myself.

Kaushik
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-04 : 15:57:15
You are very welcome, Kaushik - glad to be of help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 23:05:23
quote:
Originally posted by krchome

Hi James/Visakh,

I thank you both very much. Both of your replies are working fine and they follow the same approach which I thought should work but couldn't get the syntax right by myself.

Kaushik


you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -