Solved

SQL Query - Loop Result with Calculation

Posted on 2013-11-26
Medium Priority
269 Views
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.

``````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
``````
0
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 41

Expert Comment

ID: 39679869
try this query.
``````DECLARE @ACCOUNT VARCHAR(10)
DECLARE @YEAR VARCHAR(4)
DECLARE @PERIOD VARCHAR

SET @ACCOUNT = '90299'
SET @YEAR = '2013'
SET @PERIOD = '3'

;WITH CTE1
AS (SELECT *,
(SELECT SUM(Balance)
FROM Test t2
WHERE t1.Account = t2.Account
AND t1.Year1 = t2.Year1
AND t2.Period <= t1.Period) YTDBalance
FROM Test t1),
CTE2
AS (SELECT *,
(SELECT SUM(YTDBalance)
FROM CTE1 t2
WHERE t1.Account = t2.Account
AND t1.Year1 = t2.Year1
AND t2.Period <= t1.Period) Budget
FROM CTE1 t1),
CTE3
AS (SELECT *,
(SELECT SUM(Budget)
FROM CTE2 t2
WHERE t1.Account = t2.Account
AND t1.Year1 = t2.Year1
AND t2.Period <= t1.Period) YTDBudget
FROM CTE2 t1)
SELECT *
FROM CTE3
WHERE Account = @ACCOUNT
AND Year1 = @YEAR
AND Period = @PERIOD
``````
You may have to change the columns/table names.

http://sqlfiddle.com/#!3/81228/1
0

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 39679886
Moved the filter on Account and Year to the first CTE for better performance.
``````;WITH CTE1
AS (SELECT *,
(SELECT SUM(Balance)
FROM Test t2
WHERE t1.Account = t2.Account
AND t1.Year1 = t2.Year1
AND t2.Period <= t1.Period) YTDBalance
FROM Test t1
WHERE Account = @ACCOUNT
AND Year1 = @YEAR),
CTE2
AS (SELECT *,
(SELECT SUM(YTDBalance)
FROM CTE1 t2
WHERE t1.Account = t2.Account
AND t1.Year1 = t2.Year1
AND t2.Period <= t1.Period) Budget
FROM CTE1 t1),
CTE3
AS (SELECT *,
(SELECT SUM(Budget)
FROM CTE2 t2
WHERE t1.Account = t2.Account
AND t1.Year1 = t2.Year1
AND t2.Period <= t1.Period) YTDBudget
FROM CTE2 t1)
SELECT *
FROM CTE3
WHERE Period = @PERIOD
``````

http://sqlfiddle.com/#!3/81228/2
0

Author Closing Comment

ID: 39721578
Thank you.  That worked well.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll