How about we at least see this in more readable form:

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],

------------------------ Start of Contract Price --------------------------------------

Case

When isNull(SWR.WIPAmount,0) < 0

Then (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) - --(Minus)

------------------------ End of Contract Price ----------------------------------------

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

------------------------ End of Progress Billing --------------------------------------

abs(isNull(SWR.WIPAmount,0))))

------------------------ Start of Contract Price --------------------------------------

Else (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) - --(Minus)

------------------------ End of Contract Price ----------------------------------------

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

Steve

(aka smunson)

:-):-):-)