April 22, 2008 at 4:49 am
I have the below data in a table called say "kipp".
I am trying to get a script to work that will look at the last project with a description ,
the look at the projects above that that do not have a description and populate it with the description from the previosly gathered one. the tricky part is that if there is not a description below the last one found then fill it in with the one that was found...
I cant seem to figure it out as a newbie to SQL Server.
Any Help would be greatly appreciated.
Thanks!!
proj_id desc
--------------- ------------------------------
11293
11293.000
11293.000.00
11293.001
11293.001.01 Description1
11293.001.02
11294
11294.000
11294.000.00
11294.001
11294.001.01
11294.001.02 Description2
so basically after the script is ran the data should look like:
proj_id desc
--------------- ------------------------------
11293 Description1
11293.000 Description1
11293.000.00 Description1
11293.001 Description1
11293.001.01 Description1
11293.001.02 Description1
11294 Description2
11294.000 Description2
11294.000.00 Description2
11294.001 Description2
11294.001.01 Description2
11294.001.02 Description2
thanks in advance
April 22, 2008 at 6:25 am
Kipp,
Here is my first pass at this; I hope this helps a little:
with baseProject as
( select distinct
case when charindex('.', proj_id) > 0
then left(proj_id, charindex('.', proj_id) -1)
else proj_id
end as base_project
from kipp
), projDescription as
( select
base_project,
( select top 1
[desc]
from kipp
where [desc] <> ''
and case when charindex('.', proj_id) > 0
then left(proj_id, charindex('.', proj_id) -1)
else proj_id
end = base_project
) as description
from baseProject
)
update kipp
set [desc] = description
from kipp a
join projDescription b
on case when charindex('.', proj_id) > 0
then left(proj_id, charindex('.', proj_id) -1)
else proj_id
end = base_project
and ( [desc] is null or
[desc] <> description
)
select * from kipp
/* -------- Sample Output: --------
proj_id desc
--------------- ---------------
11293 Description1
11293.000 Description1
11293.000.00 Description1
11293.001 Description1
11293.001.01 Description1
11293.001.02 Description1
11294 Description2
11294.000 Description2
11294.000.00 Description2
11294.001 Description2
11294.001.01 Description2
11294.001.02 Description2
*/
April 22, 2008 at 1:39 pm
Here is another alternative that looks a little simpler:
;with groupData as
( select distinct
coalesce(parsename(proj_id,3), parsename(proj_id,2),
parsename(proj_id,1)) as base_project,
max(proj_id) as max_proj_id
from kipp
where [desc] <> ''
group by
coalesce(parsename(proj_id,3), parsename(proj_id,2),
parsename(proj_id,1))
), groupDescription as
( select
base_project,
max_proj_id,
[desc] as description
from groupData
join kipp
on max_proj_id = proj_id
)
update kipp
set [desc] = description
from kipp
join groupDescription
on proj_id like base_project + '%'
and (proj_id = base_project or proj_id like base_project + '.%')
and ([desc] is null or [desc] <> description)
After looking at it I really feel like I like the CASE construction better than using the COALESCE and PARSENAME construction. The CASE construction is going to be more flexible and robust.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply