holemania
asked on
SQL Query - Loop Result with Calculation
I have a table that I need to do some looping to do some calculations and not sure if this is even possible with sql query script.
The embedded image have my data and only data provided from the table is the "Balance" column. For the 1st period, the balance will be the same for YTD Balance, Budget, and YTD Budget. Every month for the same account and year, the highlighted is what gets added to get the YTD Balance, Budget, and YTD Budget.
My user would like to pull it for any period. So my query parameter will have account number, year, and period. When he select account number 90299, year 2013, and period 3, the balance is pulled, but then I need to loop for period 2 and 1 as well to get the YTD, Budget, and YTD Budget.
Yellow items added to get red result.
Red items added to get green result.
Green items added to get blue result.
Any ideas how to do this? I tried with a nested query, but can only do it for the previous period and not all the way to the first period.
The embedded image have my data and only data provided from the table is the "Balance" column. For the 1st period, the balance will be the same for YTD Balance, Budget, and YTD Budget. Every month for the same account and year, the highlighted is what gets added to get the YTD Balance, Budget, and YTD Budget.
My user would like to pull it for any period. So my query parameter will have account number, year, and period. When he select account number 90299, year 2013, and period 3, the balance is pulled, but then I need to loop for period 2 and 1 as well to get the YTD, Budget, and YTD Budget.
Yellow items added to get red result.
Red items added to get green result.
Green items added to get blue result.
Any ideas how to do this? I tried with a nested query, but can only do it for the previous period and not all the way to the first period.
DECLARE @ACCOUNT VARCHAR(10)
DECLARE @YEAR VARCHAR(4)
DECLARE @PERIOD VARCHAR
SET @ACCOUNT = '90229'
SET @YEAR = '2013'
SET @PERIOD = '3'
SELECT A.ACCOUNT, A.YEAR, A.PERIOD, A.BALANCE, (A.BALANCE + B.BALANCE) YTD_BALANCE
FROM BALANCE A
LEFT OUTER JOIN
(
SELECT A.ACCOUNT, A.YEAR, A.PERIOD, SUM(A.BALANCE) BALANCE
FROM BALANCE A
WHERE A.ACCOUNT = @ACCOUNT
AND A.YEAR = @YEAR
AND A.PERIOD = @PERIOD
) B ON B.ACCOUNT = A.ACCOUNT AND B.YEAR = A.YEAR
AND B.PERIOD = (A.PERIOD - 1)
WHERE A.ACCOUNT = @ACCOUNT AND A.YEAR = @YEAR
AND A.PERIOD = @PERIOD
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. That worked well.
Open in new window
You may have to change the columns/table names.http://sqlfiddle.com/#!3/81228/1