common table expression - SQL Server - CTE causing 'multi-part identifier could not be bound' -


i trying return previous values of rows. before this, using lag/lead, looking improve performance. anywho, error multi-part identifier not bound on [dbo].[backfill].[rownum] part. i've looked @ other similar questions here on s.o., couldn't resolve issue.

with updated ( select g.[rownum] 'rownum'     ,g.[meter#] 'prevmeter#'     ,g.[startdate] 'prevstartdate'     ,g.[enddate] 'prevenddate'     ,g.[usageamt] 'prevusageamt'     ,g.[readingtype] 'prevreadtype' (     select top 1 *     [dbo].[backfill] z     z.rownum < [dbo].[backfill].[rownum]     order rownum desc     ) g ) 

however, if run

with updated (  select  [rownum],                    (select [meter#] (select top 1 * dbo.backfill z z.rownum < dbo.backfill.rownum order rownum desc) g) 'prevmeter#',         (select [startdate] (select top 1 * dbo.backfill z z.rownum < dbo.backfill.rownum order rownum desc) g) 'prevstartdate',         (select [enddate] (select top 1 * dbo.backfill z z.rownum < dbo.backfill.rownum order rownum desc) g) 'prevenddate',            (select [usageamt] (select top 1 * dbo.backfill z z.rownum < dbo.backfill.rownum order rownum desc) g) 'prevusageamt',              (select [readingtype] (select top 1 * dbo.backfill z z.rownum < dbo.backfill.rownum order rownum desc) g) 'prevreadtype'         dbo.backfill ) 

it works fine. can point me in right direction?

are trying join same table? if you're missing join.

with updated ( select g.[rownum] 'rownum'     ,g.[meter#] 'prevmeter#'     ,g.[startdate] 'prevstartdate'     ,g.[enddate] 'prevenddate'     ,g.[usageamt] 'prevusageamt'     ,g.[readingtype] 'prevreadtype' (     select top 1 z.*     [dbo].[backfill] z     inner join [dbo].[backfill] x on z.? = x.?     z.rownum < x.[rownum]     order z.rownum desc     ) g ) 

Comments