2008 alternative to LAG?

1

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)

Many thanks for any nudges.

2

You can use an OUTER APPLY like shown below:

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);
1 Like
3

Thank you for the speedy reply. Your query returns an error about an ambiguous column name ([Count]).

Whichever letter (a, b or c) I put in front of it, all of the records for Count are zero (as if it's doing 1 - 1 for everything).

Any ideas? Apart from that, it works perfectly.

4

Sorry about that - too early in the morning on a Monday :slight_smile:

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);
1 Like
5

Brilliant, many thanks :grinning:

I had to add

AND c.TransactionDate >= CONVERT (CHAR (8), DATEADD (DD, -7, GETDATE()), 112)
AND c.TransactionDate < CONVERT (CHAR (8), GETDATE(), 112)

inside the outer apply piece of code (??) but it works beautifully.

Take a bow Sir! I'd never even heard of OUTER APPLY before your post!!