Recursive column? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Recursive column?

Author

Topic

Ryerocks
Starting Member

16 Posts

Posted - 2012-02-28 : 09:00:28

Is this doable?

I'll give a simplified example of my problem.

Table: Sales
Fields:
SalesID
ContractID
ContractYear
Sales
Expenses

I need to build a view that has a calculated field called "Surplus":
Surplus = Sales - Expense + the surplus from the previous year.

And the surplus from the previous year needs to get the sales and expenses and the surplus from the year before that. If there is no previous year (eventually), the surplus from previous year should equal zero.

All of these years have to share the same contractID.

Ryerocks
Starting Member

16 Posts

Posted - 2012-02-28 : 09:12:42

I've started this view by setting the surplus to zero when there is no previous year, its the non-zero year part that is the problem since it has to call itself.

SELECT SalesID, ContractID, ContractYear, Sales, Expenses,
CASE WHEN (SELECT SalesID FROM Sales AS b WHERE b.ContractYear = a.ContractYear -1 AND b.ContractID=a.ContractID) IS NULL THEN 0 END AS Surplus
FROM Sales AS a

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-28 : 11:05:51

What you need is a correlated subquery.

This should be enough to get you started:


CREATE TABLE dbo.Sales
(
	SalesID smallint NOT NULL identity(1,1) primary key,
	ContractYear smallint NOT NULL,
	SalesAmt money NOT NULL,
	ExpenseAmt money NOT NULL
)

INSERT INTO dbo.Sales
(ContractYear, SalesAmt, ExpenseAmt)
VALUES
(2000, 100, 90),
(2001, 100, 90),
(2002, 100, 90),
(2003, 100, 90),
(2004, 100, 90),
(2005, 100, 90),
(2006, 100, 90),
(2007, 100, 90)


SELECT 
	ContractYear, 
	SalesAmt, 
	ExpenseAmt,
	SalesAmt - ExpenseAmt as YearSurplus,
	(SELECT SUM(SalesAmt - ExpenseAmt)
	FROM
		dbo.Sales
	WHERE
		ContractYear <= s.ContractYear) as RunningSurplus
FROM 
	dbo.Sales s

Results:

ContractYear SalesAmt              ExpenseAmt            YearSurplus           RunningSurplus
------------ --------------------- --------------------- --------------------- ---------------------
2000         100.00                90.00                 10.00                 10.00
2001         100.00                90.00                 10.00                 20.00
2002         100.00                90.00                 10.00                 30.00
2003         100.00                90.00                 10.00                 40.00
2004         100.00                90.00                 10.00                 50.00
2005         100.00                90.00                 10.00                 60.00
2006         100.00                90.00                 10.00                 70.00
2007         100.00                90.00                 10.00                 80.00

(8 row(s) affected)

Keep in mind that this is inefficient and will suffer fro performance issues on large data sets, but give it a try, it should be fine.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

Ryerocks
Starting Member

16 Posts

Posted - 2012-02-28 : 14:01:09

Thats an excellent idea. Works well - but a bit slow - as you anticipated. About 5-10 seconds currently, on a dataset that will grow. However, I will only be pulling one specific row from the query, so hopefully that will respond quicker.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-28 : 16:20:30

do you have appropriate indexes?

Do a SHOWPLAN and tell us what you get

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Ryerocks
Starting Member

16 Posts

Posted - 2012-02-29 : 08:09:23

Where do I select "ShowPlan" from? Took a quick look and didn't see it.

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-29 : 10:50:09

quote:
Originally posted by Ryerocks

Where do I select "ShowPlan" from? Took a quick look and didn't see it.

Normally you would just go to Query > Include Actual Execution Plan in order to show an execution plan for yourself to analyze, but in order to post one here you should use the SHOWPLAN comman as following using the example I posted above:

SET SHOWPLAN_ALL ON;
GO

SELECT 
	ContractYear, 
	SalesAmt, 
	ExpenseAmt,
	SalesAmt - ExpenseAmt as YearSurplus,
	(SELECT SUM(SalesAmt - ExpenseAmt)
	FROM
		dbo.Sales
	WHERE
		ContractYear <= s.ContractYear) as RunningSurplus
FROM 
	dbo.Sales s;
GO
	
SET SHOWPLAN_ALL OFF;
GO

The results I get from this query are:


StmtText                                                                                                                                                                                                                                     StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                             DefinedValues                                                                                                       EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                    Warnings Type                                                             Parallel EstimateExecutions
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------

