I've got a piece of code (helpfully supplied by a kind user here) that uses LAG on a box running 2012.
I now have to use a box running 2008 and, of course, the query fails because SSMS doesn't understand what LAG is.
What is a 2008-friendly version of the following:
SELECT TransactionDate, Insurer, [Count] - LAG ([Count], 1, 0) OVER (PARTITION BY TransClass, PolicyReference ORDER BY TransactionDate), [Value]
FROM [dbo].[MyTable]
WHERE TransClass = 'YY'
AND TransactionDate >= CONVERT (CHAR (8), DATEADD (DD, -7, GETDATE()), 112)
AND TransactionDate < CONVERT (CHAR (8), GETDATE(), 112)
SELECT TransactionDate ,
Insurer ,
[Count]-COALESCE(b.Count,0)
[Value]
FROM [dbo].[MyTable] AS a
OUTER APPLY
(
SELECT TOP (1) [Count]
FROM
[dbo].[MyTable] c
WHERE
c.TransClass = a.TransClass
AND c.PolicyReference = a.PolicyReference
ORDER BY
c.TransactionDate DESC
) AS b
WHERE TransClass = 'YY'
AND TransactionDate >= CONVERT (CHAR(8), DATEADD(DD, -7, GETDATE()), 112)
AND TransactionDate < CONVERT (CHAR(8), GETDATE(), 112);
Sorry about that - too early in the morning on a Monday
SELECT a.TransactionDate ,
a.Insurer ,
a.[Count]-COALESCE(b.[Count],0),
a.[Value]
FROM [dbo].[MyTable] AS a
OUTER APPLY
(
SELECT TOP (1) [Count]
FROM
[dbo].[MyTable] c
WHERE
c.TransClass = a.TransClass
AND c.PolicyReference = a.PolicyReference
AND c.TransactionDate < a.TransactionDate
ORDER BY
c.TransactionDate DESC
) AS b
WHERE TransClass = 'YY'
AND TransactionDate >= CONVERT (CHAR(8), DATEADD(DD, -7, GETDATE()), 112)
AND TransactionDate < CONVERT (CHAR(8), GETDATE(), 112);