SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Joining multiple queries		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

krchome
Starting Member

New Zealand
2 Posts
Posted - 02/03/2013 :  22:00:21  Show Profile  Reply with Quote
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
Edited by - krchome on 02/03/2013 22:15:37

James K
Flowing Fount of Yak Knowledge

1709 Posts
Posted - 02/03/2013 :  22:56:40  Show Profile  Reply with Quote
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

India
47960 Posts
Posted - 02/03/2013 :  23:06:31  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

krchome
Starting Member

New Zealand
2 Posts
Posted - 02/04/2013 :  15:44:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1709 Posts
Posted - 02/04/2013 :  15:57:15  Show Profile  Reply with Quote
You are very welcome, Kaushik - glad to be of help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47960 Posts
Posted - 02/04/2013 :  23:05:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000