How can I combine these queries?

Question

I have 5 queries I'd like to join together. Basically what they do is they go though the database and select how much a tenant has paid, and how much a tenant owes based on how long ago he or she was charged.

I have four categories
Charge < 30 days old
Charge < 60 AND >= 30 days old
Charge < 90 AND >= 60 days old
Charge > 90 days old

I know how to get all those values separately, but how can I get them together, plus the amount the tenant has paid?

Here are my queries:
Amount the Tenant Has Paid

SELECT TransactionCode, TenantID, SUM(Amount) AS Paid FROM tblTransaction
WHERE Amount > 0
GROUP BY TransactionCode, TenantID

Charge is less than 30 days old

SELECT TransactionCode, TenantID, SUM(Amount) AS ChargedCurrent FROM tblTransaction
WHERE Amount < 0 AND TransactionDate > DATEADD("dd", -30, GETDATE())
GROUP BY TransactionCode, TenantID

Charge is less than 60 days old, but older than 29 days

SELECT TransactionCode, TenantID, SUM(Amount) AS ChargedOver30 FROM tblTransaction
WHERE Amount < 0 AND TransactionDate > DATEADD("dd", -60, GETDATE()) AND TransactionDate <= DATEADD("dd", -30, GETDATE())
GROUP BY TransactionCode, TenantID

Charge is less than 90 days old, but older than 59 days

SELECT TransactionCode, TenantID, SUM(Amount) AS ChargedOver60 FROM tblTransaction
WHERE Amount < 0 AND TransactionDate > DATEADD("dd", -90, GETDATE()) AND TransactionDate <= DATEADD("dd", -60, GETDATE())
GROUP BY TransactionCode, TenantID

Charge is older than 89 days

SELECT TransactionCode, TenantID, SUM(Amount) AS ChargedOver90 FROM tblTransaction
WHERE Amount < 0 AND TransactionDate <= DATEADD("dd", -90, GETDATE())
GROUP BY TransactionCode, TenantID

How can I get these all with one query?

score 7 · Accepted Answer · answered Jun 01 '09 at 14:50

7

It can be done like this:

SELECT TransactionCode, TenantID, 
SUM(CASE WHEN Amount > 0 then Amount ELSE 0 END) AS Paid,
SUM(CASE WHEN Amount < 0 AND TransactionDate > DATEADD("dd", -30, GETDATE()) THEN Amount ELSE 0 END) AS ChargedCurrent,  
SUM(CASE WHEN Amount < 0 AND TransactionDate > DATEADD("dd", -60, GETDATE()) AND TransactionDate <= DATEADD("dd", -30, GETDATE()) THEN Amount ELSE 0 END) AS ChargedOver30
SUM(CASE WHEN Amount < 0 AND TransactionDate > DATEADD("dd", -90, GETDATE()) AND TransactionDate <= DATEADD("dd", -60, GETDATE()) then Amount Else 0 END) AS ChargedOver60,
SUM(CASE WHEN Amount < 0 AND TransactionDate <= DATEADD("dd", -90, GETDATE()) THEN Amount ELSE 0 END) AS ChargedOver90 
FROM tblTransaction
GROUP BY TransactionCode, TenantID

answered Jun 01 '09 at 14:50

Mitch Wheat

295,962
43
465
541

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ')'. – Malfist Jun 01 '09 at 14:52
you need 'END' at the end of the CASE statements, this is T-SQL – Malfist Jun 01 '09 at 14:54
1

The "GROUP BY TransactionCode, TenantID" will give a row for each TransactionCode, TenantID; I'm not sure what you mean? – Mitch Wheat Jun 01 '09 at 15:02

score 1 · Answer 2 · answered Jun 01 '09 at 14:49

using UNION to stitch the result sets together would work. You may want to translate it using the PIVOT capabilities to get the amounts into separate columns. Sorry I can't be more specific, I don't have my notes and I don't know the exact syntax for this stuff off the top of my head.

score 1 · Answer 3 · edited Jun 01 '09 at 15:16

If you can make the projection or shape of the query the same across all 5 individual queries, you can use a union to not only combine the queries into a single result, but also order the results. I'vee modified last column to be consistent and represent a state the charge is in for your to filter from the results:

SELECT TransactionCode, TenantID, SUM(Amount) [Amount], 'Paid' [Status]
FROM tblTransactionWHERE Amount > 0
GROUP BY TransactionCode, TenantID

union

SELECT TransactionCode, TenantID, SUM(Amount) [Amount], 'Charged Current' [Status]
FROM tblTransactionWHERE Amount < 0 AND TransactionDate > DATEADD("dd", -30, GETDATE())
GROUP BY TransactionCode, TenantID

union

SELECT TransactionCode, TenantID, SUM(Amount) [Amount], 'ChargedOver30' [Status]
FROM tblTransactionWHERE Amount < 0 AND TransactionDate > DATEADD("dd", -60, GETDATE()) AND TransactionDate <= DATEADD("dd", -30, GETDATE())
GROUP BY TransactionCode, TenantID

union

SELECT TransactionCode, TenantID, SUM(Amount) [Amount], 'ChargedOver60' [Status]
FROM tblTransactionWHERE Amount < 0 AND TransactionDate > DATEADD("dd", -90, GETDATE()) AND TransactionDate <= DATEADD("dd", -60, GETDATE())
GROUP BY TransactionCode, TenantID

union

SELECT TransactionCode, TenantID, SUM(Amount) [Amount], 'ChargedOver90' [Status]
FROM tblTransactionWHERE Amount < 0 AND TransactionDate <= DATEADD("dd", -90, GETDATE())
GROUP BY TransactionCode, TenantID

order by 4 --Status

Is there anyway to get them all in one row, instead of spread out among 5 rows? — Malfist, Jun 01 '09 at 14:58

score 1 · Answer 4 · answered Jun 01 '09 at 15:01

This cross-tab query should work:

SELECT 
Case WHEN Amount > 0 Then Amount Else 0 End as [Total],
Case WHEN Amount < 0 AND TransactionDate > DATEADD("dd", -30, GETDATE())
    Then Amount Else 0 End as [Charge 0-29 Days],
Case WHEN Amount < 0 AND TransactionDate > DATEADD("dd", -60, GETDATE()) AND TransactionDate <= DATEADD("dd", -30, GETDATE()) 
    Then Amount Else 0 End as [Charge 30-59 Days],
Case WHEN Amount < 0 AND TransactionDate > DATEADD("dd", -90, GETDATE()) AND TransactionDate <= DATEADD("dd", -60, GETDATE()) 
    Then Amount Else 0 End as [Charge 60-89 Days],
Case WHEN Amount < 0 AND TransactionDate <= DATEADD("dd", -90, GETDATE())
     Then Amount Else 0 End as [Charge 90+ Days],

FROM tblTransaction GROUP BY TransactionCode, TenantID

Well rats, mith beat me to it. Why didn't I get any update notices? — RBarryYoung, Jun 01 '09 at 15:03

score 0 · Answer 5 · answered Jun 01 '09 at 14:52

0

Add an extra column to each query

SELECT TransactionCode, TenantID, SUM(Amount) AS ChargedCurrent, 30 as [DaysLate] FROM tblTransaction...

and then UNION ALL the queries together

answered Jun 01 '09 at 14:52

SillyMonkey

1,334
3
11
14

How can I combine these queries?

5 Answers5