quote:

---

Originally posted by srucker

I need help from you guru's.
I have this sp which normailzes some data to a temp table. I need to extend the "return results" select statement to join and get more data.
So I add task_charge_type to the select and created 3 joins to get it all together.
I can execute the alter sp command with no errors; however when I try to execute the sp I get the following errors:

Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69
Ambiguous column name 'PROJ_NAME'.
Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69
Ambiguous column name 'PROJ_NAME'.




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- sp_psactualsbyweek.sql
--
-- This stored procedure generates a report of the actual hours worked by a
-- resource by project and task for a specific date range from the table
-- MSP_WEB_WORK.

ALTER procedure [dbo].[sp_psactualsbyweek]
-- date range variables
@startdate datetime,
@enddate datetime
as

-- cursor variables
declare @res_name nvarchar(510)
declare @proj_name nvarchar(510)
declare @task_name nvarchar(510)
declare @start datetime
declare @finish datetime
declare @value decimal(25,6)
declare @days int
declare @count int
declare @date datetime

-- temp table to hold normalized data
create table #actuals_data (
RES_NAME nvarchar(510),
PROJ_NAME nvarchar(510),
TASK_NAME nvarchar(510),
WORK_DAY datetime,
HOURS decimal(25,6) )

-- cursor to convert denormalized data
declare actuals_csr cursor for

SELECT DISTINCT r.RES_NAME,
p.PROJ_NAME,
a.TASK_NAME,
w.WWORK_START,
w.WWORK_FINISH,
w.WWORK_VALUE
FROM MSP_WEB_RESOURCES AS r
INNER JOIN MSP_WEB_ASSIGNMENTS AS a ON a.WRES_ID = r.WRES_ID
INNER JOIN MSP_WEB_WORK AS w ON w.WASSN_ID = a.WASSN_ID
INNER JOIN MSP_WEB_PROJECTS AS p ON p.WPROJ_ID = a.WPROJ_ID
where w.WWORK_TYPE = 1 -- actual work
and ((w.WWORK_START between @startdate and @enddate) or (w.WWORK_FINISH between @startdate and @enddate))
order by 1, 2, 3, 4

open actuals_csr
fetch next from actuals_csr into @res_name, @proj_name, @task_name, @start, @finish, @value
while @@fetch_status = 0
begin
select @days = DATEDIFF(day, @start, @finish) + 1
select @count = 0
while @count < @days
begin
select @date = DATEADD(day ,@count, @start)
insert into #actuals_data values( @res_name, @proj_name, @task_name, @date, ((@value/1000)/60) )
select @count = @count + 1
end

fetch next from actuals_csr into @res_name, @proj_name, @task_name, @start, @finish, @value
end
close actuals_csr
deallocate actuals_csr

-- return results
select RES_NAME,
{p/pe/te}.PROJ_NAME,
Task_Name,
Task_Charge_Type = case when pe.ProjectEnterpriseOutlineCode30ID = 530 Then 'Expensed'
when pe.ProjectEnterpriseOutlineCode30ID = 529 and te.TaskEnterpriseOutlineCode30ID = 527 Then 'Capital' end,
Work_Day,
sum(HOURS) as 'TOTAL HOURS'
from #actuals_data
INNER JOIN MSP_WEB_PROJECTS AS p on #actuals_data.PROJ_NAME = p.PROJ_NAME
INNER JOIN MSP_VIEW_PROJ_PROJECTS_ENT AS pe ON pe.WPROJ_ID = p.WPROJ_ID
INNER JOIN MSP_VIEW_PROJ_TASKS_ENT AS te ON te.WPROJ_ID = pe.WPROJ_ID
where WORK_DAY between @startdate and @enddate
group by RES_NAME, {p/pe/te}.PROJ_NAME, Task_Name, Work_Day
order by res_name

drop table #actuals_data


--Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69
--Ambiguous column name 'PROJ_NAME'.
--Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69
--Ambiguous column name 'PROJ_NAME'.

---

select * FROM
(
select RES_NAME, 
p.PROJ_NAME, 
Task_Name, 
'Task_Charge_Type' = case when pe.ProjectEnterpriseOutlineCode30ID = 530 Then 'Expensed' 
when pe.ProjectEnterpriseOutlineCode30ID = 529 and te.TaskEnterpriseOutlineCode30ID = 527 Then 'Capital' end, 
a.Work_Day, 
sum(a.HOURS) as 'TOTAL HOURS'
from #actuals_data as a,
MSP_WEB_PROJECTS AS p --on #actuals_data.PROJ_NAME = p.PROJ_NAME
INNER JOIN MSP_VIEW_PROJ_PROJECTS_ENT AS pe ON pe.WPROJ_ID = p.WPROJ_ID
INNER JOIN MSP_VIEW_PROJ_TASKS_ENT AS te ON te.WPROJ_ID = pe.WPROJ_ID
where WORK_DAY between @startdate and @enddate)t
group by RES_NAME, p.PROJ_NAME, Task_Name, Task_Charge_Type, Work_Day
order by res_name

select t.RES_NAME, 
t.PROJ_NAME, 
t.Task_Name, 
t.Task_Charge_Type, 
t.Work_Day, t.TOTAL HOURS

 FROM
(
select RES_NAME, 
p.PROJ_NAME, 
Task_Name, 
'Task_Charge_Type' = case when pe.ProjectEnterpriseOutlineCode30ID = 530 Then 'Expensed' 
when pe.ProjectEnterpriseOutlineCode30ID = 529 and te.TaskEnterpriseOutlineCode30ID = 527 Then 'Capital' end, 
a.Work_Day, sum(a.HOURS) as 'TOTAL HOURS'
from #actuals_data as a
INNER JOIN MSP_WEB_PROJECTS AS p  ON condition
INNER JOIN MSP_VIEW_PROJ_PROJECTS_ENT AS pe ON pe.WPROJ_ID = p.WPROJ_ID
INNER JOIN MSP_VIEW_PROJ_TASKS_ENT AS te ON te.WPROJ_ID = pe.WPROJ_ID
where WORK_DAY between @startdate and @enddate)t
group by t.RES_NAME, t.PROJ_NAME, t.Task_Name, t.Task_Charge_Type, t.Work_Day
order by t.res_name