November 20, 2007 at 6:42 am
awesome it works!!
THe only thing it is doing is the last date is not getting populated...
11256.001 1995-07-05 00:00:00
11256.001.01 1995-07-05 00:00:00
11256.001.02 NULL
11256.001.03 NULL
11256.001.04 NULL
it is flowing down to .03 and stopping.
November 20, 2007 at 8:52 am
Can you post your final code, because what I have does work.
Before:
11256.001.04 2006-01-01 00:00:00 NULL
11256.001.03 NULL NULL
11256.001.02 NULL NULL
11256.001.01 1995-07-05 00:00:00 1996-05-04 00:00:00
11256.001 1995-07-05 00:00:00 1996-05-04 00:00:00
After:
11256.001.04 2006-01-01 00:00:00 NULL
11256.001.03 2006-01-01 00:00:00 NULL
11256.001.02 2006-01-01 00:00:00 NULL
11256.001.01 1995-07-05 00:00:00 1996-05-04 00:00:00
11256.001 1995-07-05 00:00:00 1996-05-04 00:00:00
November 20, 2007 at 8:55 am
yeah, below is what I have....
BEGIN TRAN
DECLARE @Dummy smalldatetime,
@id VARCHAR(50),
@dt smallDATETIME
UPDATE #proj
SET
@Dummy = proj_start_dt = CASE WHEN (
(proj_start_dt IS NULL)
AND
((proj_id = SUBSTRING(@id,1,len(proj_id)))
OR(SUBSTRING(proj_id,1,len(proj_id)-3) = SUBSTRING(@id,1,len(proj_id)-3))))
THEN @dt ELSE proj_start_dt END,
@dt = ISNULL(proj_start_dt,@DT),
@ID = proj_id
FROM #proj
COMMIT TRAN; [/size][/size]
November 20, 2007 at 8:56 am
oops... with out the "[/size]"
BEGIN TRAN
DECLARE @Dummy smalldatetime,
@id VARCHAR(50),
@dt smallDATETIME
UPDATE #proj
SET
@Dummy = proj_start_dt = CASE WHEN (
(proj_start_dt IS NULL)
AND
((proj_id = SUBSTRING(@id,1,len(proj_id)))
OR(SUBSTRING(proj_id,1,len(proj_id)-3) = SUBSTRING(@id,1,len(proj_id)-3))))
THEN @dt ELSE proj_start_dt END,
@dt = ISNULL(proj_start_dt,@DT),
@ID = proj_id
FROM #proj
COMMIT TRAN;
November 20, 2007 at 9:07 am
kipp (11/20/2007)
awesome it works!!THe only thing it is doing is the last date is not getting populated...
11256.001 1995-07-05 00:00:00
11256.001.01 1995-07-05 00:00:00
11256.001.02 NULL
11256.001.03 NULL
11256.001.04 NULL
it is flowing down to .03 and stopping.
Can you show me what you mean here? 02-04 are all NULL. So they wouldn't change here. Also the fact that the data here is in the reverse order mildly concerns me, you didn't forget about the index again?
November 20, 2007 at 9:31 am
yes, the way it should work (sorry if I did not explain this better), is find the last one that has a date (in this case 11256.001.01), and then use the date that is in there and populate all the start_dates below that (ie. start_dates for 11256.001.02,11256.001.03,11256.001.04) would all have a start date equal to the last one found (ie. 11256.001.01)...
it is in reverse order just so I could see in in numerical order, it did in fact do it in the correct order when I got the output, sorry about that...).
11256.001 1995-07-05 00:00:00
11256.001.01 1995-07-05 00:00:00
11256.001.02 NULL
11256.001.03 NULL
11256.001.04 NULL
November 20, 2007 at 9:54 am
Ok. Then that is the exact opposite that I thought you wanted.
The index should be ASC not descending, and the part where the substrings are needs adjusted slightly. Think I got it this time.
DROP TABLE #proj -- my temp table
CREATE TABLE #Proj(
[Proj_ID] [varchar](50) NOT NULL,
[Proj_Start_Dt] [smalldatetime] NULL,
[Proj_End_DT] [smalldatetime] NULL,
CONSTRAINT PK_Proj PRIMARY KEY CLUSTERED
([Proj_ID] ASC)
)
INSERT INTO [#proj] VALUES ('11256.001','1995-07-05 00:00:00','1996-05-04 00:00:00')
INSERT INTO [#proj] VALUES ('11256.001.01','1995-07-05 00:00:00','1996-05-04 00:00:00')
INSERT INTO [#proj] VALUES ('11256.001.02',NULL,null)
INSERT INTO [#proj] VALUES ('11256.001.03','1/1/2007',null)
INSERT INTO [#proj] VALUES ('11256.001.04',NULL,null)
SELECT * FROM #proj
BEGIN TRAN
DECLARE @Dummy smalldatetime,
@id VARCHAR(50),
@dt smallDATETIME
UPDATE #proj
SET
@Dummy = proj_start_dt = CASE WHEN (
(proj_start_dt IS NULL)
AND
((@id = SUBSTRING(proj_id,1,len(@id)))
OR(SUBSTRING(proj_id,1,len(@id)-3) = SUBSTRING(@id,1,len(@id)-3))))
THEN @dt ELSE proj_start_dt END,
@dt = ISNULL(proj_start_dt,@DT),
@ID = proj_id
FROM #proj
COMMIT TRAN;
SELECT * FROM #proj
Before
11256.001 1995-07-05 00:00:00 1996-05-04 00:00:00
11256.001.01 1995-07-05 00:00:00 1996-05-04 00:00:00
11256.001.02 NULL NULL
11256.001.03 2007-01-01 00:00:00 NULL
11256.001.04 NULL NULL
after
11256.001 1995-07-05 00:00:00 1996-05-04 00:00:00
11256.001.01 1995-07-05 00:00:00 1996-05-04 00:00:00
11256.001.02 1995-07-05 00:00:00 NULL
11256.001.03 2007-01-01 00:00:00 NULL
11256.001.04 2007-01-01 00:00:00 NULL
November 20, 2007 at 10:55 am
thanks... I will give that a try, I am about ready to leave for the day but will be back in tomorrow... thanks for your help and I will let you know. Where are you located? timezone?
Thanks!
November 20, 2007 at 11:05 am
Pittsburgh (EST) so I have a few hours yet, and I won't be back to work until next Monday. Thanksgiving Holiday. I am sure I will check in over while I am home, but family will be in "which is why I may need a retreat" so I won't be able to check very often.
Good luck, the technique should work, issue now is the CASE WHEN logic.
Also, if the current PK is on the PROJ_ID which I expect it is, and assuming that it is a clustered index which is most likely the case, and it is sorted in the right order, you don't need the temp table anymore. However, this is a safe test.
November 20, 2007 at 11:07 am
Thanks Bob.... We are fairly close, I am from Dayton.
Have a good Thanksgiving....
November 20, 2007 at 11:12 am
This example will:
* Take each record where the ProjStartDt is NULL,
* Trim off the last '.' + whatever follows,
* And, try to match that result with a record in the table.
If a match is found, and its ProjStartDt is not NULL then the original's ProjStartDt is updated.
tblTest data to start with:
88355 NULL NULL
88355.001 NULL NULL
88355.001.00 NULL NULL
88355.020 NULL NULL
88355.020.00 NULL NULL
88355.021 NULL NULL
88355.021.00 NULL NULL
88355.022 08/01/07 NULL
88355.022.00 NULL NULL
88355.023 07/01/07 NULL
88355.023.00 NULL NULL
UPDATE tblTest
SET ProjStartDt =
(Select Top 1 c.ProjStartDt
From tblTest c
Where c.ProjProjID = Substring(b.ProjProjID,1,
Case When Len(b.ProjProjID) - (len(b.ProjProjID) - charindex('.', b.ProjProjID, len(c.ProjProjID)) + 1) < 1
Then 1
Else Len(b.ProjProjID) - (len(b.ProjProjID) - charindex('.', b.ProjProjID, len(c.ProjProjID)) + 1)
End)
AND c.ProjProjID <> b.ProjProjID
Order by Len(c.ProjProjID) Desc
)
FROM tblTest b
WHERE b.ProjStartDt IS NULL
AND (Select Top 1 c.ProjStartDt
From tblTest c
Where c.ProjProjID = Substring(b.ProjProjID,1,
Case When Len(b.ProjProjID) - (len(b.ProjProjID) - charindex('.', b.ProjProjID, len(c.ProjProjID)) + 1) < 1
Then 1
Else Len(b.ProjProjID) - (len(b.ProjProjID) - charindex('.', b.ProjProjID, len(c.ProjProjID)) + 1)
End)
And c.ProjProjID <> b.ProjProjID
Order By Len(c.ProjProjID) Desc
) IS NOT NULL
tblTest data after first run:
88355 NULL NULL
88355.001 NULL NULL
88355.001.00 NULL NULL
88355.020 NULL NULL
88355.020.00 NULL NULL
88355.021 NULL NULL
88355.021.00 NULL NULL
88355.022 08/01/07 NULL
88355.022.00 08/01/07 NULL
88355.023 07/01/07 NULL
88355.023.00 07/01/07 NULL
-----------------------------------------------------------------------------------------------------------
If you have a ProjStartDt for 88355, then the Update statement will update: 88355.001, 88355.020, and 88355.021 with 88355’s ProjStartDt.
tblTest data to start with:
88355 06/01/07 NULL
88355.001 NULL NULL
88355.001.00 NULL NULL
88355.020 NULL NULL
88355.020.00 NULL NULL
88355.021 NULL NULL
88355.021.00 NULL NULL
88355.022 08/01/07 NULL
88355.022.00 NULL NULL
88355.023 07/01/07 NULL
88355.023.00 NULL NULL
tblTest data after first run:
88355 06/01/07 NULL
88355.001 06/01/07 NULL
88355.001.00 NULL NULL
88355.020 06/01/07 NULL
88355.020.00 NULL NULL
88355.021 06/01/07 NULL
88355.021.00 NULL NULL
88355.022 08/01/07 NULL
88355.022.00 08/01/07 NULL
88355.023 07/01/07 NULL
88355.023.00 07/01/07 NULL
If the Update statement is run a second time then 88355.001.00, 88355.020.00, and 88355.021.00 will be updated to 88355’s ProjStartDt.
P.S.
My text was formatted when I pasted it, but Preview is showing it with the spaces trimmed out.
November 20, 2007 at 11:39 am
Oh great... Helping a Bengal. Hoping to be at the 12/2 game 🙂
November 20, 2007 at 11:51 am
here is a before and after... it is somehow pulling another date...
ProjProjID ProjStartDT
------------------------------ --------------------
11256.027.05 NULL
11256.027.04 NULL
11256.027.03 NULL
11256.027.02 NULL
11256.027.01 2002-07-24 00:00:00
11256.027 2002-07-24 00:00:00
ProjProjID ProjStartDT
------------------------------ ---------------------
11256.027.05 NULL
11256.027.04 1997-10-01 00:00:00
11256.027.03 1997-10-01 00:00:00
11256.027.02 1997-10-01 00:00:00
11256.027.01 2002-07-24 00:00:00
11256.027 2002-07-24 00:00:00
November 20, 2007 at 12:26 pm
Just to clarify the assumptions I made prior to posting my solution above:
Your original post said,"...need a script that looks for NULL start and end dates at the bottom level (ie. 88355.023.00), if it finds a NULL then it goes up one level (ie. 88355.023), if it finds a date then it updates the level(s) below it, in this case 88355.023.00..."
I assumed that levels were delimited with the dots "." So, up one level from [88355.023.00] would be [88355.023]. Not because 88355.023 was the next record above 88355.023.00, but because, after stripping off the .00, 88355.023 is what you are left with.
My solution assumes that 88355 is the next level up for the following five records:
88355.001
88355.020
88355.021
88355.022
88355.023
It doesn't matter what order the records are in to start with.
Using the results you just posted, (I changed the date for 11256.027.01 so you can tell which record's date I am using.) The following would be the results of my UPDATE.
Before:
ProjProjID ProjStartDT
------------------------------ --------------------
11256.027.05 NULL
11256.027.04 NULL
11256.027.03 NULL
11256.027.02 NULL
11256.027.01 2002-08-24 00:00:00
11256.027 2002-07-24 00:00:00
After:
ProjProjID ProjStartDT
------------------------------ --------------------
11256.027.05 2002-07-24 00:00:00
11256.027.04 2002-07-24 00:00:00
11256.027.03 2002-07-24 00:00:00
11256.027.02 2002-07-24 00:00:00
11256.027.01 2002-08-24 00:00:00
11256.027 2002-07-24 00:00:00
My update will change .05,.04,.03,.02 to 11256.027's date because their date is null and because 11256.027 is one level up from each of them (by my understanding of the term "Level").
Anyway, if I misunderstood the issue I am sorry.
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply