SQL Server da Kesişen tarihleri birleştirmek

Aymira Yazılım Bilişim Aymira Yazılım Bilişim

05 Şubat 2019 Veri Tabanı Y.Sis. (18) /Access (6) (141)

Paylaşmak güzeldir. Lütfen sizde paylaşın...


SQL Server da Kesişen tarihleri birleştirerek tek zaman olarak almak için ;

 

ALTER VIEW [dbo].[V_UcustakiPersonel_Vardiyadaki_UcusBilgileri]
AS SELECT UcusEkibiPersonelId, 
			MIN(EtkiUcusBaslangicZamani)  AS EtkiUcusBaslangicZamani,
			EtkiUcusBitisZamani, 
		
			DATEDIFF(MINUTE, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) AS ToplamDakika,
			DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) AS ToplamSaniye,
			DATEDIFF(HOUR, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) AS ToplamSaat,
			
		    DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) / 86400  as  Gün ,
		   (DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) % 86400 ) / 3600  as Saat ,
		   ((DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) % 86400 ) % 3600 ) / 60 as Dakika , 
		   ((DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) % 86400 ) % 3600 ) % 60  as Saniye,

			CONVERT(VARCHAR(10), ( DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) / 86400 )) + ' Gün '
			+ CONVERT(VARCHAR(10), ( ( DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) % 86400 ) / 3600 )) + ' Saat '
			+ CONVERT(VARCHAR(10), ( ( ( DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) % 86400 ) % 3600 ) / 60 ))+ ' Dakika ' 
			+ CONVERT(VARCHAR(10), ( ( ( DATEDIFF(SECOND, MIN(EtkiUcusBaslangicZamani), EtkiUcusBitisZamani) % 86400 ) % 3600 ) % 60 ))+ ' Saniye' 
			 AS GunSaatDakikaSaniye

FROM    (SELECT  x.UcusEkibiPersonelId, x.EtkiUcusBaslangicZamani, MIN(y.EtkiUcusBitisZamani) AS EtkiUcusBitisZamani
                          FROM  dbo.UcustakiPersonelBilgileri AS x INNER JOIN
                                dbo.UcustakiPersonelBilgileri AS y ON x.UcusEkibiPersonelId = y.UcusEkibiPersonelId AND x.EtkiUcusBaslangicZamani <= y.EtkiUcusBitisZamani AND NOT EXISTS
                                (SELECT 1 AS Expr1
                                FROM dbo.UcustakiPersonelBilgileri AS z
                                WHERE (y.UcusEkibiPersonelId = UcusEkibiPersonelId) AND (y.EtkiUcusBitisZamani >= EtkiUcusBaslangicZamani) AND (y.EtkiUcusBitisZamani < EtkiUcusBitisZamani))
                          WHERE (NOT EXISTS 
                                 (SELECT 1 AS Expr1 FROM dbo.UcustakiPersonelBilgileri AS u
                                  WHERE (x.UcusEkibiPersonelId = UcusEkibiPersonelId) 
								  AND (x.EtkiUcusBaslangicZamani > EtkiUcusBaslangicZamani) 
								  AND (x.EtkiUcusBaslangicZamani <= EtkiUcusBaslangicZamani)))
                          GROUP BY x.UcusEkibiPersonelId, x.EtkiUcusBaslangicZamani) AS v
GROUP BY UcusEkibiPersonelId, EtkiUcusBitisZamani

MySQL Örneği için: https://rextester.com/EIJOI20983 linki takip edin..

 

LinqTo SQL için ise araştırmarımda şöye bir kaynak buldum denemek isteyebilirsiniz:

Önce bir oyun alanı oluşturalım:

CREATE TABLE [dbo].[dateTable](
    [dt_from] [datetime2](7) NOT NULL,
    [dt_to] [datetime2](7) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[dateTable]
           ([dt_from]
           ,[dt_to])
     VALUES 
('2012-07-21 02:00:00', '2012-07-21 04:00:00'),
('2012-07-21 03:00:00', '2012-07-21 10:00:00'),
('2012-07-21 06:00:00', '2012-07-21 17:00:00'),
('2012-07-21 18:00:00', '2012-07-21 19:00:00')
GO
Bir sürü aramız var ve onları birleştirmek istiyoruz. Birleştirilmiş aralıkların başlangıç ​​ve bitiş noktalarını bulmamız gerekir. Birleştirilmiş bir başlangıç ​​bu kısıtlamaları yerine getirmelidir:

Bazı değişmemiş aralığın başlangıcıdır.
Bu zamanda veya daha sonra biten ve bu saatten daha erken başlayan böyle bir aralık yoktur.
Aynı anda daha fazla aralık başlarsa, bu süreyi yalnızca bir kez alırız.
Birleştirilmiş bir uç ise similerdir. Her bir birleştirilmiş başlangıç ​​için tam olarak bir birleştirilmiş uç vardır. Tek sorun, başlangıcın sona ermesinden büyük olduğu zaman aralıksız olabilir. Onları geçersiz olarak atlamaya karar verdim. Ve kod burada

var ed = new DataClasses1DataContext();

var res = ed.dateTables
    .Where(d => d.dt_from <= d.dt_to) //change to < for nonzero intervals only
    .Select(f => f.dt_from)
    .Distinct()
    .Where(w => !ed.dateTables.Any(a => a.dt_from < w && a.dt_to >= w))                
    .Select(date => new
    {
        FROM = date,
        TO = ed.dateTables.Select(t => t.dt_to)
        .Where(w => !ed.dateTables.Any(a => a.dt_from <= w && a.dt_to > w) && w >= date)
        .Min()
    }).ToArray();

foreach (var r in res)
{
    Console.WriteLine("FROM: " + r.FROM + " TO: " + r.TO);
}
ve burada sonuç:

FROM: 21.07.2012 2:00:00 TO: 21.07.2012 17:00:00 
FROM: 21.07.2012 18:00:00 TO: 21.07.2012 19:00:00
oluşturulan SQL sürpriz değil, sadece biraz dağınık

SELECT [t1].[dt_from] AS [FROM], (
    SELECT MIN([t3].[dt_to])
    FROM [dbo].[dateTable] AS [t3]
    WHERE (NOT (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[dateTable] AS [t4]
        WHERE ([t4].[dt_from] <= [t3].[dt_to]) AND ([t4].[dt_to] > [t3].[dt_to])
        ))) AND ([t3].[dt_to] >= [t1].[dt_from])
    ) AS [TO]
FROM (
    SELECT DISTINCT [t0].[dt_from]
    FROM [dbo].[dateTable] AS [t0]
    WHERE [t0].[dt_from] <= [t0].[dt_to]
    ) AS [t1]
WHERE NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[dateTable] AS [t2]
    WHERE ([t2].[dt_from] < [t1].[dt_from]) AND ([t2].[dt_to] >= [t1].[dt_from])
    ))


Paylaşmak güzeldir. Lütfen sizde paylaşın...



Herkesyazar Ara