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
Post a Comment