shilkhanna
3/29/2007 6:22:00 PM
here is my code, i need to get my result in concatenation format. Also
I need my output for a particular project_ID should show all the
employee working on that project_id. Right now i am getting the result
like this. thanks in adavance, any help will be appreciated.
Project_ID-150
Project_Name- Enchancement Advance
Budget_CODE-ID- EAR
ActualCompletionDate- 5/5/2006
estimatedCompletiondate- 5/1/2006
TotalHrs= 15
Name- Laalia Lucy
Project_ID-150
Project_Name- Enchancement Advance
Budget_CODE-ID- EAR
ActualCompletionDate- 5/5/2006
estimatedCompletiondate- 5/1/2006
TotalHrs= 20
Name- Adams Ross
But i want my output like this:
Project_ID-150
Project_Name- Enchancement Advance
Budget_CODE-ID- EAR
ActualCompletionDate- 5/5/2006
estimatedCompletiondate- 5/1/2006
TotalHrs= 35
Name- Laalia Lucy, Adams Ross
Declare @Full_Name varchar(5000)
Declare @FinalString varchar(5000)
Declare @Project_ID varchar(3)
declare @Project_Name varchar(5000)
declare @Budget_Code_ID varchar(4)
declare @actual_completion_date datetime,
@actual_completion_date1 datetime,
@estimated_completion_date datetime,
@estimated_completion_date1 datetime
Declare @Total_Hrs varchar(10)
Set @Full_Name =' '
Set @project_ID= 0
Set @Project_Name= ''
Set @Budget_code_id= ''
set @actual_completion_date = '09/30/2006'
set @actual_completion_date1 = '10/01/2007'
set @estimated_completion_date = '09/30/2006'
set @estimated_completion_date1= '10/01/2007'
Set @Total_hrs= ''
SET @FinalString = ''
SET @FinalString = @FinalString + @Full_Name + ', '
set @actual_completion_date= convert(varchar(15),
Month(@actual_completion_date))+'/'+ convert(varchar(15),
day(@actual_completion_date))+ '/' + convert(varchar(15),
Year(@actual_completion_date))
set @estimated_completion_date= convert(varchar(15),
Month(@estimated_completion_date))+'/'+ convert(varchar(15),
day(@estimated_completion_date))+ '/' + convert(varchar(15),
Year(@estimated_completion_date))
Set ANSI_warnings off
declare cur_first_name cursor for
select dbo.tap_project.Project_ID,
dbo.tap_project.actual_completion_date,
dbo.tap_project.estimated_completion_date,
dbo.vwTAP_Employee.First_Name + SPACE(1) +
dbo.vwTAP_Employee.Last_Name as Full_Name
from dbo.tap_employee_project_week,
dbo.tap_project,
vwTAP_Employee,
dbo.tap_project_budget
where dbo.tap_project.project_id = dbo.tap_project_budget.project_id
and dbo.TAP_Employee_Project_Week.iPersonID =
dbo.vwTAP_Employee.iPersonId
and (actual_completion_date between @actual_completion_date and
@actual_completion_date1
OR estimated_completion_date between @estimated_completion_date
and @estimated_completion_date1)
group by dbo.tap_project.Project_ID, Actual_Completion_Date,
estimated_completion_date, dbo.vwTAP_Employee.First_Name,
dbo.vwTAP_Employee.Last_Name
order by dbo.tap_project.Project_ID
open cur_first_name
fetch next from cur_first_name
into @Project_ID, @actual_completion_date, @estimated_completion_date,
@Full_Name
while (@@FETCH_STATUS = 0)
begin
declare cur_Project_name cursor for
select
dbo.tap_project.Project_Name,
dbo.tap_budget_code.Budget_Code_ID,
sum(hours)as total_hrs
from dbo.tap_project,
dbo.tap_budget_code,
dbo.tap_project_budget,
dbo.tap_employee_project_week
where dbo.tap_employee_project_week.project_budget_code_id =
dbo.tap_project_budget.project_budget_code_id
and dbo.tap_project_budget.budget_code_id =
dbo.tap_budget_code.budget_code_id
and dbo.tap_project.project_id = @project_id
and dbo.tap_project_budget.project_id = @Project_id
--and hours >= 0
group by dbo.tap_project.project_id, dbo.tap_project.Project_Name,
dbo.tap_budget_code.Budget_Code_ID
order by dbo.tap_budget_code.Budget_code_ID
open cur_project_name
fetch next from cur_Project_name
into @Project_Name, @Budget_Code_ID, @Total_Hrs
while (@@FETCH_STATUS = 0)
begin
if (@project_ID = 2)
begin
--select @project_ID, @Project_Name, @Budget_Code_ID,
@actual_completion_date, @estimated_completion_date,
--@Total_hrs, @Full_Name
--set [Support Space Renovation and Moves 2] = @Project_Name
--where Project_ID = @project_id and Project_Name= @Project_Name
--print + @Project_ID + ''
--print + @Project_Name + ''
--Print + @Budget_Code_ID+ ''
--print + @Actual_completion_date+ ''
--Print + @estimated_completion_date+ ''
--print + @Total_Hrs+ ''
--print + @Full_Name
update Tap_project
set [Enhance Phone Conferencing 150] = @Project_Name
where project_id = @project_id
end
fetch next from cur_first_name
into @Project_ID, @actual_completion_date, @estimated_completion_date,
@Full_Name
end
close cur_first_name
deallocate cur_first_name
fetch next from cur_Project_name
into @Project_Name, @Budget_Code_ID, @Total_hrs
end
close cur_Project_name
deallocate cur_Project_name