SQL de Kesişen Tarih bulma

11.02.2020 2587 defa okundu

MS SQL de başlama ve bitiş tarihleri olan ve birbirini takip eden hareket listesinden oluşan kayıtlarda tarihlerin kesişmesi veya çakışması durumunda kesişen kayıtları bulmak için aşağıdaki gibi bir sorgu yazılabilir. Böylece aynı numaraya ait kayıtlardan kısmen veya tamamamen iç içe geçmiş tarihler bulunabilir.

Örnek Geçici tablo oluşturma

SELECT 1 AS ID,'001' AS NUMARA,'2020-01-01' AS BASLAMATARIHI,'2020-01-10' AS BITISTARIHI
INTO #GECICI_TABLO
UNION ALL
SELECT 2 AS ID,'001' AS NUMARA,'2020-01-11' AS BASLAMATARIHI,'2020-01-21' AS BITISTARIHI
UNION ALL
SELECT 3 AS ID,'001' AS NUMARA,'2020-01-22' AS BASLAMATARIHI,'2020-02-02' AS BITISTARIHI
UNION ALL
SELECT 4 AS ID,'001' AS NUMARA,'2020-02-02' AS BASLAMATARIHI,'2020-02-16' AS BITISTARIHI
UNION ALL
SELECT 5 AS ID,'001' AS NUMARA,'2020-01-01' AS BASLAMATARIHI,'2020-01-10' AS BITISTARIHI
UNION ALL
SELECT 6 AS ID,'002' AS NUMARA,'2019-01-01' AS BASLAMATARIHI,'2019-01-19' AS BITISTARIHI
UNION ALL
SELECT 7 AS ID,'002' AS NUMARA,'2019-01-20' AS BASLAMATARIHI,'2019-02-10' AS BITISTARIHI
UNION ALL
SELECT 8 AS ID,'002' AS NUMARA,'2019-02-17' AS BASLAMATARIHI,'2019-03-15' AS BITISTARIHI
UNION ALL
SELECT 9 AS ID,'002' AS NUMARA,'2019-03-02' AS BASLAMATARIHI,'2019-04-20' AS BITISTARIHI
UNION ALL
SELECT 10 AS ID,'003' AS NUMARA,'2019-06-06' AS BASLAMATARIHI,'2019-07-07' AS BITISTARIHI
UNION ALL
SELECT 11 AS ID,'003' AS NUMARA,'2019-07-08' AS BASLAMATARIHI,'2019-09-20' AS BITISTARIHI

Oluşturulan geçici tablo aşağıdaki gibidir.

SELECT * FROM #GECICI_TABLO
ORDER BY ID,BASLAMATARIHI

ID	NUMARA	BASLAMATARIHI	BITISTARIHI
1	1	01.01.2020	10.01.2020
2	1	11.01.2020	21.01.2020
3	1	22.01.2020	02.02.2020
4	1	02.02.2020	16.02.2020
5	1	01.01.2020	10.01.2020
6	2	01.01.2019	19.01.2019
7	2	20.01.2019	10.02.2019
8	2	17.02.2019	15.03.2019
9	2	02.03.2019	20.04.2019
10	3	06.06.2019	07.07.2019
11	3	08.07.2019	20.09.2019

Kesişen kayıtları bulan sorgu

SELECT T1.ID,T1.NUMARA,T1.BASLAMATARIHI,T1.BITISTARIHI
FROM #GECICI_TABLO AS T1
INNER JOIN #GECICI_TABLO AS T2 ON T1.NUMARA=T2.NUMARA
WHERE T1.BASLAMATARIHI<>T2.BASLAMATARIHI AND (T1.BASLAMATARIHI BETWEEN T2.BASLAMATARIHI AND T2.BITISTARIHI OR T1.BITISTARIHI BETWEEN T2.BASLAMATARIHI AND T2.BITISTARIHI)
ORDER BY T1.NUMARA,T1.ID