SELECT 
	ContractYear, 
	SalesAmt, 
	ExpenseAmt,
	SalesAmt - ExpenseAmt as YearSurplus,
	(SELECT SUM(SalesAmt - ExpenseAmt)
	FROM
		dbo.Sales
	WHERE
		ContractYear <= s.ContractYear) as RunningSurplus
FROM 
	dbo.Sales s;    1           1           0           NULL                           NULL                           1                                                                                                                                                                    NULL                                                                                                                8             NULL          NULL          NULL        0.00728038       NULL                                                                          NULL     SELECT                                                           0        NULL
  |--Compute Scalar(DEFINE:([Expr1008]=[Expr1006]))                                                                                                                                                                                          1           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1008]=[Expr1006])                                                                                                                                       [Expr1008]=[Expr1006]                                                                                               8             0             8E-07         41          0.00728038       [s].[ContractYear], [s].[SalesAmt], [s].[ExpenseAmt], [Expr1002], [Expr1008]  NULL     PLAN_ROW                                                         0        1
       |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[ContractYear]))                                                                                                                                                                    1           3           2           Nested Loops                   Inner Join                     OUTER REFERENCES:([s].[ContractYear])                                                                                                                                NULL                                                                                                                8             0             3.344E-05     41          0.00727958       [s].[ContractYear], [s].[SalesAmt], [s].[ExpenseAmt], [Expr1002], [Expr1006]  NULL     PLAN_ROW                                                         0        1
            |--Compute Scalar(DEFINE:([Expr1002]=[TEST].[dbo].[Sales].[SalesAmt] as [s].[SalesAmt]-[TEST].[dbo].[Sales].[ExpenseAmt] as [s].[ExpenseAmt]))                                                                                   1           4           3           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1002]=[TEST].[dbo].[Sales].[SalesAmt] as [s].[SalesAmt]-[TEST].[dbo].[Sales].[ExpenseAmt] as [s].[ExpenseAmt])                                          [Expr1002]=[TEST].[dbo].[Sales].[SalesAmt] as [s].[SalesAmt]-[TEST].[dbo].[Sales].[ExpenseAmt] as [s].[ExpenseAmt]  8             0             8E-07         33          0.0032916        [s].[ContractYear], [s].[SalesAmt], [s].[ExpenseAmt], [Expr1002]              NULL     PLAN_ROW                                                         0        1
            |    |--Clustered Index Scan(OBJECT:([TEST].[dbo].[Sales].[PK__Sales__C952FB120169315C] AS [s]))                                                                                                                                 1           5           4           Clustered Index Scan           Clustered Index Scan           OBJECT:([TEST].[dbo].[Sales].[PK__Sales__C952FB120169315C] AS [s])                                                                                                   [s].[ContractYear], [s].[SalesAmt], [s].[ExpenseAmt]                                                                8             0.003125      0.0001658     25          0.0032908        [s].[ContractYear], [s].[SalesAmt], [s].[ExpenseAmt]                          NULL     PLAN_ROW                                                         0        1
            |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END))                                                                                                                                    1           9           3           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1006]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END)                                                                                           [Expr1006]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END                                                   1             0             1.94E-06      15          0.00395454       [Expr1006]                                                                    NULL     PLAN_ROW                                                         0        8
                 |--Stream Aggregate(DEFINE:([Expr1015]=COUNT_BIG([Expr1009]), [Expr1016]=SUM([Expr1009])))                                                                                                                                  1           10          9           Stream Aggregate               Aggregate                      NULL                                                                                                                                                                 [Expr1015]=COUNT_BIG([Expr1009]), [Expr1016]=SUM([Expr1009])                                                        1             0             1.94E-06      15          0.00395454       [Expr1015], [Expr1016]                                                        NULL     PLAN_ROW                                                         0        8
                      |--Compute Scalar(DEFINE:([Expr1009]=[TEST].[dbo].[Sales].[SalesAmt]-[TEST].[dbo].[Sales].[ExpenseAmt]))                                                                                                               1           11          10          Compute Scalar                 Compute Scalar                 DEFINE:([Expr1009]=[TEST].[dbo].[Sales].[SalesAmt]-[TEST].[dbo].[Sales].[ExpenseAmt])                                                                                [Expr1009]=[TEST].[dbo].[Sales].[SalesAmt]-[TEST].[dbo].[Sales].[ExpenseAmt]                                        2.4           0             8E-07         17          0.0039083        [Expr1009]                                                                    NULL     PLAN_ROW                                                         0        8
                           |--Clustered Index Scan(OBJECT:([TEST].[dbo].[Sales].[PK__Sales__C952FB120169315C]), WHERE:([TEST].[dbo].[Sales].[ContractYear]<=[TEST].[dbo].[Sales].[ContractYear] as [s].[ContractYear]))                      1           12          11          Clustered Index Scan           Clustered Index Scan           OBJECT:([TEST].[dbo].[Sales].[PK__Sales__C952FB120169315C]), WHERE:([TEST].[dbo].[Sales].[ContractYear]<=[TEST].[dbo].[Sales].[ContractYear] as [s].[ContractYear])  [TEST].[dbo].[Sales].[SalesAmt], [TEST].[dbo].[Sales].[ExpenseAmt]                                                  2.4           0.0032035     8.73E-05      25          0.0039019        [TEST].[dbo].[Sales].[SalesAmt], [TEST].[dbo].[Sales].[ExpenseAmt]            NULL     PLAN_ROW                                                         0        8

You should post the results from the actual query you are using in your environment.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

Ryerocks
Starting Member

16 Posts

Posted - 2012-02-29 : 11:06:23

heh, my result is 249 rows for the showplan. Do I post all 249 rows, or is that way too much?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 11:18:30

no, I don't think it's too much

But, do you know how to generate the DDL for your table, including all the constraints and indexes?

Post that

And did you mention how many rows are in that table?

And Change this

SELECT SUM(SalesAmt - ExpenseAmt)

To something like this

SELECT SUM(SalesAmt - ExpenseAmt) AS SUM_Sales_Less_Expense_Amt

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 11:37:43

Can you post the actual Query or View?

And do you have an Index on Contract Year?

Do your tables have primary keys or some other unique constraint?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-29 : 11:51:34

I see some table scans in there, but from this I can't tell how big of an impact they are having, however the fact that its a table scan instead of an index scan implies that there is no primary key on the table.

Yeah, posting the actual query would be good.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

Ryerocks
Starting Member

16 Posts

Posted - 2012-02-29 : 12:35:02

Its a series of cascading queries. There should be primary keys on all the tables.

I'd have to post 20 different views in here to show the entire structure of joins and views within views.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 12:39:48

What are "Cascading" Queries?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Ryerocks
Starting Member

16 Posts

Posted - 2012-02-29 : 12:48:42

For example, view1, which is a series of joins from other tables and views.
Then View2, which is based on View1 with a number of calculated fields
Then View3, which is based on View2
And finally View4, which is based on View3.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 13:15:13

Is this Access?

Nesting Views would be a BAD thing IMHO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-01 : 08:00:28

Nope. SQL Server.

Anyways, everything works really well - so thanks a bunch for the help guys. Much appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-01 : 08:16:18

Tried this?

DECLARE	@Sales TABLE
	(
		SalesID smallint NOT NULL identity(1,1) primary key,
		ContractYear smallint NOT NULL,
		SalesAmt money NOT NULL,
		ExpenseAmt money NOT NULL
	)

INSERT	@Sales
VALUES	(2000, 100, 90),
	(2001, 100, 90),
	(2002, 100, 90),
	(2003, 100, 90),
	(2004, 100, 90),
	(2005, 100, 90),
	(2006, 100, 90),
	(2007, 100, 90)

SELECT		w.ContractYear,
		w.SalesAmt,
		w.ExpenseAmt,
		q.Surplus
FROM		@Sales AS w
INNER JOIN	(
			SELECT		v.number AS ContractYear,
					SUM(s.SalesAmt - s.ExpenseAmt) AS Surplus
			FROM		@Sales AS s
			INNER JOIN	master.dbo.spt_values AS v ON v.type = 'P'
			WHERE		v.number BETWEEN s.ContractYear AND 2007
			GROUP BY	v.number
		) AS q ON q.ContractYear = w.ContractYear

N 56°04'39.26"
E 12°55'05.63"

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-14 : 10:15:43

Hey guys. My clients have requested a small wrinkle to the surplus concept. Any given year could have a "surplusOverride". So the query would need to consider this other column if it has data.

If Surplus = Sales - Expense + the surplus from the previous year, the surplus from a previous year could be a client entered "surplusOverride" field instead. So, lets say there are 10 years of data, and around year 5, an override was entered. The surplus that is calculated on prior years surplus, and so on, would need to only go as far back as year 5, use the override value, and then calculate the rest as normal.

This probably is simpler than I'm making it in my head, but if anyone has a good solution to this, that would be extremely helpful. Thanks!

Ryerocks
Starting Member

16 Posts

Posted - 2012-03-14 : 10:16:25

btw, I deleted that huge showplan as it was cluttering up this thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources