shilkhanna
3/21/2007 1:23:00 PM
On Mar 20, 9:08 pm, "xyb" <xiangyua...@gmail.com> wrote:
> On 3?21?, ??5?03?, shilkha...@gmail.com wrote:
>
>
>
>
>
> > 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
>
> and so what is the trouble of your code/function?- Hide quoted text -
>
> - Show quoted text -
code is working fine, just need to add function in there since it is
giving me result like this :
150
Enhance Phone Conferencing
ARD
Oct 23 2006 12:00AM
Aug 1 2006 12:00AM
176
172
Develop Macro to Update QVR spreadsheet (Administrative Review)
ARD
Dec 25 2006 12:00AM
Dec 21 2006 12:00AM
31
213
Develop Draft VED/Breeze video
ARD
Oct 20 2006 12:00AM
Sep 11 2006 12:00AM
213
Develop Draft VED/Breeze video
ARD
Oct 20 2006 12:00AM
Sep 11 2006 12:00AM
Lalla Allga
213
Develop Draft VED/Breeze video
ARD
Oct 20 2006 12:00AM
Sep 11 2006 12:00AM
25
Amy Boys
but i want to show my result like for each project_ID,right now 213 is
repeating on each line, i want to combine that so it gives me result
like this: if u cna help me that will be greatly aprpeciated. thanks
in advance.
213
Develop Draft VED/Breeze video
ARD
Oct 20 2006 12:00AM
Sep 11 2006 12:00AM
25
Lalla Allga, Amy Boys