July 25, 2006 at 2:09 pm
Hi,
I don't know if my brain is going but I can't seem to wrap logic around this one:
I'm creating a financial report that will look something like this:
| AP Period 1 | ||||
| GL Account | Current AP Period | Budgeted | Variance | Cumulative |
| 1 | $ 25 | $ 35 | $ (10) | $ 25 |
| AP Period 2 | ||||
| GL Account | Current AP Period | Budgeted | Variance | Cumulative |
| 1 | $ 35 | $ 25 | $ 10 | $ 60 |
The user will select a given AP Period and depending on that period the cumulative amount will be calculated. Using straight SQL how would I have a running sum based on the previous value.
My raw data looks something like this:
| glacct | ap01 | ap02 | ap03 | ap04 | ap05 | ap06 |
| 70704 | -45.78 | -85.11 | 10 | 15 | 25 | 6 |
I'm not looking for a SQL answer but rather just ideas on how to tackle this issue.
Thanks in advanced,
July 25, 2006 at 2:36 pm
DECLARE @t TABLE(pos int, val int)
INSERT INTO @t
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40
SELECT A.pos ,SUM(B.val) as Total
FROM @t A INNER JOIN @t B
ON A.pos>=B.pos
GROUP BY A.pos
post DDL, sample data , desired result
Vasc
July 26, 2006 at 5:59 am
The above code will work if you're in the situation you have outline. However my data situation is that I have twelve columns of data not 12 rows.
This is what I have so far:
If @AP = '01'
BEGIN
SELECT glname,glacct,ap01,SUM(ap01) AS APCUM
FROM finance_departmental_budget_datasource_final
WHERE gldept = @Dept AND Multiyear = @MultiYear
GROUP BY glname,glacct,ap01
END
Through AP12
If @AP = '12'
BEGIN
SELECT glname,glacct,ap01,SUM(ap01 + ap02) AS APCUM
FROM finance_departmental_budget_datasource_final
WHERE gldept = @Dept AND Multiyear = @MultiYear
GROUP BY glname,glacct,ap01
END
RETURN
I'm trying to avoid adding 12 separate if conditions but I somehow have to add the ap columns in order to provide the right cumulative calculation based on what the user selects. How can I refactor the above code is my real question?
Thanks,
July 26, 2006 at 8:27 am
SELECT glname,glacct,ap01,SUM(
CASE WHEN @AP>=1 THEN 1 ELSE 0 END * AP01+
CASE WHEN @AP>=2 THEN 1 ELSE 0 END * AP02+
CASE WHEN @AP>=3 THEN 1 ELSE 0 END * AP03+
CASE WHEN @AP>=4 THEN 1 ELSE 0 END * AP04+
CASE WHEN @AP>=5 THEN 1 ELSE 0 END * AP05+
CASE WHEN @AP>=6 THEN 1 ELSE 0 END * AP06+....
) AS APCUM
FROM finance_departmental_budget_datasource_final
WHERE gldept = @Dept AND Multiyear = @MultiYear
GROUP BY glname,glacct,ap01
Vasc
July 27, 2006 at 5:15 am
Thanks for your input
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply