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.
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 InvoiceSELECTa.NAME, SUM((t.AMOUNT)) AS LifeTimeInvoiceFROM dbo.DR_ACCS aJOIN dbo.DR_TRANS t ON a.ACCNO = t.ACCNOWHERE 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 SELECTa.NAME, ABS(SUM((t.AMOUNT))) AS LifeTimePaymentFROM dbo.DR_ACCS aJOIN dbo.DR_TRANS t ON a.ACCNO = t.ACCNOWHERE a.CustomerCode = 1326499 AND TRANSTYPE IN (4, 5) --PaymentAND REF1 <> 'Credit'GROUP BY a.NAME-- Lifetime CreditSELECTa.NAME, SUM((t.AMOUNT)) AS LifeTimeCreditFROM dbo.DR_ACCS aJOIN dbo.DR_TRANS t ON a.ACCNO = t.ACCNOWHERE 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.NAMEThe 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. CheerskaushikKaushik |
|
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 LifeTimeCreditFROM dbo.DR_ACCS a JOIN dbo.DR_TRANS t ON a.ACCNO = t.ACCNOWHERE a.CustomerCode = 1326499GROUP BY a.NAME |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-03 : 23:06:31
|
[code]SELECTa.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 LifeTimeCreditFROM dbo.DR_ACCS aJOIN dbo.DR_TRANS t ON a.ACCNO = t.ACCNOWHERE a.CustomerCode = 1326499 GROUP BY a.NAME[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|