Running Total

[imfarhan]

Hi I'm trying to calculate the RT(Running total) based on the Key and Period fields
Using the following SQL but doesn't generate the right result, please can you assist me with that
The 3rd column RT is the output on excel but idea so to generate similar result using SQL

SELECT 
  Budget_2025.[Fiscal Year]&Budget_2025.[Cost Centre Code]&Budget_2025.[Subjective Code]&Budget_2025.[Analysis One Code]&Budget_2025.[Analysis Two Code] As Key,
  Budget_2025.[Fiscal Period Number],
  Budget_2025.IM_WTEPlan AS IM_WTEPlan,
    (
        SELECT SUM(x2.IM_WTEPlan)
        FROM Budget_2025 as x2
        WHERE   x2.[Fiscal Year]&x2.[Cost Centre Code]&x2.[Subjective Code]&x2.[Analysis One Code]&x2.[Analysis Two Code]  
              =  Budget_2025.[Fiscal Year]&Budget_2025.[Cost Centre Code]&Budget_2025.[Subjective Code]&Budget_2025.[Analysis One Code]&Budget_2025.[Analysis Two Code]
         AND     x2.[IM_WTEPlan] <= Budget_2025.IM_WTEPlan
    ) AS rt

FROM Budget_2025

WHERE (((Budget_2025.Currency)="STAT")) 
AND Budget_2025.[Cost Centre Code] = "495150"
ORDER BY  Budget_2025.[Cost Centre Code], Budget_2025.[Subjective Code], Budget_2025.[Fiscal Period Number];




[ATTACH type="full"]113279[/ATTACH]

Thanks

 

[imfarhan]

Re-write the SQL to generate the correct result but the monthly order not showing correctly
Can't i share the table or Excel file so can test on your end.?
or if you type first 3 lines should be able to check my SQL
Thanks
Farhan

SELECT
x1.Key,
x1.Month,
x1.Value,
(
  SELECT SUM(x2.Value)  as RT
  FROM RT_Example as x2
  WHERE x2.Key  = x1.Key
  AND    x2.Value <= x1.Value
) as RT
FROM RT_Example AS x1
ORDER BY x1.Key, x1.Month

 

[Joe4]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

However, in your SQL script, I think there may be an issue here:

  AND    x2.Value <= x1.Value

Shouldn't that be your date field?, i.e.

  AND    x2.Month <= x1.Month

 

[Micron]

If you can use a report for the results, it is far easier to get a running sum in a report - no code required.