WHILE Statement with CURSOR
-
I have a cursor that goes thorugh a table and updates a Date table.
Except that I need to increase the FISCAL_DAY column by 1 until I get to the next record in my fetch.
I just cant get it, anyone can give me a heads up?
Here is my cursor code.
====================
DECLARE dt_cur CURSOR
FOR SELECT mep_from_dt, mep_to_dt FROM mend_period
OPEN dt_cur
FETCH dt_cur INTO @from_dt, @to_dt
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE tmp_D_Dates
SET fiscal_day = DATEPART(DD, @from_dt),
fiscal_month = DATEPART(M,@from_dt),
fiscal_year = DATEPART(YYYY,@from_dt),
fiscal_month_nm = UPPER(DATENAME(MONTH,@from_dt))
WHERE dt BETWEEN @from_dt and @to_dt
FETCH dt_cur INTO @from_dt, @to_dt
END
CLOSE dt_cur
DEALLOCATE dt_cur
-
If I understand correctly, you want to bump @from_dt by one day. So say @from_date = 1/31/2005, you would want fiscal_day to be 1 (as in 2/1/2005) not 32.
If that's the case,
SET fiscal_day = DATEPART(DD,DATEADD(day,1,@from_dt)),
SET fiscal_month = DATEPART(DD,DATEADD(day,1,@from_dt)),
etc..
As an aside, I think there is a way to do this without using a cursor altogether. Something like:
UPDATE tmp_D_Dates SET fiscal_day = DATEPART(DD, DATEADD(day,1,B.mep_from_dt)), fiscal_month = DATEPART(M,DATEADD(day,1,B.mep_from_dt)), fiscal_year = DATEPART(YYYYDATEADD(day,1,B.mep_from_dt)), fiscal_month_nm = UPPER(DATENAME(MONTHDATEADD(day,1,B.mep_from_dt)) FROM tmp_D_Dates A JOIN mend_period B ON A.dt BETWEEN B.mep_from_dt AND mep_to_dt
Test that before letting loose in production though.
JR
-
Thanks for the reply. But will this add 1 to the day for all dates in my between statement.
Example:
I have to update this day filed for all dates between Jan 1 and Jan 31. But I need to add 1 each time until the last day in my between statement
-
Maybe a snippet of sample data would help, I don't quite get the goal.
You have table mend_period which looks like:
mep_from_dt mep_end_dt 1/1/2005 1/31/2005
2/1/2005 2/28/2005 Then you have table tmp_D_Dates which looks sort of like:
dt fiscal_day fiscal_month fiscal_year fiscal_month_nm 1/15/2005 1/20/2005 2/1/2005 Can you give similar sample of these tables and what you want the end result to be?
Thanks,
JR
-
Using the "FROM" and "TO" date, I want to select all the dates in the dates table that fall in that range.
"FROM" value 1/1/2005, "TO" value 1/31/2005 then increase the day field by one as I update the table to the end of the "TO" value then RESET to 1 to start again for the next month.
dt fiscal_day fiscal_month fiscal_year fiscal_month_nm 1/1/2005 1
1 2005 1/2/2005 2 1 2005 2/1/2005 1 2 2005 -
So fiscal_day is a counter of the items in that period order by date? So if the data looked like this, the results would be:
dt fiscal_day fiscal_month fiscal_year fiscal_month_nm 1/1/2005 1
1 2005 1/2/2005 2 1 2005 1/15/2005 3 1 2005 1/20/2005 4 1 2005 1/20/2005 5 1 2005 2/1/2005 1 2 2005 Assuming the period is defined as 1/1/2005 to 1/31/2005.
(notice the duplicate date of 1/20/2005)
Is that what you mean?
-
Thats right. We can think of the fiscal as a counter and it resets itself when a new month is reached.
-
OK, that's a little different than my original answer was trying to do. Basically, you want to walk your tmp_D_Dates table instead, incrementing this row counter as you go. Although you said it "resets itself when a new month is reached", I'm writing this so that it resets when the med_period record changes. That might be the same thing if mend_period is full of monthly periods. Anyway, here's the query although I haven't parsed it against a database to ensure it's 100% correct.
DECLARE @from_dt datetime DECLARE @to_dt datetime DECLARE @thedate datetime DECLARE @lastfrom datetime DECLARE @counter int
--Set a default value SET @lastfrom = '1/1/1900'
DECLARE dt_cur CURSOR FOR SELECT A.dt, B.mep_from_dt, B.mep_to_dt FROM tmp_D_Dates A JOIN mend_period B ON A.dt BETWEEN B.mep_from_dt AND B.mep_to_dt ORDER BY A.dt, B.mep_from_dt FOR UPDATE OF dt
OPEN dt_cur FETCH dt_cur INTO @thedate, @from_dt, @to_dt
WHILE @@FETCH_STATUS = 0 BEGIN --If we are no longer on the same period, reset the counter. IF (@lastfrom <> @from_dt) SET @counter = 1
UPDATE tmp_D_Dates SET fiscal_day = @counter, fiscal_month = DATEPART(M,@from_dt), fiscal_year = DATEPART(YYYY,@from_dt), fiscal_month_nm = UPPER(DATENAME(MONTH,@from_dt)) WHERE CURRENT OF dt_cur
--increment counter SET @count = @counter + 1
--hold value of previous from_dt to compare next time through. SET @lastfrom = @from_dt
FETCH dt_cur INTO @thedate, @from_dt, @to_dt
END CLOSE dt_cur DEALLOCATE dt_cur
Hope that helps.
JR
-
Generally speaking, cursors are bad for performance, and should be avoided if you have large amounts of data.
The following is (I hope
) a set-based solution:-- Create tables and test data
declare @mend_period table (mep_from_dt datetime, mep_end_dt datetime)
insert @mend_period select '2005-1-1', '2005-1-31'
insert @mend_period select '2005-2-1', '2005-2-28'
declare @tmp_D_Dates table (dt datetime)
insert @tmp_D_Dates select '2005-1-1'
insert @tmp_D_Dates select '2005-1-2'
insert @tmp_D_Dates select '2005-1-15'
insert @tmp_D_Dates select '2005-1-20'
insert @tmp_D_Dates select '2005-1-20'
insert @tmp_D_Dates select '2005-2-1'
-- Create and populate @Dates table for subsequent use
declare @Dates table (id int identity(1, 1), dt datetime, mep_from_dt datetime)
insert @Dates (dt, mep_from_dt) select d.dt, m.mep_from_dt from @tmp_D_Dates d inner join @mend_period m
on m.mep_from_dt <= d.dt and d.dt <= m.mep_end_dt
order by d.dt
-- Final query
select d.dt, d.id - p.id + 1 as fiscal_day, datepart(m, d.dt) as fiscal_month, datepart(yyyy, d.dt) as fiscal_year
from @Dates d inner join
(
select mep_from_dt, min(id) as id from @Dates group by mep_from_dt
)
p on p.mep_from_dt = d.mep_from_dt
-
Very true. Cursors will suck the life out of a SQL server. Here's another angle for a set based operation.
First, I had to assume that we have (or can add) some kind of unique key on the tmp_D_dates tables. I used a guid for testing, but an identity int would work as well:
create table tmp_D_Dates (id uniqueidentifier default(newid()), dt datetime, fiscal_day int, fiscal_month int, fiscal_year int)
Then I used this Update statement to fill in the fiscal_day number:
UPDATE tmp_D_Dates SET fiscal_day = ( SELECT count(*)+1 FROM tmp_D_Dates B WHERE B.dt BETWEEN P.mep_from_dt AND P.mep_end_dt --Only count records before or equal to dt AND A.dt >= B.dt --And only count those before dt, or having a lower id value. AND (A.dt > B.dt OR A.id > B.id) ) FROM tmp_D_Dates A JOIN mend_period P ON A.dt BETWEEN P.mep_from_dt AND P.mep_end_dtBasically, I'm telling it to update fiscal_days with the count of all records before the current one and in the same period. However, I had to deal with the possibility of multiple entries for the same dt value. That's why I added the id column, and the test to make sure that we don't count a record multiple times.
If dt is unique, we don't need the id column, and the inner WHERE clause gets simplified to:
WHERE B.dt BETWEEN P.mep_from_dt AND P.mep_end_dt AND A.dt > B.dt
I haven't profiled this for performance, but with proper indexing I think it will clip along quite nicely.
JR
-
thanks JR it worked perfectly
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply