November 16, 2010 at 3:22 pm
Hi,
Basically what I'm looking to do is sum data from a Cash Flow table and group by the Past and Future values. Problem though is I can't find a simple way to get it working when values only exist in Past or Future since I need both Past and Future rows to exist in all cases, just with zero values in the one where no data exists.
Below is some SQL that may help demo what I'm needing:
create table tmpCashFlow (AccountID Numeric(5,0), CFDate Datetime, Income Numeric(18,2))
Insert into tmpCashFlow (AccountID,CFDate,Income) values
(123,'2010-01-01',123.43),
(123,'2010-02-01',123.43),
(123,'2010-03-01',432.23),
(123,'2010-04-01',123.43),
(123,'2010-05-01',44.32),
(123,'2010-06-01',123.52),
(123,'2010-07-01',123.65),
(123,'2010-08-01',234.43),
(123,'2010-09-01',143.42),
(123,'2010-10-01',22.44),
(123,'2010-11-01',432.32),
(123,'2010-12-01',123.43),
(125,'2010-01-01',123.43),
(125,'2010-02-01',123.43),
(125,'2010-03-01',432.23),
(125,'2010-04-01',123.43),
(125,'2010-05-01',44.32)
Go
CREATE VIEW tmpCFRecap As
select case when CFDate < getdate() then 'To Date' Else 'Remaining' end as Note,
AccountID,
sum(Income) as SumIncome
from tmpCashFlow
group by case when CFDate < getdate() then 'To Date' Else 'Remaining' end,
AccountID
Go
select * from tmpCFRecap where AccountID = 123
select * from tmpCFRecap where AccountID = 125
Go
drop table tmpCashFlow
drop view tmpCFRecap
For AccountID 123 it shows both 'Remaining' and 'To Date', but for AccountID it only shows 'To Date' though I need it to also show both but with zero values if no data is present.
I've tried every combination of joins I can come-up with to no avail along with a few other options. I'd rather not do unions since I know there's some way to do it in one select since I'm sure I've done this before. Any suggestions?
Thanks --
Sam
November 16, 2010 at 3:55 pm
I'm not sure if it's th fastes possible solution, but here's how I'd do it:
Step 1: get the list of distinct AccountID's
Step 2: Build a "master list" by cross referencing the each AccountID to the values 'Remaining' and 'To Date' and finally
Step 3: using this "master list" as a right join to the actual data
; WITH acc_ids AS -- get a list of all AccountID's
(
SELECT DISTINCT AccountID
FROM tmpCFRecap
), notes AS -- build an auxiliary "table" holding all values required for the output
(
SELECT 'Remaining' AS note UNION ALL
SELECT 'To Date'
), base AS -- get the "base list" by assign each value of notes to each AccountID
(
SELECT *
FROM acc_ids
CROSS JOIN notes
)
-- final query using a left join from the "base list" to the actual values and assign 0.00 if no actual values are found
SELECT
base.AccountID,
base.note,
ISNULL(tmpCFRecap.sumincome,0) AS sumincome
FROM base
LEFT OUTER JOIN tmpCFRecap
ON base.AccountID=tmpCFRecap.AccountID
AND base.note=tmpCFRecap.note
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply