February 24, 2015 at 3:14 pm
hi,
I have a query to run with last day of the month as the criteria for rolling 6 previous months. Basically the same query for 6 times.
I used CTE but don't know how I can use the date from CTE into the query criteria?
Any idea.
Thanks
Jagan
February 24, 2015 at 3:30 pm
Do you have any DDL? Sample data? Example of the desired output?
-- Itzik Ben-Gan 2001
February 24, 2015 at 4:11 pm
That doesn't sounds like a need of recursive queries. It seems to me that a tally table could do the job or just a date range.
We need more details including DDL, sample data and expected results.
February 24, 2015 at 4:56 pm
I used a while loop and got all the dates with 6 grids. How can I append all these results in the while loop one after another or in one single output file without overwriting? Any help?
February 24, 2015 at 6:34 pm
not sure if this can help (the query is pretty big)
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 where enddate < @d1
)
set @i = @i + 1;
set @d1 = eomonth(@d1, -1)
end
February 24, 2015 at 7:46 pm
So you include the whole history each month? Or why do you have only "enddate < @d1"?
This is an example of filtering using 6 months.
SELECT *
FROM SomeTable st
JOIN (VALUES(0),(-1),(-2),(-3),(-4),(-5)) Tally(n) ON st.enddate = DATEADD(MONTH, Tally.n, @d1)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply