about this senario... - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

about this senario...

Author

Topic

ppatil
Starting Member

9 Posts

Posted - 2006-08-23 : 02:36:54

Dear All,

I have one table say Timesheet
values are as follows
job# activity# workfunction# Hours# comment
1# 1 # 1 # 2 # asa
2# 1 # 1 # 3 # sbsb
3# 2 # 2 # 5 # sdsa
4# 2 # 3 # 6 # fdgg

Now senario is that

I want to update Job from table timesheet (1 and 2 to 5) and (3 and 4 to 6).
Now it looks like follows;
job# activity# workfunction# Hours #comment
5# 1 # 1 #2 #asa
5# 1 # 1 #3 #sbsb
6# 2 # 2 #5 #sdsa
6# 2 # 3 #6 #fdgg

Insted of this I want
job# activity# workfunction# Hours #comment
5# 1 # 1 #5 #asa,sbsb
6# 2 # 2 #11 #sdsa,fdgg

Any solution for this.
Thanks In advance.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-23 : 03:21:46

Somthing like this


Declare @TimeSheet Table 
(
[ID] Int Identity(1,1),
job int,
activity int,
workfunction int,
Hours int,
comment varchar(10)
)
Insert @TimeSheet 
Select 1, 1 , 1 , 2 , 'asa' Union All
Select 2, 1 , 1 , 3 , 'sbsb' Union All
Select 3, 2 , 2 , 5 , 'sdsa' Union All
Select 4, 2 , 3 , 6 , 'fdgg' 

Update @TimeSheet Set Job = Case When Job In ( 1,2) Then 5 
                            When Job In (3,4) Then 6   
			End 

-- Now update other details. 
Update t Set Hours = t.Hours+ f.hours,Comment = t.Comment + ',' + f.Comment From @TimeSheet t
Inner Join (Select * From  @TimeSheet t1 
            Where [id] = (Select Max(id) From @TimeSheet t2 where t1.[job] =t2.[job])) f
on t.job = f.job and t.[id] <> f.[id] 

-- Delete the unwanted records. 
delete t from @TimeSheet t where t.[id] = (select max([id]) from @timesheet t2 where t.[job] = t2.[job])

Select * From @TimeSheet

Chirag

ppatil
Starting Member

9 Posts

Posted - 2006-08-23 : 03:39:11

Dear Chirag,

But One issue is that when I updated that job I have to check activity and workfunction for that job are same or not. If same then I have to do next.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-23 : 03:50:10

you mean to say while updating the jobs to 5 and 6 you need to check that?

Chirag

ppatil
Starting Member

9 Posts

Posted - 2006-08-23 : 03:53:55

Yes, For this any solution.

ppatil
Starting Member

9 Posts

Posted - 2006-08-23 : 08:12:11

Hey, I created procedure for that .
Thnaks A lot for U r Support.