Adding and Recursive Loop - 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 2005 Forums

Transact-SQL (2005)

Adding and Recursive Loop

Author

Topic

uja100
Starting Member

6 Posts

Posted - 2011-12-02 : 09:28:33

I have the following table:

|Rules| |Reference Date| |Length| |CurrStart|
|Rule1| |04-12-1993| |14| |11-28-2011|
|Rule2| |01-02-2005| | 7| |12-01-2011|
|Rule3| |04-01-1996| | 7| |11-28-2011|

What I'm looking to do is:
For each rule, output (Reference Date + Length), until Reference Date = CurrStart

For example:
Rule1
04-12-1993
04-26-1993
...
10-16-2011
10-30-2011
11-14-2011
11-28-2011

Thanks in advance...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 09:35:35

[code]
DECLARE @rule varchar(100)

SET @rule='Rule1'
;With CTE([Reference Date],Length,CurrStart)
AS
(
SELECT [Reference Date],Length,CurrStart
FROM Table
WHERE Rules = @rule
UNION ALL
SELECT DATEADD(dd,Length,[Reference Date]),Length,CurrStart
FROM CTE
WHERE DATEADD(dd,Length,[Reference Date])<= CurrStart
)

SELECT [Reference Date]
FROM CTE
OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 09:38:53

maybe something like
oops - this is getting very similar to visakh16's now.

;with cte as
(
select Rules, dte = ReferenceDate, length, enddte = CurrStart from tbl
union all
select Rules, dte =dateadd(dd,length,dte), enddte = enddte from cte where dte<enddte
)
select Rules, dte from cte
order by Rules, dte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

uja100
Starting Member

6 Posts

Posted - 2011-12-02 : 11:11:32

quote:
Originally posted by visakh16

DECLARE @rule varchar(100)

SET @rule='Rule1'
;With CTE([Reference Date],Length,CurrStart)
AS
(
SELECT [Reference Date],Length,CurrStart
FROM Table
WHERE Rules = @rule
UNION ALL
SELECT DATEADD(dd,Length,[Reference Date]),Length,CurrStart
FROM CTE
WHERE DATEADD(dd,Length,[Reference Date])<= CurrStart
)

SELECT [Reference Date]
FROM CTE
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

uja100
Starting Member

6 Posts

Posted - 2011-12-02 : 11:16:35

There was a problem with my previous reply.
What I wanted to say is:

Thank you visakh16, that seemed to work.
I have two follwup questions:

1. If i wanted just the last 20 results then where would I put the where clause and how would it look like.

2. If I wanted to populate another table with the results then how could I do that. I was thinking

Create Table D1 (Ref_Date)
Insert into D1 (Ref_Date)
<Put the CTE here>

Thanks in advance...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-02 : 11:21:47

with cte
(
)
insert tbl
SELECT top 20 [Reference Date]
FROM CTE
OPTION (MAXRECURSION 0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 12:11:50

quote:
Originally posted by uja100

There was a problem with my previous reply.
What I wanted to say is:

Thank you visakh16, that seemed to work.
I have two follwup questions:

1. If i wanted just the last 20 results then where would I put the where clause and how would it look like.

2. If I wanted to populate another table with the results then how could I do that. I was thinking

Create Table D1 (Ref_Date)
Insert into D1 (Ref_Date)
<Put the CTE here>

Thanks in advance...

it should be


Create Table D1 (Ref_Date)
GO
DECLARE @rule varchar(100)


SET @rule='Rule1'
;With CTE([Reference Date],Length,CurrStart)
AS
(
SELECT [Reference Date],Length,CurrStart
FROM Table
WHERE Rules = @rule
UNION ALL
SELECT DATEADD(dd,Length,[Reference Date]),Length,CurrStart
FROM CTE
WHERE DATEADD(dd,Length,[Reference Date])<= CurrStart
)
Insert into D1 (Ref_Date)
SELECT TOP 20 [Reference Date]
FROM CTE
ORDER BY [Reference Date] DESC
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

uja100
Starting Member

6 Posts

Posted - 2011-12-02 : 12:42:39

This is weird because I get the following output:

Ref_Date
-----------------------
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-28 00:00:00.000
2011-11-14 00:00:00.000
2011-11-14 00:00:00.000
2011-11-14 00:00:00.000
2011-11-14 00:00:00.000
2011-11-14 00:00:00.000
2011-11-14 00:00:00.000
2011-11-14 00:00:00.000

I cannot figure out why its doing this.

**********************

Create Table D4 (Ref_Date DateTime)
GO
DECLARE @rule varchar(100)
SET @rule='Bi-weekly In Day'
;With CTE([Ref_Date],PayPeriodLength,CurrPayPeriodStart)
AS
(
SELECT [Ref_Date],PayPeriodLength,CurrPayPeriodStart
FROM D1
WHERE FixedRule = @rule
UNION ALL
SELECT DATEADD(dd,PayPeriodLength,[Ref_Date]),PayPeriodLength,CurrPayPeriodStart
FROM CTE
WHERE DATEADD(dd,PayPeriodLength,[Ref_Date])<= CurrPayPeriodStart
)

Insert into D4 (Ref_Date)
SELECT TOP 20 [Ref_Date]
FROM CTE
ORDER BY [Ref_Date] DESC
OPTION (MAXRECURSION 0)

Select * From D4
Drop Table D4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 12:53:03

hmm..how did output change now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

uja100
Starting Member

6 Posts

Posted - 2011-12-02 : 15:34:02

I found a work around.

I adjusted: SELECT TOP 20 [Ref_Date]
To: SELECT Distinct TOP 20 [Ref_Date]

Now I'm getting the results.

Btw,
When I ran the original query it did give me duplicate entries for the first date, 04-12-1993, but then it processed the subsequent dates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 00:25:23

quote:
Originally posted by uja100

I found a work around.

I adjusted: SELECT TOP 20 [Ref_Date]
To: SELECT Distinct TOP 20 [Ref_Date]

Now I'm getting the results.

Btw,
When I ran the original query it did give me duplicate entries for the first date, 04-12-1993, but then it processed the subsequent dates.

Oh ok...so you had duplicates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

uja100
Starting Member

6 Posts

Posted - 2011-12-08 : 17:18:30

I think I know why I have the duplicates.
If it's what I think it is doesn't relate to the SQL Statement.

Thank you...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 00:22:43

ok...np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources