Select rows based on other rows in the same table

Question

I have come up with a very simple example to demonstrate the problem I'm trying to solve.

I need to select customer contracts that...

1) have expired or will expire in the next three months

AND

2) that do not have a new contract already in place.
All Expired contracts remain in the table along with the new ones.

Based on these business rules my expected results from the test data would be to return contract ID 6 (customer 3) as it is an expired contract with no new contract and contract ID 7 (customer 4) as it has less than 3 months to run.

I have looked at some examples where the solution is to join the table to itself

e.g. how do I query sql for a latest record date for each user

I think I could select only the most recent contract for each customer then check its expiry date like this but it only returns Contract ID 6 and not 7 like I'm expecting. I'm using SQL 2008 R2.

Any Ideas where I'm going wrong?

SELECT [ContractID]
      ,[StartDate]
      ,[ExpiryDate]
      ,TC.[CustomerID]
  FROM [Test].[dbo].[TestContract] TC
  inner join
  (
    select CustomerID,
    MAX(ExpiryDate) as MaxDate
    From Test.dbo.TestContract
    Group by CustomerID     
  )CM on TC.CustomerID = CM.CustomerID and TC.ExpiryDate = CM.MaxDate
  Where TC.ExpiryDate < DateAdd(DAY, 30, GETDATE())

Here is my test Data

ContractID  StartDate                 ExpiryDate                CustomerID
1           2017-02-01 00:00:00.000   2018-02-01 00:00:00.000   1
2           2016-01-01 00:00:00.000   2017-01-01 00:00:00.000   1
4           2016-01-01 00:00:00.000   2017-11-01 00:00:00.000   2
5           2017-11-01 00:00:00.000   2018-11-01 00:00:00.000   2
6           2016-10-01 00:00:00.000   2017-10-01 00:00:00.000   3
7           2016-12-01 00:00:00.000   2017-12-01 00:00:00.000   4
8           2015-12-01 00:00:00.000   2016-12-01 00:00:00.000   4
9           2017-06-01 00:00:00.000   2018-06-01 00:00:00.000   5

Here is a script to recreate my test table and data.

USE [Test]
GO
/****** Object:  Table [dbo].[TestContract]    Script Date: 10/05/2017 17:07:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestContract](
    [ContractID] [int] IDENTITY(1,1) NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [ExpiryDate] [datetime] NOT NULL,
    [CustomerID] [int] NOT NULL,
 CONSTRAINT [PK_TestContract] PRIMARY KEY CLUSTERED 
(
    [ContractID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TestContract] ON
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (1, CAST(0x0000A70D00000000 AS DateTime), CAST(0x0000A87A00000000 AS DateTime), 1)
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (2, CAST(0x0000A58000000000 AS DateTime), CAST(0x0000A6EE00000000 AS DateTime), 1)
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (4, CAST(0x0000A58000000000 AS DateTime), CAST(0x0000A81E00000000 AS DateTime), 2)
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (5, CAST(0x0000A81E00000000 AS DateTime), CAST(0x0000A98B00000000 AS DateTime), 2)
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (6, CAST(0x0000A69200000000 AS DateTime), CAST(0x0000A7FF00000000 AS DateTime), 3)
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (7, CAST(0x0000A6CF00000000 AS DateTime), CAST(0x0000A83C00000000 AS DateTime), 4)
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (8, CAST(0x0000A56100000000 AS DateTime), CAST(0x0000A6CF00000000 AS DateTime), 4)
INSERT [dbo].[TestContract] ([ContractID], [StartDate], [ExpiryDate], [CustomerID]) VALUES (9, CAST(0x0000A78500000000 AS DateTime), CAST(0x0000A8F200000000 AS DateTime), 5)
SET IDENTITY_INSERT [dbo].[TestContract] OFF

@TheGameiswar It is written there in the question :) ID 6 and 7 — David P, Oct 05 '17 at 10:38
Anagha picked my stupid mistake. The 30 should have been a 90. I'll go and test it with my real data now which is obviously going to be way more complicated. Thanks to everyone for your proposed solutions. I only need one that works :) I'll see if I can understand what you are describing as I'm sure there are great learning opportunities for me there. . — David P, Oct 05 '17 at 10:47

score 2 · Accepted Answer · answered Oct 05 '17 at 07:19

You said "expired or will expire in the next three months" means there should be condition like DateAdd(DAY, 90, GETDATE()) instead of 30

After modification in your query:

SELECT [ContractID]
      ,[StartDate]
      ,[ExpiryDate]
      ,TC.[CustomerID]
  FROM [TestContract] TC
  inner join
  (
    select CustomerID,
    MAX(ExpiryDate) as MaxDate
    From TestContract
    Group by CustomerID     
  )CM on TC.CustomerID = CM.CustomerID and TC.ExpiryDate = CM.MaxDate
  Where TC.ExpiryDate <  DateAdd(DAY, 90, GETDATE())

one alternative :

select [ContractID],[StartDate],[ExpiryDate] ,[CustomerID]
from (select [ContractID],[StartDate],ExpiryDate ,TC.[CustomerID],
      ROW_NUMBER() over (partition by customerid order by ExpiryDate desc) rn
FROM [TestContract] TC ) a
where rn =1 
and ExpiryDate <  DateAdd(DAY, 90, GETDATE())

You picked it :) I can't believe I missed it. I was so convinced my solution wasn't going to work I didn't even look for stupid mistakes. I simply changed my 30 to a 90 and I get the expected result. — David P, Oct 05 '17 at 10:42

Jeff Williams · Answer 2 · 2017-10-05T09:33:10.770

The way I understand the problem, windowing functions like ROW_NUMBER() shouldn't be neccessary.

Using the join approach...

SELECT
    TC.ContractID
  , TC.StartDate
  , TC.ExpiryDate
  , TC.CustomerID
FROM dbo.TestContract TC
LEFT JOIN dbo.TestContract TC2
    ON TC2.CustomerID = TC.CustomerID
    AND TC2.StartDate > TC.StartDate
WHERE
    TC.ExpiryDate < dateadd(day, 90, getdate())
    AND TC2.ContractID is null

However, since we don't need any columns from the joined table in the select clause, I would instead opt for using NOT EXISTS. Though neither are very complicated, this is easier for me to reason about because when read it more closely resembles the business rules, and no thought is required to determine if the join would produce additional rows...

SELECT
    TC.ContractID
  , TC.StartDate
  , TC.ExpiryDate
  , TC.CustomerID
FROM dbo.TestContract TC
WHERE
    TC.ExpiryDate < dateadd(day, 90, getdate())
    AND NOT EXISTS (
        SELECT *
        FROM dbo.TestContract TC2
        WHERE
            TC2.CustomerID = TC.CustomerID
            AND TC2.StartDate > TC.StartDate
        )

When you have inner joined tables which are only used in their join condition, or outer joined tables which are only used in their own join condition and in the where clause, you can often re-write the query with EXISTS or NOT EXISTS.

Thanks Jeff, Both great answers and simpler than the one I came up with. I think I will try and implement your second answer because there is no need to include an outer join. — David P, Oct 06 '17 at 00:06
You're welcome, thanks for coming back to comment. Pretty sure these solutions will perform better than the row number approach. Though, if you dont have much data it may only be noticeable when looking at the query plan. — Jeff Williams, Oct 07 '17 at 01:27

Jibin Balachandran · Answer 3 · 2017-10-05T07:26:41.500

1

Use ROW_NUMBER() in a subquery to get the latest contract for every customer and then check for expirydate

SELECT ContractID,StartDate,ExpiryDate,CustomerID
FROM (
       SELECT ContractID,StartDate,ExpiryDate,CustomerID,
              ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY ExpiryDate DESC) AS RN
       FROM [YourTable]
) X
WHERE X.RN=1 AND X.ExpiryDate < DateAdd(DAY, 90, GETDATE())

edited Oct 05 '17 at 07:26

answered Oct 05 '17 at 07:08

Jibin Balachandran

3,381
1
24
38

1

Thanks, That also works but my original solution was very close to working all be it not very pretty. I gave tick to the person who spotted my error first. I'll see which is easier to implement. – David P Oct 05 '17 at 10:54

score 0 · Answer 4 · answered Oct 05 '17 at 08:14

SELECT ContractID,StartDate,ExpiryDate,CustomerID
FROM (
       SELECT ContractID,StartDate,ExpiryDate,CustomerID,
              ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY ExpiryDate DESC) AS RN
       FROM TestContract
) a
WHERE a.RN=1 AND ( a.ExpiryDate < DateAdd(DAY, 30, GETDATE()) OR a.ExpiryDate <= DateAdd(M, 3, GETDATE()) )

Select rows based on other rows in the same table

4 Answers4