Very hard SQL ms project type code - help needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Very hard SQL ms project type code - help needed

Author

Topic

greeny122229
Starting Member

25 Posts

Posted - 2009-05-05 : 12:16:45

Hi Guys i have the following 2 tables

>The start and end date of the final task gets populated automatically.

>From this it needs to work backwards referencing table 2 (the one with successor id) and work out the finish and start dates of each task using cost from table 1 as the amount of days

So my ouput would look like:
http://www.mgsc.co.uk/images/help1.bmp

Ideally it needs to use working days only

Any help would be greatly appreciated as i'm not sure where to start

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-05 : 15:14:53

Just to confirm - These tables are in Sql Server 2000 right? And you are looking for sql server 2000 t-sql code as a solution, right?
Also, if this is MS Project can't you use its functionality to set all the dates based on the final completion date?

Be One with the Optimizer
TG

greeny122229
Starting Member

25 Posts

Posted - 2009-05-06 : 03:43:02

hi guys - sorry if i was a tad vague
>I require sql server 2000 code (stored proc) to complete this,
>it is not in ms project - i just used that as an example as this needs to do something similar but in sql2000

any help appreciated

Thanks

Matt

greeny122229
Starting Member

25 Posts

Posted - 2009-05-06 : 09:59:39

anyone have any ideas??

greeny122229
Starting Member

25 Posts

Posted - 2009-05-06 : 10:54:05

Hi Guys,

don't waste your time on this, once i've resolved i'll post the code and a more graphic explanation of what i wanted.

many tahnks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-06 : 15:28:56

I think I got it. Since your data represents a kind of hierachy (id - successor_id) we need to iterate back through the chain. My first solution was set-based but was wrong for task_id 12 since it's branch didn't include task 13. So I switched to the way I would build a tree hierachy in sql 2000.


set nocount on
declare @t1 table 
       (rec_id int
       ,moc_id int
       ,material_id int
       ,amend_id int
       ,task_id int
       ,cost int
       ,start_date datetime
       ,end_date datetime)

insert @t1
select 19, 56, 1, 5, 11, 1, null, null union all
select 20, 56, 1, 5, 12, 2, null, null union all
select 21, 56, 1, 5, 13, 3, null, null union all
select 22, 56, 1, 5, 14, 4, null, null union all
select 23, 56, 1, 5, 15, 5, null, null union all
select 24, 56, 1, 5, 16, 6, '2009-05-13', '2009-05-20'

declare @t2 table 
       (rec_id int
       ,task_id int
       ,successor_id int)
insert @t2
select 24, 11, 12 union all
select 25, 13, 14 union all
select 26, 12, 14 union all
select 27, 14, 15 union all
select 28, 15, 16 

if object_id('tempdb..#work') > 0 
       drop table #work
create table #work 
       (rec_id int
       ,task_id int
       ,cost int
       ,start_date datetime
       ,end_date datetime
       ,running_cost int
       ,lev int
       ,branch varchar(200))


declare @lev int
set @lev = 1

insert #work (rec_id, task_id, cost, lev, branch, running_cost, start_date, end_date)
select t1.rec_id
       ,t1.task_id
       ,t1.cost
       ,@lev
       ,convert(varchar(12), t1.task_id)
       ,t1.cost
       ,dateadd(day, -1*t1.cost, t1.end_date)
       ,t1.end_date
from   @t1 t1 
left outer join @t2 t2 
       on t1.task_id = t2.task_id 
where  t2.successor_id is null 
and    t1.end_date is not null

while @@rowcount > 0
begin
       set @lev = @lev+1

       insert #work (rec_id, task_id, cost, lev, branch, running_cost, start_date, end_date)
       select t1.rec_id
              ,t1.task_id
              ,t1.cost
              ,@lev
              ,w.branch + '-' + convert(varchar(12), t1.task_id)
              ,w.running_cost + t1.cost
              ,dateadd(day, -1*t1.cost, w.start_date)   --start_date is prev start_date less current cost
              ,dateadd(day, -1, w.start_date)           --end_date is 1 day before prev start_date
       from   #work w
       join   @t2 t2
              on t2.successor_id = w.task_id
       join   @t1 t1 on t1.task_id = t2.task_id
       left join #work x
              on x.task_id = t1.task_id
       where  w.lev = @lev - 1
       and    x.task_id is null
end

select * from @t1
select * from @t2

select t1.rec_id
       ,t1.moc_id
       ,t1.material_id
       ,t1.amend_id
       ,t1.task_id
       ,t1.cost
       ,w.start_date
       ,w.end_date
       ,w.running_cost
       ,w.lev
       ,w.branch
from   #work w
join   @t1 t1
       on t1.rec_id = w.rec_id
order by rec_id

OUTPUT:

rec_id      moc_id      material_id amend_id    task_id     cost        start_date              end_date
----------- ----------- ----------- ----------- ----------- ----------- ----------------------- -----------------------
19          56          1           5           11          1           NULL                    NULL
20          56          1           5           12          2           NULL                    NULL
21          56          1           5           13          3           NULL                    NULL
22          56          1           5           14          4           NULL                    NULL
23          56          1           5           15          5           NULL                    NULL
24          56          1           5           16          6           2009-05-13 00:00:00.000 2009-05-20 00:00:00.000

rec_id      task_id     successor_id
----------- ----------- ------------
24          11          12
25          13          14
26          12          14
27          14          15
28          15          16


rec_id      moc_id      material_id amend_id    task_id     cost        start_date              end_date                running_cost lev         branch
----------- ----------- ----------- ----------- ----------- ----------- ----------------------- ----------------------- ------------ ----------- ---------------
19          56          1           5           11          1           2009-05-02 00:00:00.000 2009-05-02 00:00:00.000 18           5           16-15-14-12-11
20          56          1           5           12          2           2009-05-03 00:00:00.000 2009-05-04 00:00:00.000 17           4           16-15-14-12
21          56          1           5           13          3           2009-05-02 00:00:00.000 2009-05-04 00:00:00.000 18           4           16-15-14-13
22          56          1           5           14          4           2009-05-05 00:00:00.000 2009-05-08 00:00:00.000 15           3           16-15-14
23          56          1           5           15          5           2009-05-09 00:00:00.000 2009-05-13 00:00:00.000 11           2           16-15
24          56          1           5           16          6           2009-05-14 00:00:00.000 2009-05-20 00:00:00.000 6            1           16

Be One with the Optimizer
TG

greeny122229
Starting Member

25 Posts

Posted - 2009-05-07 : 09:50:40

Hi thanks for your response (unfortunatly i've only just seen it and have already sorted this in the mean time)- the code I have used is as follows - i'm going to start a new post as i need it to use working days only (mon - fri), thanks for taking the time to investigate though:

--reset test
--update TEMP_TBL_MOC_TASKS set start_date = null, end_date = null

--get last tasks (set initial end date)
begin
update TEMP_TBL_MOC_TASKS
set end_date = '15 may 2009' where task_id in
(select task_id from TEMP_TBL_MOC_TASKS where task_id not in(select task_id from LKP_TASK_PRIORITY))
and moc_id = 56
end
--get last tasks (set Start date time & end date and time)
begin
update b
set b.start_date = a.start_date, b.end_date = a.end_date
from
(
select
task_id,
cost,
dateadd(hh,+9,(dateadd(d,-cost,end_date)))+1 as start_date ,
dateadd(hh,17,(min(end_date))) as end_date
from
(
select task_id, cost, start_date, end_date
from TEMP_TBL_MOC_TASKS
where end_date is not null
)as a
group by
task_id,
cost,
end_date
) as a
left outer join
TEMP_TBL_MOC_TASKS b
on
b.task_id = a.task_id
end
--set end date and start date on all subsequent tasks
while exists (select * from TEMP_TBL_MOC_TASKS where start_date is null and moc_id = 56)
begin
update b
set b.end_date = a.end_date, b.start_date = a.start_date
from
(select
task_id,
a.cost,
dateadd(hh,-8,(dateadd(d,-(cost-1),end_date))) as start_date,
end_date
from(
select a.task_id,a.cost, dateadd(hh,8,(min(c.start_date)))-1 as end_date from temp_tbl_moc_tasks a, lkp_task_priority b, temp_tbl_moc_tasks c
where a.start_date is null and a.task_id = b.task_id and b.sucessor_id = c.task_id
and c.start_date is not null
and not exists
(select * from lkp_task_priority d, temp_tbl_moc_tasks e
where a.task_id = d.task_id and d.sucessor_id = e.task_id and e.start_date is null)
group by
a.task_id,
a.cost
)as a
group by
a.task_id,
a.end_date,
a.cost
)as a
left outer join
TEMP_TBL_MOC_TASKS b
on
b.task_id = a.task_id
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources