trying to make into function

microsoft.public.sqlserver.programming

20 Mar 2007 9:03 PM

shilkhanna

Trying to make into function, want to combine the specify project_id
to get the result in same row with all the information such as
project_name, budget_code_id etc without any repeats. can someone
help. thanks in advance.

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, @estimated_completion_date
datetime
Declare @Total_Hrs varchar(10)

set @actual_completion_date = '09/30/2006'
set @actual_completion_date = '10/01/2007'
set @estimated_completion_date = '09/30/2006'
set @estimated_completion_date= '10/01/2007'

SET @FinalString = ''
Set ANSI_warnings off
declare cur_first_name cursor for

select
dbo.tap_project.Project_ID,
dbo.tap_project.Project_Name,
dbo.tap_budget_code.Budget_Code_ID ,
dbo.tap_project.actual_completion_date,
dbo.tap_project.estimated_completion_date,
sum(hours)as total_hrs,
dbo.vwTAP_Employee.First_Name + SPACE(1) +
dbo.vwTAP_Employee.Last_Name as Full_Name
    from dbo.tap_employee_project_week,
    dbo.tap_project,
    dbo.tap_budget_code,
    dbo.tap_project_budget,
    dbo.vwTAP_Employee
    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 = dbo.tap_project_budget.project_id
    and dbo.TAP_Employee_Project_Week.iPersonID =
dbo.vwTAP_Employee.iPersonId
    and (actual_completion_date between '09/30/2006' and '10/01/2007'
                OR estimated_completion_date between '09/30/2006' and
'10/01/2007')
    --and hours is not null
group by dbo.tap_project.project_id,
dbo.tap_project.Project_Name,dbo.tap_budget_code.Budget_Code_ID,
Actual_Completion_Date, estimated_completion_date,
dbo.vwTAP_Employee.First_Name, dbo.vwTAP_Employee.Last_Name
order by dbo.tap_budget_code.Budget_code_ID

open cur_first_name
fetch next from cur_first_name
into @Project_ID, @Project_Name, @Budget_Code_ID,
@actual_completion_date, @estimated_completion_date,
@Total_Hrs, @Full_Name

while (@@FETCH_STATUS = 0)
begin

print @Project_ID
Print @Project_Name
Print @Budget_Code_ID
print @actual_completion_date
Print @estimated_completion_date
print @Total_Hrs
Print @full_name

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))

fetch next from cur_first_name
into @Project_ID, @Project_Name, @Budget_Code_ID,
@actual_completion_date, @estimated_completion_date,
@Total_Hrs, @Full_Name

end
close cur_first_name
deallocate cur_first_name