How do you know it's going to be a run once script?
How do you know it's a small data set?
How do you know that new records inserted are going to have a non null yearto entry?
Its obvious to me that you're caught up in your own titles and don't like other people giving their opinions.
Me personally, I like to read everyone's questions and answers so if I pick up a thing or two, I feel as though doing a "sometimes" boring DBA work is worthwhile.
And on the flip side if I can share a few things that I've picked up in my time as being a DBA then that's great
Joe
-----Original Message-----
From: Thomas Anthony [mailto:mssqldba-ezmlmshield-x84598755.[Email address protected]
Sent: Saturday, 28 August 2004 5:51 PM
To: LazyDBA Discussion
Subject: RE: Query
Don't get me wrong, I run screaming anytime I get handed script with the
words CURSOR in them; however, that's the problem, it's a word. It is
just because it has been so mistreated and so misused.
Here's the bottom line. If it's in a script, all likelihood is that it
will only be ran once.
If the recordset is small, and will remain small, there won't be enough
difference to care about.
If you spend more time thinking about how to do it without a cursor than
it would to implement and run the cursor, you'd be waisting time.
Save the tricky stuff for when you want a puzzle to solve on the
weekends, not when you need to get work done.
Me, personally, I'm a puzzle person. That's why I respond to this
distribution list. So, more often than not, I try to come up with a
set-based solution. But that doesn't mean you have to.
Sincerely,
Anthony Thomas, MCDBA, MCSA
-----Original Message-----
From: Joe Bertone
[mailto:mssqldba-ezmlmshield-x45351776.[Email address protected]
Sent: Thursday, August 26, 2004 8:50 PM
To: LazyDBA Discussion
Subject: RE: Query
Hi,
This code seemed to work for me
declare @currentyear int
set @currentyear = datepart(yy,getdate())
select *,
yearto = isnull((select top 1 yearfrom-1 from temp t2 where
t2.yearfrom > t1.yearfrom and t1.refid = t2.refid order by yearfrom
asc),@currentyear)
from temp t1
Regards,
Joe
-----Original Message-----
From: Michael van der Veeke
[mailto:mssqldba-ezmlmshield-x99860338.[Email address protected]
Sent: Wednesday, 25 August 2004 3:28 PM
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]
---------------------------------------------------------------------
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]
***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************
---------------------------------------------------------------------
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