CTE for LEAD function not quite working

Question

Post reply

CTE for LEAD function not quite working

smw147

SSCommitted

Points: 1544
More actions
July 19, 2016 at 4:42 am

#328544

Firtly, sorry about the title, not sure how to describe the issue 🙂
I have this code which works well for SQL 2012 databases and above:
SELECT
PremiumRef
, PolicyRef
, Amount
, PolicyPremiumDate AS [StartDate]
, DATEADD(dd, -1, LEAD(PolicyPremiumDate) OVER(PARTITION BY PolicyRef ORDER BY PolicyPremiumDate)) AS [EndDate]
FROM
PolicyPremiums
WHERE
PremType = 2
ORDER BY PolicyRef, StartDate
The table doesn't have end dates so I'm getting the end date by using the start date from the next row. The results are as follows:
|------------|-----------|--------|------------|------------|
| PremiumRef | PolicyRef | Amount | StartDate | EndDate |
|------------|-----------|--------|------------|------------|
| 38000032 | 1000272 | 100.00 | 2010-07-01 | 2014-12-31 |
|------------|-----------|--------|------------|------------|
| 38000034 | 1000272 | 222.00 | 2015-01-01 | NULL |
|------------|-----------|--------|------------|------------|
| 1976000010 | 2000308 | 1.00 | 2015-02-01 | 2015-12-20 |
|------------|-----------|--------|------------|------------|
| 1976000002 | 2000308 | 2.00 | 2015-12-21 | 2016-01-23 |
|------------|-----------|--------|------------|------------|
| 1976000007 | 2000308 | 4.00 | 2016-01-24 | 2016-01-29 |
|------------|-----------|--------|------------|------------|
| 1976000009 | 2000308 | 5.00 | 2016-01-30 | 2016-01-31 |
|------------|-----------|--------|------------|------------|
| 1976000008 | 2000308 | 6.00 | 2016-02-01 | NULL |
|------------|-----------|--------|------------|------------|
As expected, the last row doesn't have an End Date for each PolicyRef.
I'm trying to make this work with SQL 2008R2, but it's not quite working as it puts an end date on the last record for each PolicyRef.
Here's my code:
WITH Prems
AS
(
SELECT
PremiumRef
, PolicyRef
, PolicyPremiumDate
, Amount
, ROW_NUMBER() OVER (ORDER BY PolicyPremiumDate) AS rn
FROM PolicyPremiums
WHERE PremType = 2
)
SELECT
a.PremiumRef
, a.PolicyRef
, a.Amount
, a.PolicyPremiumDate AS [StartDate]
, DATEADD(dd, -1, b.PolicyPremiumDate) AS [EndDate]
FROM
Prems a LEFT JOIN Prems b ON a.rn = b.rn - 1
ORDER BY
a.PolicyRef, [StartDate]
The results are as below. It seems to be ignoring the fact the policyref has changed on the next line.
|------------|-----------|--------|------------|------------|
| PremiumRef | PolicyRef | Amount | StartDate | EndDate |
|------------|-----------|--------|------------|------------|
| 38000032 | 1000272 | 100.00 | 2010-07-01 | 2014-12-31 |
|------------|-----------|--------|------------|------------|
| 38000034 | 1000272 | 222.00 | 2015-01-01 | 2015-01-31 |
|------------|-----------|--------|------------|------------|
| 1976000010 | 2000308 | 1.00 | 2015-02-01 | 2015-12-20 |
|------------|-----------|--------|------------|------------|
| 1976000002 | 2000308 | 2.00 | 2015-12-21 | 2016-01-23 |
|------------|-----------|--------|------------|------------|
| 1976000007 | 2000308 | 4.00 | 2016-01-24 | 2016-01-29 |
|------------|-----------|--------|------------|------------|
| 1976000009 | 2000308 | 5.00 | 2016-01-30 | 2016-01-31 |
|------------|-----------|--------|------------|------------|
| 1976000008 | 2000308 | 6.00 | 2016-02-01 | NULL |
|------------|-----------|--------|------------|------------|
Here's some DDL to assist
CREATE TABLE [dbo].[PolicyPremiums](
[PremiumRef] [int] NOT NULL,
[PolicyRef] [int] NULL,
[PolicyPremiumDate] [datetime] NULL,
[PremType] [smallint] NULL,
[Amount] [money] NULL
)
INSERT INTO [dbo].[PolicyPremiums]
([PremiumRef]
,[PolicyRef]
,[PolicyPremiumDate]
,[PremType]
,[Amount])
VALUES
(38000032, 1000272, '2010-07-01', 2, 100.00)
, (38000034, 1000272, '2015-01-01', 2, 222.00)
, (1976000010, 2000308, '2015-02-01', 2, 1.00)
, (1976000002, 2000308, '2015-12-21', 2, 2.00)
, (1976000007, 2000308, '2016-01-24', 2, 4.00)
, (1976000009, 2000308, '2016-01-30', 2, 5.00)
, (1976000008, 2000308, '2016-02-01', 2, 6.00)
I tried adding a PARTITION BY PolicyRef into my ROW_NUMBER() in the CTE to make sure it deals with each group of policyref seperately, but that didn't work and just seems to create duplicate rows.
Is anybody able to assist? I know I'm missing something, but can't quite think what. Many thanks in advance
Regards
Steve
Regards
Steve

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Steve

You're right about PARTITION BY, but you just need also to add PolicyRef to the join predicate:WITH Prems

AS

(

SELECT

PremiumRef

, PolicyRef

, PolicyPremiumDate

, Amount

, ROW_NUMBER() OVER (PARTITION BY PolicyRef ORDER BY PolicyPremiumDate) AS rn

FROM PolicyPremiums

WHERE PremType = 2

)

SELECT

a.PremiumRef

, a.PolicyRef

, a.Amount

, a.PolicyPremiumDate AS [StartDate]

, DATEADD(dd, -1, b.PolicyPremiumDate) AS [EndDate]

FROM

Prems a LEFT JOIN Prems b ON a.rn = b.rn - 1 AND a.PolicyRef = b.PolicyRef

ORDER BY

a.PolicyRef, [StartDate]

John

smw147 SSCommitted Points: 1544 More actions · Answer 2

I knew I was missing something. So obvious now :rolleyes:

Many thanks John, much appreciated 😀

Regards

Steve