November 16, 2007 at 9:20 am
I am new to SQL Server and need help fast....
If have the following table data...
[font="Tahoma"]ProjProjID ProjStartDT ProjEndDT
------------------------------------
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 NULL NULL
88355.022.00 NULL NULL
88355.023 NULL NULL
88355.023.00 NULL NULL
[/font]
I 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, if it does not find a date then it goes up a level, but if it hits (in thise example, 88355.022) then it spits out an error message. Is this too difficult? Please help if you can...
Thnaks in advance.
November 18, 2007 at 8:27 pm
update table
set ProgStartDT = (
select top 1 ProjStartDT
from table where projstartdt is not null
order by ProjProjID desc)
where ProgStartDT is null
Are you looking for something like that?
The other option is to go with
update t
set t.ProgStartDT = x.ProjStartDT
from table t
inner join
(select top 1 ProjStartDT
from table where projstartdt is not null
order by ProjProjID desc) x
on x.ProjProjID > t.ProjProjID
where t.ProgStartDT is null
Paul Ross
November 19, 2007 at 3:48 am
Thanks So Much Paul... I will give that a try on our test box. I appreciate the help.
Kipp
November 19, 2007 at 5:43 am
There is a small problem... and again, I do not know how to fix it...
THe update on the date should only go up one level...
ProjProjID ProjStartDT ProjEndDT
------------------------------------
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 NULL NULL
88355.022.00 07/01/07 NULL
88355.023 NULL NULL
88355.023.00 08/01/2007 NULL
on the last line, it should grab the 08/01/2007 and update the start date for 88355.023 . Likewise, the 07/01/07 should update 88355.022 with the start_date. The bottom level should not flow all the way up he chain...
November 19, 2007 at 10:01 am
Can you change the clustered index on the table?
November 19, 2007 at 10:10 am
unfortunately not, I don't have access to that.
November 19, 2007 at 10:26 am
Just makes it a little harder, but this was a trick that I was shown a few weeks back.
/*
CREATE TABLE [tblTest](
[ProjProjID] [varchar](50) NOT NULL,
[ProjStartDt] [datetime] NULL,
[ProjEndDT] [datetime] NULL,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[ProjProjID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
*/
DECLARE @Dummy datetime,@id VARCHAR(50),@Dt DATETIME
UPDATE tblTest
SET
@Dummy = ProjStartDt = CASE WHEN ((ProjStartDt IS NULL) AND (ProjProjID = SUBSTRING(@id,1,len(projProjID)))) THEN @dt ELSE ProjStartDt END,
@dt = ISNULL(projStartDt,@DT),
@ID = ProjProjID
FROM tblTest
SELECT * FROM tblTest
Since you can't do this directly, create a temp table, extract the needed data, then update based upon the temp table.
The trick here is that the data is sorted in the table based upon the clustered index. So you are safe to make the assumption that the previous row will be what you expect it to be.
The CASE... Statement basicall states, don't change the value if you either have one, or it isn't part of the same project.
Then you set your variables to their current values.
There is a reason for the @dummy, but I can't recall what it is.
This should get you started
November 19, 2007 at 10:54 am
ok, this is what I have, but it does not seem to be updating for some reason...
DROP TABLE proj_id_11256; -- my temp table
SELECT * INTO proj_id_11256
FROM PROJ order by proj_id;
-- proj is my real table that I will be using this on once it works
-- below the query is the data I have...
begin tran
update proj_id_11256 SET proj_start_dt = '2006-01-01'
where proj_id = '11256.001.04'
commit tran;
BEGIN TRAN
DECLARE @Dummy smalldatetime,
@id VARCHAR(50),
@dt smallDATETIME
UPDATE proj_id_11256
SET
@Dummy = proj_start_dt = CASE WHEN ((proj_start_dt IS NULL)
AND (proj_id = SUBSTRING(@id,1,len(proj_id))))
THEN @dt ELSE proj_start_dt END,
@dt = ISNULL(proj_start_dt,@DT),
@ID = proj_id
FROM proj_id_11256
COMMIT TRAN;
-- Data
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 NULL NULL
11256.001.04 2006-01-01 00:00:00 NULL
I changed the start_dt for 11256.001.04 , which should the after the script is run, flow and make the dates for 11256.001.03 and 11256.001.02 the same as .04, .01 will not change since it already has a date.
November 19, 2007 at 7:53 pm
kipp (11/19/2007)
ok, this is what I have, but it does not seem to be updating for some reason...DROP TABLE proj_id_11256; -- my temp table
SELECT * INTO proj_id_11256
FROM PROJ order by proj_id;
-- proj is my real table that I will be using this on once it works
-- below the query is the data I have...
First of all. Temp tables begin with # that allows so no need to mask
DROP TABLE #proj -- my temp table
SELECT * INTO #proj
FROM PROJ
Don't worry about the order by, we will fix that now.
create clustered index #proj_ci on #proj(proj_id) desc
The decending clusterd index is key here. It forces the order of the data in the table, not the order by you tried to use.
begin tran
update #proj SET proj_start_dt = '2006-01-01'
where proj_id = '11256.001.04'
commit tran;
That's fine but there is an issue, my design was not intended for the example you are giving now.
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))))
THEN @dt ELSE proj_start_dt END,
@dt = ISNULL(proj_start_dt,@DT),
@ID = proj_id
FROM #proj
COMMIT TRAN;
Think about what the proj_id = substring... part is doing. I am saying if the current proj_id is a substring of the previous proj_id then use it if the proj_start_dt is also null. The example you gave before is not the same as the one below. You will have to get more creative with your case statement.
-- Data
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 NULL NULL
11256.001.04 2006-01-01 00:00:00 NULL
I changed the start_dt for 11256.001.04 , which should the after the script is run, flow and make the dates for 11256.001.03 and 11256.001.02 the same as .04, .01 will not change since it already has a date.
Sorry
'11256.001.03' != substring('11256.001.04',1,len('11256.001.03'))
but
'11256.001' = substring('11256.001.04',1,len('11256.001')) DOES!
Hope this helps
November 20, 2007 at 3:39 am
Hi Bob... Did not mean to change things. The example I gave now is what I got after I implemented the changes. I am going to give your suggestions a try and see how things go. I will let you know. Will you be around today if I have any other questions? Thanks for your help, I am learning, slow, but learning....
Kipp
November 20, 2007 at 5:09 am
ok, I think I am just dense or something.
Below is what I have, it is not updating.. I am really lost and dont know what to do next... Sorry. Should this be updating my temp table?
USE testdb;
DROP TABLE #proj -- my temp table
SELECT * INTO #proj
FROM PROJ WHERE proj_start_dt IS NULL OR proj_end_dt IS NULL;
CREATE CLUSTERED INDEX #proj_ci ON #proj(proj_id DESC)
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))))
THEN @dt ELSE proj_start_dt END,
@dt = ISNULL(proj_start_dt,@DT),
@ID = proj_id
FROM #proj
COMMIT TRAN;
November 20, 2007 at 5:29 am
Hi,
I think you want this,
SELECT *
INTO #proj
FROM PROJ WHERE proj_start_dt IS NULL OR proj_end_dt IS NULL;
CREATE CLUSTERED INDEX #proj_ci ON #proj(proj_id DESC)
BEGIN TRAN
DECLARE @Dummy smalldatetime,
@id VARCHAR(50),
@dt smallDATETIME
UPDATE #proj
SET @Dummy = CASE WHEN ((proj_start_dt IS NULL) AND (proj_id = SUBSTRING(@id,1,len(proj_id))))
THEN @dt ELSE proj_start_dt END,@Dt = ISNULL(proj_start_dt,@DT),@ID = proj_id
COMMIT TRAN;
Regards,
ahmed
November 20, 2007 at 5:40 am
thanks.. I must be missing something... below is what I have.
11256 1995-07-05 00:00:00
11256.000 NULL
11256.000.00 NULL
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
What it should be doing is starting at the lowest (11256.001.04 or 11256.000.00) going up until it hits a date and then using that date to fill in what is below. So 11256.001.02,11256.001.03 and 11256.001.04 should have a date of what is in 11256.001.01.
Please help if you know what I am saying here....
November 20, 2007 at 6:21 am
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] DESC)
)
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',NULL,null)
INSERT INTO [#proj] VALUES ('11256.001.04','2006-01-01 00:00:00',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
((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;
SELECT * FROM #proj
This works, but the logic may not be right.
November 20, 2007 at 6:35 am
thanks Bob... Let me give that a try... I appreciate your help... just looking at your code and playing with it you have taught me alot already.
Thanks.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply