Sélectionnez des lignes sans chevauchement de plages de dates

Solution pour dbo.Loan avec PRIMARY KEY

Pour résoudre cela, vous avez besoin d'une approche en deux étapes, comme indiqué dans le SQL Fiddle suivant . J'ai ajouté une colonne LoanId à vos données d'exemple et la requête nécessite qu'un tel identifiant unique existe. Si vous ne l'avez pas, vous devez ajuster la clause join pour vous assurer qu'un prêt ne correspond pas à lui-même.

Configuration du schéma MS SQL Server 2008 :

 CREATE TABLE dbo.Loans (LoanID INT, [BorrowerID] int, [StartDate] datetime, [DueDate] datetime) GO INSERT INTO dbo.Loans (LoanID, [BorrowerID], [StartDate], [DueDate]) VALUES (1, 1, '2012-09-02 00:00:00', '2012-10-01 00:00:00'), (2, 2, '2012-10-05 00:00:00', '2012-10-21 00:00:00'), (3, 3, '2012-11-07 00:00:00', '2012-11-09 00:00:00'), (4, 4, '2012-12-01 00:00:00', '2013-01-01 00:00:00'), (5, 4, '2012-12-01 00:00:00', '2013-01-14 00:00:00'), (6, 1, '2012-12-20 00:00:00', '2013-01-06 00:00:00'), (7, 3, '2013-01-07 00:00:00', '2013-01-22 00:00:00'), (8, 3, '2013-01-15 00:00:00', '2013-01-18 00:00:00'), (9, 1, '2013-02-20 00:00:00', '2013-02-24 00:00:00') GO 

Vous devez d'abord savoir quels prêts se chevauchent avec un autre prêt. La requête utilise <= pour comparer les dates de début et d'échéance. Cela count les prêts où le deuxième commence le même jour que le premier se termine comme chevauchement. Si vous avez besoin que ceux-ci ne se chevauchent pas, utilisez plutôt < aux deux endroits.

Requête 1 :

 SELECT *, CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 WHERE L2.BorrowerID = L1.BorrowerID AND L2.LoanID <> L1.LoanID AND L1.StartDate <= L2.DueDate AND L2.StartDate <= l1.DueDate) THEN 1 ELSE 0 END AS HasOverlappingLoan FROM dbo.Loans L1; 

Résultats :

 | LOANID | BORROWERID | STARTDATE | DUEDATE | HASOVERLAPPINGLOAN | |--------|------------|----------------------------------|---------------------------------|--------------------| | 1 | 1 | September, 02 2012 00:00:00+0000 | October, 01 2012 00:00:00+0000 | 0 | | 2 | 2 | October, 05 2012 00:00:00+0000 | October, 21 2012 00:00:00+0000 | 0 | | 3 | 3 | November, 07 2012 00:00:00+0000 | November, 09 2012 00:00:00+0000 | 0 | | 4 | 4 | December, 01 2012 00:00:00+0000 | January, 01 2013 00:00:00+0000 | 1 | | 5 | 4 | December, 01 2012 00:00:00+0000 | January, 14 2013 00:00:00+0000 | 1 | | 6 | 1 | December, 20 2012 00:00:00+0000 | January, 06 2013 00:00:00+0000 | 0 | | 7 | 3 | January, 07 2013 00:00:00+0000 | January, 22 2013 00:00:00+0000 | 1 | | 8 | 3 | January, 15 2013 00:00:00+0000 | January, 18 2013 00:00:00+0000 | 1 | | 9 | 1 | February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 | 0 | 

Maintenant, avec cette information, vous pouvez déterminer les emprunteurs qui n'ont pas de prêts qui se chevauchent avec cette requête:

Requête 2 :

 WITH OverlappingLoans AS ( SELECT *, CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 WHERE L2.BorrowerID = L1.BorrowerID AND L2.LoanID <> L1.LoanID AND L1.StartDate <= L2.DueDate AND L2.StartDate <= l1.DueDate) THEN 1 ELSE 0 END AS HasOverlappingLoan FROM dbo.Loans L1 ), OverlappingBorrower AS ( SELECT BorrowerID, MAX(HasOverlappingLoan) HasOverlappingLoan FROM OverlappingLoans GROUP BY BorrowerID ) SELECT * FROM OverlappingBorrower WHERE hasOverlappingLoan = 0; 

Ou vous pouvez même get plus d'informations en comptant les prêts ainsi que de countr le nombre de prêts qui ont chevauchement d'autres prêts pour chaque emprunteur dans la database. (Remarque: si le prêt A et le prêt B se chevauchent, les deux seront considérés comme un prêt qui se chevauche par cette requête)

Résultats :

 | BORROWERID | HASOVERLAPPINGLOAN | |------------|--------------------| | 1 | 0 | | 2 | 0 | 

Requête 3 :

 WITH OverlappingLoans AS ( SELECT *, CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 WHERE L2.BorrowerID = L1.BorrowerID AND L2.LoanID <> L1.LoanID AND L1.StartDate <= L2.DueDate AND L2.StartDate <= l1.DueDate) THEN 1 ELSE 0 END AS HasOverlappingLoan FROM dbo.Loans L1 ) SELECT BorrowerID,COUNT(1) LoanCount, SUM(hasOverlappingLoan) OverlappingCount FROM OverlappingLoans GROUP BY BorrowerID; 

Résultats :

 | BORROWERID | LOANCOUNT | OVERLAPPINGCOUNT | |------------|-----------|------------------| | 1 | 3 | 0 | | 2 | 1 | 0 | | 3 | 3 | 2 | | 4 | 2 | 2 | 

---

---

Solution pour dbo.Loan sans key PRIMARY

MISE À JOUR: Comme l'exigence fait appel à une solution qui ne repose pas sur un identifiant unique pour chaque prêt, j'ai apporté les modifications suivantes:

1) J'ai ajouté un emprunteur qui a deux prêts avec les mêmes dates de début et d'échéance

SQL Fiddle

Configuration du schéma MS SQL Server 2008 :

 CREATE TABLE dbo.Loans ([BorrowerID] int, [StartDate] datetime, [DueDate] datetime) GO INSERT INTO dbo.Loans ([BorrowerID], [StartDate], [DueDate]) VALUES ( 1, '2012-09-02 00:00:00', '2012-10-01 00:00:00'), ( 2, '2012-10-05 00:00:00', '2012-10-21 00:00:00'), ( 3, '2012-11-07 00:00:00', '2012-11-09 00:00:00'), ( 4, '2012-12-01 00:00:00', '2013-01-01 00:00:00'), ( 4, '2012-12-01 00:00:00', '2013-01-14 00:00:00'), ( 1, '2012-12-20 00:00:00', '2013-01-06 00:00:00'), ( 3, '2013-01-07 00:00:00', '2013-01-22 00:00:00'), ( 3, '2013-01-15 00:00:00', '2013-01-18 00:00:00'), ( 1, '2013-02-20 00:00:00', '2013-02-24 00:00:00'), ( 5, '2013-02-20 00:00:00', '2013-02-24 00:00:00'), ( 5, '2013-02-20 00:00:00', '2013-02-24 00:00:00') GO 

2) Ces prêts «à date égale» nécessitent une étape supplémentaire:

Requête 1 :

 SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount FROM dbo.Loans GROUP BY BorrowerID, StartDate, DueDate; 

Résultats :

 | BORROWERID | STARTDATE | DUEDATE | LOANCOUNT | |------------|----------------------------------|---------------------------------|-----------| | 1 | September, 02 2012 00:00:00+0000 | October, 01 2012 00:00:00+0000 | 1 | | 1 | December, 20 2012 00:00:00+0000 | January, 06 2013 00:00:00+0000 | 1 | | 1 | February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 | 1 | | 2 | October, 05 2012 00:00:00+0000 | October, 21 2012 00:00:00+0000 | 1 | | 3 | November, 07 2012 00:00:00+0000 | November, 09 2012 00:00:00+0000 | 1 | | 3 | January, 07 2013 00:00:00+0000 | January, 22 2013 00:00:00+0000 | 1 | | 3 | January, 15 2013 00:00:00+0000 | January, 18 2013 00:00:00+0000 | 1 | | 4 | December, 01 2012 00:00:00+0000 | January, 01 2013 00:00:00+0000 | 1 | | 4 | December, 01 2012 00:00:00+0000 | January, 14 2013 00:00:00+0000 | 1 | | 5 | February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 | 2 | 

3) Maintenant, avec chaque gamme de prêt unique, nous pouvons utiliser l'ancienne technique à nouveau. Cependant, nous devons également comptabiliser ces prêts «à date égale». (L1.StartDate <> L2.StartDate OR L1.DueDate <> L2.DueDate) empêche un prêt d'être mis en correspondance avec lui-même. OR LoanCount > 1 counts pour les prêts «à date égale».

Requête 2 :

 WITH NormalizedLoans AS ( SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount FROM dbo.Loans GROUP BY BorrowerID, StartDate, DueDate ) SELECT *, CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 WHERE L2.BorrowerID = L1.BorrowerID AND L1.StartDate <= L2.DueDate AND L2.StartDate <= l1.DueDate AND (L1.StartDate <> L2.StartDate OR L1.DueDate <> L2.DueDate) ) OR LoanCount > 1 THEN 1 ELSE 0 END AS HasOverlappingLoan FROM NormalizedLoans L1; 

Résultats :

 | BORROWERID | STARTDATE | DUEDATE | LOANCOUNT | HASOVERLAPPINGLOAN | |------------|----------------------------------|---------------------------------|-----------|--------------------| | 1 | September, 02 2012 00:00:00+0000 | October, 01 2012 00:00:00+0000 | 1 | 0 | | 1 | December, 20 2012 00:00:00+0000 | January, 06 2013 00:00:00+0000 | 1 | 0 | | 1 | February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 | 1 | 0 | | 2 | October, 05 2012 00:00:00+0000 | October, 21 2012 00:00:00+0000 | 1 | 0 | | 3 | November, 07 2012 00:00:00+0000 | November, 09 2012 00:00:00+0000 | 1 | 0 | | 3 | January, 07 2013 00:00:00+0000 | January, 22 2013 00:00:00+0000 | 1 | 1 | | 3 | January, 15 2013 00:00:00+0000 | January, 18 2013 00:00:00+0000 | 1 | 1 | | 4 | December, 01 2012 00:00:00+0000 | January, 01 2013 00:00:00+0000 | 1 | 1 | | 4 | December, 01 2012 00:00:00+0000 | January, 14 2013 00:00:00+0000 | 1 | 1 | | 5 | February, 20 2013 00:00:00+0000 | February, 24 2013 00:00:00+0000 | 2 | 1 | 

Cette logique de requête n'a pas changé (autre que le changement de début).

Requête 3 :

 WITH NormalizedLoans AS ( SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount FROM dbo.Loans GROUP BY BorrowerID, StartDate, DueDate ), OverlappingLoans AS ( SELECT *, CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 WHERE L2.BorrowerID = L1.BorrowerID AND L1.StartDate <= L2.DueDate AND L2.StartDate <= l1.DueDate AND (L1.StartDate <> L2.StartDate OR L1.DueDate <> L2.DueDate) ) OR LoanCount > 1 THEN 1 ELSE 0 END AS HasOverlappingLoan FROM NormalizedLoans L1 ), OverlappingBorrower AS ( SELECT BorrowerID, MAX(HasOverlappingLoan) HasOverlappingLoan FROM OverlappingLoans GROUP BY BorrowerID ) SELECT * FROM OverlappingBorrower WHERE hasOverlappingLoan = 0; 

Résultats :

 | BORROWERID | HASOVERLAPPINGLOAN | |------------|--------------------| | 1 | 0 | | 2 | 0 | 

4) Dans cette requête de comptage, nous devons à nouveau incorporer les counts de prêt «à date égale». Pour cela nous utilisons SUM(LoanCount) au lieu d'un COUNT simple. Nous devons également multiplier hasOverlappingLoan avec LoanCount pour get à nouveau le nombre de chevauchements correct.

Requête 4 :

 WITH NormalizedLoans AS ( SELECT BorrowerID, StartDate, DueDate, COUNT(1) LoanCount FROM dbo.Loans GROUP BY BorrowerID, StartDate, DueDate ), OverlappingLoans AS ( SELECT *, CASE WHEN EXISTS(SELECT 1 FROM dbo.Loans L2 WHERE L2.BorrowerID = L1.BorrowerID AND L1.StartDate <= L2.DueDate AND L2.StartDate <= l1.DueDate AND (L1.StartDate <> L2.StartDate OR L1.DueDate <> L2.DueDate) ) OR LoanCount > 1 THEN 1 ELSE 0 END AS HasOverlappingLoan FROM NormalizedLoans L1 ) SELECT BorrowerID,SUM(LoanCount) LoanCount, SUM(hasOverlappingLoan*LoanCount) OverlappingCount FROM OverlappingLoans GROUP BY BorrowerID; 

Résultats :

 | BORROWERID | LOANCOUNT | OVERLAPPINGCOUNT | |------------|-----------|------------------| | 1 | 3 | 0 | | 2 | 1 | 0 | | 3 | 3 | 2 | | 4 | 2 | 2 | | 5 | 2 | 2 | 

Je suggère fortement de find un moyen d'utiliser ma première solution, car une table de prêt sans key primaire est, disons, une design «étrange». Cependant, si vous ne pouvez vraiment pas y arriver, utilisez la deuxième solution.

Je l'ai fait fonctionner mais d'une manière un peu compliquée. Il obtient d'abord les emprunteurs qui ne répondent pas aux critères de la requête interne et renvoie le rest. La requête interne a 2 parties:

Obtenez tous les emprunts qui se chevauchent ne commencent pas le même jour.

Obtenez tous les emprunts à partir de la même date.

 select distinct BorrowerID from borrowings where BorrowerID NOT IN ( select b1.BorrowerID from borrowings b1 inner join borrowings b2 on b1.BorrowerID = b2.BorrowerID and b1.StartDate < b2.StartDate and b1.DueDate > b2.StartDate union select BorrowerID from borrowings group by BorrowerID, StartDate having count(*) > 1 ) 

J'ai dû utiliser 2 requêtes internes séparées car votre table n'a pas d'identifiant unique pour chaque logging et en utilisant b1.StartDate <= b2.StartDate comme je devrais avoir fait une jointure d'logging à elle-même. Il serait bon d'avoir un identifiant séparé pour chaque logging.

essayer

 with cte as ( select *, row_number() over (partition by b order by s) r from loans ) select l1.b from loans l1 except select c1.b from cte c1 where exists ( select 1 from cte c2 where c2.b = c1.b and c2.r <> c1.r and (c2.s between c1.s and c1.e   or c1.s between c2.s and c2.e) ) 

Si vous êtes sur SQL 2012, vous pouvez le faire comme ceci:

 with cte as ( select BorrowerID, StartDate, DueDate, lag(DueDate) over (partition by borrowerid order by StartDate, DueDate) as PrevDueDate from test ) select distinct BorrowerID from cte where BorrowerID not in (select BorrowerID from cte where StartDate <= PrevDueDate)