December 22, 2008 at 11:32 am
I have a stored procedure that is looping through a sequential tally table to grab date ranges
DECLARE CUR_DATES CURSOR FOR
SELECT IC.N,
IC.StartDate1,
IC.EndDate1
FROM ( SELECT N,
DATEADD(d, ((@Freq+1) * (N-1)),@StartDate) StartDate1,
CASE WHEN
DATEADD(d,((@Freq * N)+(N-1)),@StartDate) > @EndDate THEN @EndDate
ELSE DATEADD(d,((@Freq * N)+(N-1)),@StartDate)
END EndDate1
FROM dbo.Tally T
WHERE T.N <= DATEDIFF(d,@StartDate,@EndDate) / @Freq + 1) IC
order by n
What I am trying to accomplish is date ranges based on a frequency selected by the User. For example:
Startdate = 1/1/2008
Enddate = 1/29/2008
Frequency = 7 days
Results from above are
Jan 1 2008 12:00AMJan 8 2008 12:00AM
Jan 9 2008 12:00AMJan 16 2008 12:00AM
Jan 17 2008 12:00AMJan 24 2008 12:00AM
Jan 25 2008 12:00AMJan 29 2008 12:00AM
Feb 2 2008 12:00AMJan 29 2008 12:00AM
Since the end date is greater then the calculated start date on the fourth line it puts the end date in without and calculations. However the loop does not stop and goes on for one more line.
Can anyone take a look at this and see if they can pick up on what I am missing?
Thanks
December 22, 2008 at 11:39 am
within the body of the loop write something like this..
IF(condition to be checked)
begin
BREAK -- this will take u out of loop
end
December 22, 2008 at 12:09 pm
This thread is a continuation of : http://www.sqlservercentral.com/Forums/Topic616449-338-1.aspx%5B/url%5D
Sorry Akzsurtep, I missed your last reply.
So, you have your table of Start and End Dates. I don't understand why you've turned my tally table into a cursor :hehe:. A tally table *replaces* a cursor, it doesn't become the definition table for it.
What are you trying to do with these dates now that you have them that you feel requires a cursor?
December 22, 2008 at 12:28 pm
Thanks
I explained what I was trying to accomplish in detail because it seems to be tricky to get it to work exactly right.
If I have:
OPEN CUR_DATES
FETCH NEXT FROM CUR_DATES INTO @N, @StartDate1, @EndDate1
WHILE @@FETCH_STATUS = 0
BEGIN
IF @StartDate1 > @EndDate1
begin
BREAKend[/color]
It does break the loop, but one record too early. And on the next line the Start Date is NOT greater than the end date, so not sure why it is breaking there. And the enddate1 on the last line defaults to @EndDate, based on the statements within the beginning of the loop. My results are then:
StartDate EndDate
Jan 1 2008 12:00AMJan 8 2008 12:00AM
Jan 9 2008 12:00AMJan 16 2008 12:00AM
Jan 17 2008 12:00AMJan 24 2008 12:00AM
I need to break the loop after the additional line
StartDate EndDate
Jan 25 2008 12:00AMJan 29 2008 12:00AM
Anyone else have any ideas?
Thanks
December 22, 2008 at 12:34 pm
I needed to loop through dates and just grab a sequence number each time from the tally table to run it against parameters in a stored procedure. I then incorporated that into my existing data variables in a TEMP table in order to get the proper data sent to Crystal. That is the only way I could get it to work the way I wanted it to with the structure I already have. And it did work very well and gave me the proper dates with a little math on the date frequency and sequence table. Now the only issue is making the break in the correct way to get the loop to stop once the 'Looped' @EndDate1 is > the 'global' @EndDate parameter.
December 22, 2008 at 12:41 pm
In the previous post, you wrote ...
The select works to get me the counts. And the idea of the Tally table does make the selects more simplistic. I just have to figure out how to make each date "range" a declared variable or column to add it to my TEMP table that already exists for each of the database pulls for data counts.
The answer to that will be in the form of a dynamic crosstab (I believe) and that bit of computational heaven can be found at the following link...
[font="Arial Black"]Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2008 at 1:36 pm
There as got to be an easier way. The entire procedure mathematically is currently working,except for producing too many rows of data. The cross tabbing, for the purpose of what I am trying to do, is a little overkill.
Within the stored procedure look at the statements. In reality I shouldn't even need a break as the section:
FROM dbo.Tally T
WHERE T.N <= DATEDIFF(d,@StartDate,@EndDate) / @Freq +1) IC
order by n
should stop it.
It is creating the start dates in intervals of 8:
1/1/2008
1/9/2008
1/17/2008
1/24/2008
The difference between the 'global' start date and 'global' end date is 28 and with a frequency of 7 the date diff should give me a result of 3.5 rounded to 4. The where clause should check that against the tally table and not pull any N value if it is greater than 4. But it does not seem to be stopping there. I'm getting
2008-01-01 00:00:00.0002008-01-08 00:00:00.000
2008-01-09 00:00:00.0002008-01-16 00:00:00.000
2008-01-17 00:00:00.0002008-01-24 00:00:00.000
2008-01-25 00:00:00.0002008-01-29 00:00:00.000
2008-02-02 00:00:00.0002008-01-29 00:00:00.000
December 22, 2008 at 2:12 pm
Nah... you just got bit by the rounding with integers... try this...
DECLARE @StartDate DATETIME,
@EndDate DATETIME,
@Frequency INT
SELECT @StartDate = '1/1/2008',
@EndDate = '1/29/2008',
@Frequency = 7
SELECT d.StartDate,
CASE WHEN d.EndDate < @EndDate THEN d.EndDate ELSE @EndDate END AS EndDate
FROM (
SELECT DATEADD(dd,(t.N-1)*@Frequency,@StartDate) AS StartDate,
DATEADD(dd,(t.N-1)*@Frequency,@StartDate)+@Frequency-1 AS EndDate
FROM dbo.Tally t
WHERE t.N <= DATEDIFF(dd,@StartDate,@EndDate)*1.0/@Frequency+1
)d
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2008 at 7:06 pm
That does get the dates correctly. But I need it in a cursor format so that it will spit out a results set with the dates as the first 2 columns. The calculations of each column are based on the start date and end date ranges and I want the results to be a table version that I can then bring into Crystal reports.
What I currently have works, except for ending the loop. I see that your statement works, but cannot fit it into the format that I need.
I finally did get it to work with:
DECLARE CUR_DATES CURSOR FOR
SELECT IC.N,
IC.StartDate1,
IC.EndDate1
FROM ( SELECT N,
DATEADD(d, (@Freq * (N-1)),@StartDate) StartDate1,
CASE WHEN
DATEADD(d,(@Freq * N),@StartDate) > @EndDate THEN @EndDate
ELSE DATEADD(d,((@Freq *N)-1),@StartDate)
END EndDate1
FROM dbo.Tally T
WHERE T.N <= DATEDIFF(d,@StartDate,@EndDate) / @Freq + 1
) IC
order by n
I have an issue with some of the counts still, but 15 out of 19 that work and a date range that works makes me happy 🙂
Thanks for all the help!
Alicia
December 22, 2008 at 9:12 pm
Frankly, I cannot see a single reason to use a Cursor for this. It would work much better and faster if you dropped it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 5:27 am
I agree... tell us what the rest of the problem is and maybe use the link in my signature to find out how to post some test data. There's no need to bog your system down with a cursor on this.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply