LAG and ORDER BY

[SARAH GEORGE]

Hi,

Can anyone tell me why this script cannot be represented in the grid or diagram panes?


SELECT *
FROM ( SELECT
v_rentregister_bi.[Rentregister Id] AS RentRegisterID,
v_rentregister_bi.[Tenant Ref] AS TenantRef,
v_rentregister_bi.Narrative,
v_rentregister_bi.[Start Date] AS StartDate,
v_rentregister_bi.[End Date] AS EndDate,
v_rentregister_bi.[Current],
v_rentregister_bi.Amount,
LAG(v_rentregister_bi.Amount) OVER (ORDER BY v_rentregister_bi.[Tenant Ref],v_rentregister_bi.[Start Date]) AS PreviousAmount,
LAG(v_rentregister_bi.[Tenant Ref]) OVER (ORDER BY v_rentregister_bi.[Tenant Ref],v_rentregister_bi.[Start Date]) AS PreviousTenantRef

FROM OPENQUERY(propcodr, 'select `Rentregister Id`, `Tenant Ref`, Narrative, `Start Date`, `End Date`, Amount, `Current` from v_rentregister_bi
WHERE `Tenant Ref` IN ("TestA1") OR `Tenant Ref` IN ("P236839A1") OR `Tenant Ref` IN ("TestB1") OR `Tenant Ref` IN ("TestC1")') AS v_rentregister_bi )
AS Subquery
WHERE Amount <> PreviousAmount AND StartDate > '2021-10-31' AND TenantRef = PreviousTenantRef