October 25, 2007 at 9:33 am
Sorry, I've been here before, deja-vu all over again.
Cannot find previous thread.
I need to update a date field based on the next record in a grouped sequence. The eff_date_end = (eff_date_start -1) from th next record in sequence if it exists. If not,set to 1/1/2099.
Based on cd1, cd2
id | cd1 | cd2 | date_start | date_end
1 | a | a | 1/1/1960 |
2 | a | a | 4/1/1967 |
3 | a | a | 8/1/1999 |
4 | b | b | 1/1/1960 |
5 | c | c | 1/1/1960 |
6 | c | c | 7/1/2001 |
should end up -
id | cd1 | cd2 | date_start | date_end
1 | a | a | 1/1/1960 | 3/31/1960
2 | a | a | 4/1/1967 | 7/31/1967
3 | a | a | 8/1/1999 | 1/1/2099
4 | b | b | 1/1/1960 | 1/1/2099
5 | c | c | 1/1/1960 | 6/30/2001
6 | c | c | 7/1/2001 | 1/1/2099
thanks for your support.
Randy McKnight
October 25, 2007 at 9:49 am
after-thought;
just update all eff_end_date with 1/1/2009 prior. Then I don't have to worry about that in the logic.
Randy.
October 25, 2007 at 10:27 am
Here's an example - adapt it to using your table and field names.
use testing
go
drop index endmatric.ix_em_coend
--create the index that will help with the sequencing.
create index ix_em_coend on endmatric(company,date_start desc)
--grouping will be determined based on company, and put the start dates in reverse order for traversing the table
declare @dummy datetime
declare @def_end datetime
declare @temp datetime
declare @previd varchar(2)
select @previd=''
set @def_end='1/1/2099'
set @temp=@def_end
--the WITH (INDEX()) syntax forces the sort during the update, so it makes the process work
--the extra throwaway variable @dummy makes the running part work in 2005
update endmatric
set
@dummy=date_end=case when @previd=company then dateadd(dd,-1,@temp) else @def_end end,
@temp=date_start,
@previd=company
from endmatric WITH (index(ix_em_coend),TABLOCK)
--see what you've done
select * from endmatric with (index(ix_em_coend),tablock)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 25, 2007 at 11:29 am
Thats genius.
Thanks.
Randy.
October 25, 2007 at 11:51 am
I was working on a simialr solution but when I tried your index trick to get the update in the right order it still goes asc. Might be because I am using SQL7
create index ix_mytable on mytable(cd1,cd2,date_start desc)
declare @dummy datetime,@newdate datetime, @cd1 varchar(5),@cd2 varchar(5)
update mytable
set
@dummy=date_end = case when @cd1 = cd1 and @cd2 = cd2 then dateadd(dd,-1,@newdate) else '1/1/2009' end,
@cd1 = cd1,
@cd2 = cd2,
@newdate = date_start
from mytable with (index(ix_mytable),tablock)
id cd1 cd2 date_start date_end
4 a a 1960-01-01 00:00:00.000 2009-01-01 00:00:00.000
5 a a 1967-04-01 00:00:00.000 1959-12-31 00:00:00.000
2 a a 1999-08-01 00:00:00.000 1967-03-31 00:00:00.000
3 b b 1960-01-01 00:00:00.000 2009-01-01 00:00:00.000
6 c c 1960-01-01 00:00:00.000 2009-01-01 00:00:00.000
1 c c 2001-07-01 00:00:00.000 1959-12-31 00:00:00.000
(6 row(s) affected)
October 25, 2007 at 12:16 pm
Sorry - have no idea if it will work against 7.0.
If anything I have a gut feeling that the DESC syntax is invalid on 7.0.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 26, 2007 at 4:49 am
It's probably because the WITH INDEX trick doesn't really work.
Before anyone blows a fuse, by 'not really work' I mean 'not supported', 'isn't reliable', 'cannot be guranteed 100% to always behave like one thinks', etc....
This is one of those 'ordering tricks' that indeed seems to work, but it doesn't in the sense of 'can I trust it to always do what I want'.
It's an exploit on the the physical implementation of the table, and as such SQL Server itself leaves no guarantees that the 'order we expect' will always be preserved in all cases. The key component here is what the execution plan looks like when the query is executed, and I belive, also which isolation level.
So, what I'm saying is: test it and test it again, and if you really decide to use it, then one is on one's own. That is, use the 'trick' on your own risk.
/Kenneth
October 26, 2007 at 5:13 pm
MrPoleCat,
Would you be kind enought to test this on your SQL Server 7? Thank you, Sir...
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#yourtable','U') IS NOT NULL
DROP TABLE #yourtable
--===== Recreate the test table
CREATE TABLE #yourtable
(
ID INTEGER PRIMARY KEY CLUSTERED,
CD1 VARCHAR(10),
CD2 VARCHAR(10),
Date_Start DATETIME,
Date_End DATETIME
)
--===== Populate the test table with data from the post
INSERT INTO #yourtable
(ID,CD1,CD2,Date_Start)
SELECT '1','a','a','1/1/1960' UNION ALL
SELECT '2','a','a','4/1/1967' UNION ALL
SELECT '3','a','a','8/1/1999' UNION ALL
SELECT '4','b','b','1/1/1960' UNION ALL
SELECT '5','c','c','1/1/1960' UNION ALL
SELECT '6','c','c','7/1/2001'
--===== Add the required indexes and keys
CREATE INDEX IDX_yourtable_CD1_CD2_Date_Start
ON #yourtable (CD1 ASC, CD2 ASC, Date_Start DESC)
GO
--===== Declare some require local variables
DECLARE @PrevCD1 VARCHAR(10)
DECLARE @PrevCD2 VARCHAR(10)
DECLARE @PrevStartDate DATETIME
DECLARE @Dummy DATETIME
SELECT @PrevCD1 = '', @PrevCD2=''
--===== Do the update using the index to control the order
UPDATE #yourtable
SET @Dummy = Date_End = CASE
WHEN CD1 = @PrevCD1
AND CD2 = @PrevCD2
THEN @PrevStartDate - 1
ELSE '01/01/2099'
END,
@PrevCD1 = CD1,
@PrevCD2 = CD2,
@PrevStartDate = Date_Start
FROM #yourtable WITH (INDEX(IDX_yourtable_CD1_CD2_Date_Start),TABLOCK)
--===== Display the results in the correct order
SELECT *
FROM #yourtable
ORDER BY CD1,CD2,Date_Start
--Jeff Moden
October 26, 2007 at 5:46 pm
October 26, 2007 at 7:10 pm
Roger that...
--Jeff Moden
October 29, 2007 at 7:27 am
October 29, 2007 at 5:29 pm
mrpolecat (10/29/2007)
Jeff--It throws errors
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'ASC'.
Server: Msg 208, Level 16, State 1, Line 12
Invalid object name '#yourtable'.
It's gotta be the way you're pasting the code... I get no errors...
[font="Courier New"](6 row(s) affected)
(6 row(s) affected)
ID CD1 CD2 Date_Start Date_End
----------- ---------- ---------- ------------------------------------------------------ ------------------------------------------------------
1 a a 1960-01-01 00:00:00.000 1967-03-31 00:00:00.000
2 a a 1967-04-01 00:00:00.000 1999-07-31 00:00:00.000
3 a a 1999-08-01 00:00:00.000 2099-01-01 00:00:00.000
4 b b 1960-01-01 00:00:00.000 2099-01-01 00:00:00.000
5 c c 1960-01-01 00:00:00.000 2001-06-30 00:00:00.000
6 c c 2001-07-01 00:00:00.000 2099-01-01 00:00:00.000
(6 row(s) affected)[/font]
Copy from the code box on the thread, paste into MS Word, replace all ^l (circumflex lower case "L") to ^p (circumflex lower case "P"). Then, copy from that...
Man, I sure do wish they'd fix this stuff...
--Jeff Moden
October 29, 2007 at 5:45 pm
I did that. I fixed the last error which appeared to be a wierd character carried over. The first error "Near ASC" is real. I took out ASC in both spots and it works. I tried replacing them with DESC and it failed. It doesn't throw the error on the last order but it doesn't use it either. Appears to be an SQL 7 non feature.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply