Select IV.item, IV.meter, isnull(wt.name,0)as name,  case when whh.meterstop is null then 0 end meterstop, whh.rejected, Case when cast(meterstop as int)  > 0 then cast(meterstop as int) when meterstop is null then isnull(IV.meter,0) else isnull(IV.meter,0) end EndMeter, ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) as LastFreq,
case when whh.rejected = 1 then ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 0 then 100 when ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0) = 100 then 500 else ISNULL(CAST(SUBSTRING(wt.name,1,4)as int),0)+500 end NextFreq

From INVENTORY IV Left Outer Join
  (Select worksorderhdr.itemcode, Max(worksorderhdr.worktype) WorkType,
      Max(worksorderhdr.date_created) LastService, Max(worksorderhdr.worknumber)
      DN
    From worksorderhdr
    Where worksorderhdr.worktype In (10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
      21, 22, 32)
    Group By worksorderhdr.itemcode) whi On whi.itemcode = IV.item
  Left Outer Join
  worksordertypes wt On whi.WorkType = wt.id Left Outer Join
  worksorderhdr whh On whi.DN = whh.worknumber Left Outer Join
  sched_maintenance crt On crt.code = IV.item And crt.description Like 'CERT%'
    And crt.next_calendar_date Is Not Null Left Outer Join
  (Select sm.code, Max(Case
        When sm.last_maint Like '[0-9]%' Then Abs(sm.last_maint) Else 0
      End) LastM, Max(sm.description) Descript
    From sched_maintenance sm
    Where sm.description Like 'Z%'
    Group By sm.code
    Having Max(sm.last_maint) Is Not Null And Max(sm.last_maint) Not Like 'DN%')
    lm On lm.code = IV.item Left Outer Join
  (Select Max(dh.sname) Sname, Max(dh.saddr1) Saddr1, Max(dh.saddr2) Saddr2,
      Max(dh.scity) Town, Max(dh.szip) SPost, di.item, dh.dticket Contract
    From deltickitem di Inner Join
      deltickhdr dh On di.dticket = dh.dticket
    Where di.stage = 4
    Group By di.item, dh.dticket) sd On sd.item = IV.item And
    IV.itemised_status In (32, 64) Left Join
  inventory IR On IV.item = IR.mfg
Where IV.itemised_status > 15 and (IR.descr is null or IR.descr like 'Appell%')
Order By IV.item 

Msg 207, Level 16, State 1, Line 3
Invalid column name 'NextFreq'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'NextFreq'.