Recursive Query - 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 2012 Forums

Transact-SQL (2012)

Recursive Query

Author

Topic

deepujagan
Starting Member

4 Posts

Posted - 2015-02-24 : 17:07:01

Hi,
I have to run the same query with the date filter as last day of month for previous rolling 6 months. Any idea how I could run the same query for different last day of the month.

Thanks
Jagan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-24 : 17:21:36

You can use a WHILE loop. Example:

DECLARE @i tinyint
SET @i = 1
WHILE @i<=6
BEGIN
YourQuery, using @i with DATEADD in WHERE clause
SET @i = @i + 1
END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

deepujagan
Starting Member

4 Posts

Posted - 2015-02-24 : 17:26:48

Thanks for the reply. I used the CTE for the 6 previous rolling dates. How can I bring each date into the query using the while loop?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-24 : 17:36:10

For instance, this gets you January 31st:
select dateadd(dd,-datepart(dd,getdate()),getdate())

This gets you December 31st:
declare @d datetime
set @d = getdate()
select dateadd(dd,-datepart(dd,dateadd(mm,-1,@d)),dateadd(mm,-1,@d))

Notice the -1. Now use that formula for each, replace -1 with -i. But start i at 0 and go up to <=5 in the loop.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

deepujagan
Starting Member

4 Posts

Posted - 2015-02-24 : 18:44:36

Thank you. It worked but I got 6 result windows for each end of month date. Is there a way I can get it in one single file? Please let me know.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-24 : 19:05:54

Oh I thought that's what you wanted.

Show me the query you've been using with the date filter. You can replace object names with general names.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

deepujagan
Starting Member

4 Posts

Posted - 2015-02-24 : 20:01:18

Not sure if this can help, its pretty big query

Declare @d1 date, @i tinyint;
set @i = 1;
set @d1 = case when EOMONTH(GETDATE()) > Getdate() then eomonth(getdate(), -1) else eomonth(getdate()) end;

While @i < 7
Begin
(
my query goes here with where enddate < @d1
)
set @i = @i + 1;
set @d1 = eomonth(@d1, -1)
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-25 : 12:31:33

No, I need to see the "my query goes here" part. Is "where enddate < @d1" the only thing in the where clause? Are you trying to constraing it to be "where enddate > "6 months ago" and enddate < "end of the previous month"?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources