Thanks Michael...
It works like a charm. I was trying to steer away from using a Cursor but as I got more into it I knew that was the only way to go. Again thanks for your help.
Jim
-----Original Message-----
From: Michael van der Veeke
[mailto:mssqldba-ezmlmshield-x99860338.[Email address protected]
Sent: Wednesday, August 25, 2004 1:28 AM
To: LazyDBA Discussion
Subject: RE: Query
Hi Jim,
We need to use a cursor to do this:
--Declare cursor to select all unique refid's
Declare cRefid cursor scroll for
select distinct refid from tblA
--Variable declarations
declare @refid int
Declare @CID int
Declare @iYear int
Declare @i int
Declare @iPriorYear int
--Open cursor and get first rec
open cRefid
fetch first from cRefid into @refid
While (@@Fetch_status=0)
Begin
--select all records associated with refid
Declare cTblA cursor scroll for
Select cID, iyear from TblA
Where refid = @refid
Order by iYear
--set counter to zero
set @i = 0
--open cursor and get last rec. We get last record and work
backwards
Open cTblA
Fetch last from cTblA into @CID, @iyear
While (@@Fetch_Status=0)
Begin
if (@i=0)
Begin
--update the last record with the current date
Update tblA
Set YearTo = datepart(Year, getdate())
Where CID = @CID
--record year of record
set @iPriorYear = @iYear
--make sure we don't come back here
set @i = 1
End
else
Begin
--update record to the year of the previous record
minus 1
Update tblA
Set YearTo = @iPriorYear - 1
Where CID = @CID
--record year of record
set @iPriorYear = @iYear
end
--move back to first record until all are looked at
Fetch PRIOR from cTblA into @CID, @iyear
End
--close cursor and deallocate it
-- so that we can reuse it
Close cTblA
Deallocate cTblA
--get next refid
fetch next from cRefid into @refid
End
--close cursor and deallocate it
Close cRefid
Deallocate cRefid
Let me know how you go..
HTH
Regards,
Michael van der Veeke
Technical Director
Property4view Pty Ltd, Australia
Database Administrator
Border Express Pty Ltd
Phone: +61 (0) 260 226 046
Mobile : 0419 419 547
Email : [Email address protected]
[Email address protected]
Web : http://www.property4view.com
http://www.borderexpress.com
NOTICE
The information contained in this electronic mail message is privileged and
confidential, and is intended only for use of the addressee. If you are not
the intended recipient, any disclosure, reproduction, distribution or other
use of this communication is strictly prohibited. If you have received this
communication in error, please notify the sender by reply transmission and
delete the message without copying or disclosing it.
-----Original Message-----
From: JRotan [mailto:mssqldba-ezmlmshield-x8039740.[Email address protected]
Sent: Wednesday, 25 August 2004 12:24 AM
To: LazyDBA Discussion
Subject: Query
Hello Everyone,
I have been tasked with supplying a date range. Here's the table:
TblA
--------
cid int
refid int
year int
cite varchar(2000)
Here 's a sample of the data:
cid refid year cite
1 1 1990 XXX
2 1 1991 YYY
3 2 1995 ZZZ
4 2 1999 AAA
Now my manager wants me to add another column to the table called yearto.
I'm trying to write a query that would find yearto from the data so I can
UPDATE the table to reflect this. The business rule is that the current
status for a record is the years between the record and the next record that
have the same refid. If there is none than the current status is up to the
current year -- 2004. Here's what the table will look like after I run the
UPDATE
cid refid year cite yearto
1 1 1989 XXX 1991
2 1 1992 YYY 2004
3 2 1995 ZZZ 1998
4 2 1999 AAA 2004
Any suggestions/comments??
TIA
Jim
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page