Solved

SQL Query - Loop Result with Calculation

Posted on 2013-11-26
3
268 Views
Last Modified: 2013-12-16
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.

Result I'm after.
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

Open in new window

0
Comment
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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 41

Expert Comment

by:Sharath
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 

Open in new window

You may have to change the columns/table names.

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

Accepted Solution

by:
Sharath earned 500 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 

Open in new window


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

Author Closing Comment

by:holemania
ID: 39721578
Thank you.  That worked well.
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

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…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question