Can I improve the 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 2000 Forums

Transact-SQL (2000)

Can I improve the query.

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2003-12-01 : 09:29:53

Hello,
I am writing a sql script to update a table so that the start date and end date on all rows are in sequential order. There is no overlapping In dates for all rows

I have wriiten a script that makes use of a cursor. Although the script works. I was wondering if there was better way to do this below is the script that i wrote.

Declare @chshowcd varchar(10),
@ishowyear int,
@Revtype varchar(20)

SELECT @chshowcd = 'PSIJ',
@ishowyear = 2004,
@Revtype = 'PSIJ Advert'

IF Object_id('#ShowAdjustments') is not null
Drop table #ShowAdjustments

SELECT chGLCode,
iAdjustmentId,
chAdjustCategory,
chAdjustCd,
vchAdjustDesc,
chAdjustType,
dtEffectiveDate,
dtExpirationDate

INTO #ShowAdjustments

FROM omAdjustmentRules
WHERE chShowCd = @chshowcd and
iShowYear = @ishowyear and
chRevTypeCd = @RevType
Order By iAdjustmentID

DECLARE @iAdjusment int,
@Startdate datetime,
@EndDate datetime,
@PrevEndDate datetime

UPDATE #ShowAdjustments
Set dtEffectiveDate ='01/01/2003',
dtExpirationDate='02/01/2003'
from #ShowAdjustments

EXEC ('Declare AdjustmentDates insensitive cursor
for SELECT
iAdjustmentId,
dtEffectiveDate,
dtExpirationDate
FROM #ShowAdjustments Order By IAdjustmentID asc ')

OPEN AdjustmentDates

FETCH NEXT FROM AdjustmentDates INTO @iAdjusment , @Startdate ,@EndDate

WHILE @@FETCH_STATUS = 0

BEGIN

/* First Row*/
IF @PrevEndDate is null
BEGIN
Set @Startdate = dateadd(dd,1,@Startdate)
Set @EndDate = dateadd(dd,1,@Startdate)

Update #ShowAdjustments set dtEffectiveDate = @Startdate,
dtExpirationDate =@EndDate
FROM #ShowAdjustments
WHERE #ShowAdjustments.iAdjustmentId = @iAdjusment

SET @PrevEndDate = @Enddate

END
ELSE
IF @PrevEndDate is not null

BEGIN
Set @Startdate = dateadd(dd,1,@PrevEndDate)
Set @EndDate = dateadd(dd,1,@Startdate)

Update #ShowAdjustments set dtEffectiveDate = @Startdate,
dtExpirationDate =@EndDate
FROM #ShowAdjustments
WHERE #ShowAdjustments.iAdjustmentId = @iAdjusment

SET @PrevEndDate = @Enddate

END

FETCH NEXT FROM AdjustmentDates INTO @iAdjusment , @Startdate ,@EndDate
END

CLOSE AdjustmentDates
DEALLOCATE AdjustmentDates

Thanks in Advance

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-01 : 10:09:02

[code]
create table test (n int, startdate datetime,enddate datetime)

insert into test
select 1,'01/01/2003','02/01/2003'
union all select 3 ,'01/01/2003','02/01/2003'
union all select 4 ,'01/01/2003','02/01/2003'
union all select 7 ,'01/01/2003','02/01/2003'
union all select 9 ,'01/01/2003','02/01/2003'
union all select 13 ,'01/01/2003','02/01/2003'

select identity(int,1,1) id,n,startdate,enddate --other fields
into #update
from test
--where criteria

select * from #update

declare @id int; set @id = 1
while @id <= (select max(id) from #update)
begin
update #update
set startdate = dateadd(day,@id-1,startdate),
enddate = dateadd(day,@id-1,enddate)
where id = @id
set @id = @id+1
end

select * from #update

drop table test
drop table #update
[/code]

miranwar
Posting Yak Master

125 Posts

Posted - 2003-12-01 : 10:32:43

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources