Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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
52326 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

3873 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000