Ok, Here is the SP with the problem. Maybe another set of Eyes can see what is wrong. I am getting multi-part identifier could not be bound on every field.
Any help would be great.
Thank you.
SELECT
jn.branch_no as [Branch #],
b.branch_name as [Branch Name],
Case When jn.install_flag='Y' Then
'Install'
Else
'Service'
End as [Install/Service],
jn.jobgl_id as [GL],
jt.descr as [Job Type],
ct.Descr as [Customer Type],
jn.billto_cust_no as [Cust No],
s.Sitetype_id as [Site Type],
s.cspart_no as [CS Partition],
s.servarea_id as [Service Area],
s.site_no as [Site No],
s.site_name as [Site Name],
s.site_addr1 as [Site Address],
s.site_addr2 as [Site Address 2],
s.City_name as [City],
s.state_id as [State],
s.zip_code as [Zip],
IsNull(e1.last_name,'None') as [Sales Person],
IsNull(e.last_name,'None') as [Project Manager],
IsNull(e2.last_name,'None') as [Lead Employee],
jn.job_no as [Job No],
js.descr as [Job Status],
jn.change_date as [Closed Date],
jn.Servplan_id as [Service Plan],
--lak added next 4 rows 9/19/07
IsNull(jr.descr,'Install') as [Job Request],
IsNull(jn.cs_no,'None') as [CS No],
syst.descr as [System Type],
IsNull(jn.udf1,' ') as [WMS Proposal No],
IsNull(jn.udf2,' ') as [PO NTE],
IsNull(jn.udf4,' ') as [Service Type],
sum(jc.orig_direct_labor) as [Est. Labor],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_Direct_Labor
End) as [Labor],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_overhead
End) as [OverHead],
Sum(jc.orig_equip_cost) as [Est. Equip],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_Equip_Cost
End) as [Equipment],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_NonInventory_Cost
End) as [Non Inventory],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_SubContract
End) as [Sub-Contract],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_Misc_Cost
End) as [Misc],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_Permit_Cost
End) as [Permit],
sum( Case when ac.seqno 0 then
0
Else
ac.Act_hours
End) as [Actual Hours]
,Sum(Case When ac.seqno0 Then
0
Else
ac.act_Total_Cost
End) as [Actual Total Cost],
Sum(Case When ac.seqno0 Then
0
Else
jc.rev_Total_Cost
End) as [Revised Total Cost],
Sum(Case When jc.rev_total_Cost = 0 Then
0
Else
Case When ac.seqno0 Then
0
Else
100*((ac.act_Total_Cost-jc.rev_Total_Cost)/jc.rev_Total_Cost)
End
End) as [Var%],
Sum(Case When ac.seqno0 Then
0
Else
jc.Orig_Total_Cost
End) as [Original Total Cost],
Sum(Case When jc.Orig_Total_Cost = 0 Then
0
Else
Case When ac.seqno0 Then
0
Else
100*((ac.act_Total_Cost-jc.Orig_Total_Cost)/jc.Orig_Total_Cost)
End
End) as [Var%],
Sum(Case When ac.seqno0 Then
0
Else
ac.act_Total_Price
End) as [Net Price],
Sum(Case When ac.Act_Total_Price = 0 Then
0
Else
Case When ac.seqno0 Then
0
Else
100*((ac.act_Total_Price-ac.act_Total_Cost)/ac.act_Total_Price)
End
End) as [Margin]
,Sum(Case When ac.seqno0 Then
0
Else
Case When
Case When ac.seqno0 Then
0
Else
ac.act_direct_labor
End = 0 And
Case When ac.seqno0 Then
0
Else
ac.act_equip_cost
End = 0 And
Case When ac.seqno0 Then
0
Else
IsNull(ac.act_misc_cost,0)+IsNull(ac.act_noninventory_cost,0)+IsNull(ac.act_overhead,0)+IsNull(ac.act_permit_cost,0)+IsNull(ac.act_subcontract,0)
End = 0 Then
0
Else
Case When jh.disc_id is null Then
jc.rev_total_price
Else
Case When substring(jh.disc_id,1,3)='REF' OR substring(jh.disc_id,1,3)='IPB' Then
jc.rev_total_price+jh.disc_amt
Else
jc.rev_total_price
End
End
End
End) as [Contract Price],
IsNull(SWR.WIPAmount,0) as [Previous WIP Revenue]
,Case when isNull(SWR.WIPAmount,0) < 0 Then
------------------------ Start of Contract Price --------------------------------------
(Sum(Case When ac.seqno0 Then
0
Else
Case When
Case When ac.seqno0 Then
0
Else
ac.act_direct_labor
End = 0 And
Case When ac.seqno0 Then
0
Else
ac.act_equip_cost
End = 0 And
Case When ac.seqno0 Then
0
Else
IsNull(ac.act_misc_cost,0)+IsNull(ac.act_noninventory_cost,0)+IsNull(ac.act_overhead,0)+IsNull(ac.act_permit_cost,0)+IsNull(ac.act_subcontract,0)
End = 0 Then
0
Else
Case When jh.disc_id is null Then
jc.rev_total_price
Else
Case When substring(jh.disc_id,1,3)='REF' OR substring(jh.disc_id,1,3)='IPB' Then
jc.rev_total_price+jh.disc_amt
Else
jc.rev_total_price
End
End
End
End)
------------------------ End of Contract Price ----------------------------------------
- --(Minus)
------------------------ Start of Progress Billing -------------------------------------
(Sum(IsNull(ac.progress_bill_amt,0))+
Sum(Case When substring(jh.disc_id,1,3)='IPB' Then
Case When ac.seqno>0 Then
0
Else
IsNull(jh.disc_amt,0)
End
Else
0
End)
------------------------ End of Progress Billing --------------------------------------
- --(Minus)
abs(isNull(SWR.WIPAmount,0))))
Else
------------------------ Start of Contract Price --------------------------------------
(Sum(Case When ac.seqno0 Then
0
Else
Case When
Case When ac.seqno0 Then
0
Else
ac.act_direct_labor
End = 0 And
Case When ac.seqno0 Then
0
Else
ac.act_equip_cost
End = 0 And
Case When ac.seqno0 Then
0
Else
IsNull(ac.act_misc_cost,0)+IsNull(ac.act_noninventory_cost,0)+IsNull(ac.act_overhead,0)+IsNull(ac.act_permit_cost,0)+IsNull(ac.act_subcontract,0)
End = 0 Then
0
Else
Case When jh.disc_id is null Then
jc.rev_total_price
Else
Case When substring(jh.disc_id,1,3)='REF' OR substring(jh.disc_id,1,3)='IPB' Then
jc.rev_total_price+jh.disc_amt
Else
jc.rev_total_price
End
End
End
End)
------------------------ End of Contract Price ----------------------------------------
- --(Minus)
sum(isNull(SWR.WIPAmount,0)))
End as [Net $ Earned],
Sum(IsNull(ac.progress_bill_amt,0))+
Sum(Case When substring(jh.disc_id,1,3)='IPB' Then
Case When ac.seqno>0 Then
0
Else
IsNull(jh.disc_amt,0)
End
Else
0
End) as [Progress Billing]
,jn.comment as [Comment]
FROM
dfm_job_no_cons jn with (nolock)
Left outer join job_actual_cost ac with (nolock) on jn.origjob = ac.job_no
Left outer join job_type jt with (nolock) on jn.jobtype_id = jt.jobtype_id
Left outer join job_status js with (nolock) on jn.jobstat_id = js.jobstat_id
Left outer join job_header_discount jh on jn.origjob = jh.job_no
Left outer join customer c with (nolock) on jn.billto_cust_no = c.cust_no
Left outer join customer_type ct with (nolock) on c.cusType_id = ct.cusType_id
Left outer join site s with (nolock) on jn.site_no = s.site_no
Left outer join job_cost jc with (nolock) on jn.origjob = jc.job_no
Left outer join employee e with (nolock) on jn.sales_emp_no = e.emp_no
Left outer join employee e1 with (nolock) on c.sales_emp_no = e1.emp_no
Left outer join employee e2 with (nolock) on jn.LeadEmployee = e2.emp_no
Left outer join branch b with (nolock) on jn.branch_no = b.branch_no
Left outer join site_type st with (nolock) on s.sitetype_id = st.sitetype_id
Left outer join system_type syst with (nolock) on jn.systype_id = syst.systype_id
Left outer join job_request jr with (nolock) on jn.jobreq_id = jr.jobreq_id
Left Outer Join (Select swr1.jobno,swr1.wipamount
from DPC_SavedWipRevenue swr1 with(nolock)
where swr1.datesaved = (Select Max(swr2.datesaved)
from DPC_SavedWipRevenue swr2 with(nolock)
where swr1.jobno = swr2.jobNO)) SWR on SWR.jobNO = jn.job_no
WHERE
jn.branch_no in (6,7) and
js.jobstat_id = 'C' AND
jn.change_date >= '08/01/2009 00:00:00' AND
jn.change_date <= '09/05/2009 23:59:59'
And (jn.install_flag In ('N'))
Group By jn.branch_no,
b.branch_name,
Case When jn.install_flag='Y' Then
'Install'
Else
'Service'
End,
jn.jobgl_id,
jt.descr,
jn.billto_cust_no,
s.Sitetype_id,
s.cspart_no,
s.servarea_id,
s.site_name,
s.site_addr1,
s.site_addr2,
s.City_name,
s.state_id,
s.zip_code,
IsNull(e.last_name,'None'),
IsNull(e1.last_name,'None'),
IsNull(e2.last_name,'None'),
jn.job_no,
js.descr,
jn.change_date,
jn.Servplan_id,
--next 4 rows added by LAK 9/19/07
IsNull(jr.descr,'Install'),
IsNull(jn.cs_no,'None'),
syst.descr,
s.site_no,
IsNull(jn.udf1,' '),
IsNull(jn.udf2,' '),
IsNull(jn.udf4,' ')
,SWR.WIPAmount
,jn.comment
,ct.Descr