Zaman aralıklarında hesaplama nasıl yapılır?

Bir sorunum var, çözüyorum ama uzun bir prosedür yazdım ve tüm olası vakaları kapsadığından emin olamıyorum.

Sorun:

Bir ana aralık zamanım ( A'dan B'ye ) ve ikincil aralık zamanları (Çok fazla veya hayır)

(`From X to Y AND From X` to Y` AND X`` to  Y`` AND ....`) 

Ana aralık zamanımın (AB) tüm bölümlerini etkin ve en az sayıda koşulda dakika içinde ikincil aralıklar dışında SQL server Prosedürü ve C# yöntemi)?

Örneğin : If my Main interval From 02:00 to 10:30 And say one secondary interval From 04:00 to 08:00

Now i want this result : ((04:00 - 02:00) + (10:30 -08:00))* 60

Grafik ile örnek:

ilk durumda sonuç şöyle olacaktır:

((X-A) + (B-Y)) * 60

ve çok fazla ikincil dönemim olduğunda daha karmaşık olacak.

NOT:

Sadece ana periyodu [A, B] 'in UNION ' ı ile en fazla iki paralel ikincil aralık setinde karşılaştırmak zorunda kaldığımda meydana gelen ikincil aralıklar arasındaki örtüşme olabilir. ilk kümenin yalnızca bir ikincil aralık içermesi ve ikinci kümenin (çok fazla veya hiç) ikincil aralık içermesi gerekir. Örneğin, [A, B] ile ( kümelerini karşılaştıran grafikteki örnek) 2,5 ) ilk set (2) bir ikincil aralıktan oluşur ve ikinci set (5) üç ikincil aralıktan oluşur. ve bu en kötü durum, ele almam gerek.

Örneğin :

IF my main interval is [15:00,19:40] and i have two sets of secondary intervals .according to my rule at least one of these sets should consists of one secondary interval. say the first set is [11:00 ,16:00] and the second set is consists of say two secondary intervals [10:00,15:00],[16:30,17:45] Now i want the result (16:30 -16:00) +(19:40 -17:45)


Yorumlara göre:

Masam şöyle:

İlk tablo ikincil dönemleri, belirli bir çalışan için aynı tarihte en fazla iki ikincil dönem grubunu içerir. ilk küme, (W) [work_st, work_end] iş gününde yalnızca bir ikincil dönem içerir ve gün kümesi [ise, bu küme boş kalır E] ve bu durumda ikincil dönemler arasında çakışma olmaz. ve ikinci set, aynı tarihte [check_in, check_out] aynı tarihte birçok ikincil dönem içerebilir, çünkü çalışan aynı günde birçok kez check_in_out yapabilir.

emp_num  day_date   work_st    work_end   check_in   check_out     day_state

  547    2015-4-1   08:00       16:00     07:45      12:10           W
  547    2015-4-1   08:00       16:00     12:45      17:24           W
  547    2015-4-2   00:00       00:00     07:11      13:11           E

İkinci tablo, [A, B] ana dönemini içerir ve bu çalışan için o gün için bir dönemdir (bir kayıt)

emp_num  day_date   mission_in    mission_out
  547    2015-4-1    15:00          21:30
  547    2015-4-2    8:00           14:00

Önceki örnekte gerekirse bir prosedür veya yöntem varsa bu prosedür iki parametre almalıdır:

  • Tarih
  • emp_num

in the previous example it should be like this ('2015-4-1' ,547)

Açıklamama göre:

  • The Main period (Mission Period) [A,B] from the second table : Should be only one period in this date for that employee

    [15:00,21:30]

  • The secondary period for the passed date ('2015-4-1') for that employee was two sets of secondary periods (the worst case) from the first table

    The first set should contain only one secondary period (or zero periods) [08:00,16:00] the second set could contain many secondary periods (or zero periods)

    [07:45,12:10],[12:45,17:24]

Çıktı [17: 24,21: 30] dakikaya dönüştürülmüş olmalıdır

Not

tümü day_date, mission_in, mission_out, work_st, work_end, check_in, check_out datetime alanlarıdır, ancak basitleştirme örneğine sadece zaman koyarım, day_date dışındaki tarih bölümünü yoksaymak istiyorum, çünkü buna ek olarak hesapladığım tarih emp_num 'a.

enter image description here

18
Bunu da programlamak zorunda kaldım. Ben bir sproc mantığı koymak yerine, şiddetle programlamanızı öneririm. daha sonra çeşitli senaryoları test edebilirsiniz. eğer problemin benimkine benziyorsa, gün ışığından yararlanma, zaman dilimleri, vardiya başlangıcını mı yoksa saatin zamanını mı kullanacağınızla ilgili yasalar vb.
katma yazar Ewan, kaynak
Bunu da programlamak zorunda kaldım. Ben bir sproc mantığı koymak yerine, şiddetle programlamanızı öneririm. daha sonra çeşitli senaryoları test edebilirsiniz. eğer problemin benimkine benziyorsa, gün ışığından yararlanma, zaman dilimleri, vardiya başlangıcını mı yoksa saatin zamanını mı kullanacağınızla ilgili yasalar vb.
katma yazar Ewan, kaynak
Bunu da programlamak zorunda kaldım. Ben bir sproc mantığı koymak yerine, şiddetle programlamanızı öneririm. daha sonra çeşitli senaryoları test edebilirsiniz. eğer problemin benimkine benziyorsa, gün ışığından yararlanma, zaman dilimleri, vardiya başlangıcını mı yoksa saatin zamanını mı kullanacağınızla ilgili yasalar vb.
katma yazar Ewan, kaynak
@VladimirBaranov: sql sunucu versiyonumu etiketliyorum, grafik ve örnekler olası tüm durumları açıklıyor
katma yazar Anyname Donotcare, kaynak
@RobertMcKee: İki ikincil aralık kümesinden birinin çalışma süresi [start_time, end_time] ve ikincil aralıklara sahip ikinci küme çalışan kontrol ins_outs ve ana dönem belirli bir görev olduğunu söyleyin ve tüm bu aralıklar db'de var.
katma yazar Anyname Donotcare, kaynak
@Tanner: tarih/saat olarak saklıyorum, fakat benim davam aynı tarihte
katma yazar Anyname Donotcare, kaynak
@Tanner: tarih/saat olarak saklıyorum, fakat benim davam aynı tarihte
katma yazar Anyname Donotcare, kaynak
@Tanner: Sorumu tablo verisi örneğiyle yeniden düzenledim
katma yazar Anyname Donotcare, kaynak
@Tanner: Sorumu tablo verisi örneğiyle yeniden düzenledim
katma yazar Anyname Donotcare, kaynak
@Tanner: Sorumu tablo verisi örneğiyle yeniden düzenledim
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: sql sunucu versiyonumu etiketliyorum, grafik ve örnekler olası tüm durumları açıklıyor
katma yazar Anyname Donotcare, kaynak
@Tanner: tarih/saat olarak saklıyorum, fakat benim davam aynı tarihte
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: sql sunucu versiyonumu etiketliyorum, grafik ve örnekler olası tüm durumları açıklıyor
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: Tamam, fikri daha fazla açıklayacağım. İlk tabloda 3 satır ( 1-4 belirli tarih için iki satır ve diğer '2-4' tarihi için iki satır) ikinci tablo iki farklı tarih için iki satır içeriyor. hesaplarım belirli bir kullanıcı için tarih başına yapılmalıdır.
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: Tamam, fikri daha fazla açıklayacağım. İlk tabloda 3 satır ( 1-4 belirli tarih için iki satır ve diğer '2-4' tarihi için iki satır) ikinci tablo iki farklı tarih için iki satır içeriyor. hesaplarım belirli bir kullanıcı için tarih başına yapılmalıdır.
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: Tamam, fikri daha fazla açıklayacağım. İlk tabloda 3 satır ( 1-4 belirli tarih için iki satır ve diğer '2-4' tarihi için iki satır) ikinci tablo iki farklı tarih için iki satır içeriyor. hesaplarım belirli bir kullanıcı için tarih başına yapılmalıdır.
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: Örneği açıklığa kavuşturmak için soruyu düzenliyorum :)
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: Örneği açıklığa kavuşturmak için soruyu düzenliyorum :)
katma yazar Anyname Donotcare, kaynak
@VladimirBaranov: Örneği açıklığa kavuşturmak için soruyu düzenliyorum :)
katma yazar Anyname Donotcare, kaynak
@RobertMcKee: İki ikincil aralık kümesinden birinin çalışma süresi [start_time, end_time] ve ikincil aralıklara sahip ikinci küme çalışan kontrol ins_outs ve ana dönem belirli bir görev olduğunu söyleyin ve tüm bu aralıklar db'de var.
katma yazar Anyname Donotcare, kaynak
hayır, [A, B] ana dönemini, en çok iki paralel ikincil aralık kümesinin UNION ile karşılaştırılması gerektiğinde çakışma olabilir./code> ilk küme yalnızca bir ikincil aralık içermeli ve ikinci küme ikincil aralık (çok fazla veya hiç) içermelidir. Örneğin, [A, B] to ( 2,5 kümeleri) ilk küme (2) bir ikincil aralıktan ve ikinci küme (5) 'den oluşur. birçok ikincil aralıktan oluşur. ve bu en kötü durum, ele almam gerek
katma yazar Anyname Donotcare, kaynak
hayır, [A, B] ana dönemini, en çok iki paralel ikincil aralık kümesinin UNION ile karşılaştırılması gerektiğinde çakışma olabilir./code> ilk küme yalnızca bir ikincil aralık içermeli ve ikinci küme ikincil aralık (çok fazla veya hiç) içermelidir. Örneğin, [A, B] to ( 2,5 kümeleri) ilk küme (2) bir ikincil aralıktan ve ikinci küme (5) 'den oluşur. birçok ikincil aralıktan oluşur. ve bu en kötü durum, ele almam gerek
katma yazar Anyname Donotcare, kaynak
hayır, [A, B] ana dönemini, en çok iki paralel ikincil aralık kümesinin UNION ile karşılaştırılması gerektiğinde çakışma olabilir./code> ilk küme yalnızca bir ikincil aralık içermeli ve ikinci küme ikincil aralık (çok fazla veya hiç) içermelidir. Örneğin, [A, B] to ( 2,5 kümeleri) ilk küme (2) bir ikincil aralıktan ve ikinci küme (5) 'den oluşur. birçok ikincil aralıktan oluşur. ve bu en kötü durum, ele almam gerek
katma yazar Anyname Donotcare, kaynak
@GertArnold: evet, ancak bazı zamanlar ikincil aralıkların bölümleri 2 durumu gibi ana dönem dışındadır, bu nedenle sonuç (BY) olur veya çok sayıda ikincil aralık olur. hiçbir ikincil aralık yok
katma yazar Anyname Donotcare, kaynak
@GertArnold: evet, ancak bazı zamanlar ikincil aralıkların bölümleri 2 durumu gibi ana dönem dışındadır, bu nedenle sonuç (BY) olur veya çok sayıda ikincil aralık olur. hiçbir ikincil aralık yok
katma yazar Anyname Donotcare, kaynak
@GertArnold: evet, ancak bazı zamanlar ikincil aralıkların bölümleri 2 durumu gibi ana dönem dışındadır, bu nedenle sonuç (BY) olur veya çok sayıda ikincil aralık olur. hiçbir ikincil aralık yok
katma yazar Anyname Donotcare, kaynak
@Tanner: Sorumu bir örnekle düzenliyorum
katma yazar Anyname Donotcare, kaynak
@Tanner: Sorumu bir örnekle düzenliyorum
katma yazar Anyname Donotcare, kaynak
@RobertMcKee: İki ikincil aralık kümesinden birinin çalışma süresi [start_time, end_time] ve ikincil aralıklara sahip ikinci küme çalışan kontrol ins_outs ve ana dönem belirli bir görev olduğunu söyleyin ve tüm bu aralıklar db'de var.
katma yazar Anyname Donotcare, kaynak
@Tanner: Sorumu bir örnekle düzenliyorum
katma yazar Anyname Donotcare, kaynak
Ayrıca yalnızca saati mi, tarih ve saati mi saklıyorsunuz?
katma yazar Tanner, kaynak
Ayrıca yalnızca saati mi, tarih ve saati mi saklıyorsunuz?
katma yazar Tanner, kaynak
Ayrıca yalnızca saati mi, tarih ve saati mi saklıyorsunuz?
katma yazar Tanner, kaynak
Sorunu şimdi biraz daha iyi anlayabiliyorum, ancak verilerin nasıl depolandığını bize gösterseniz, şu anda depolanıyorsa yardımcı olur mu? Bunun çözülebileceğini düşünüyorum, ancak yanıtlayan herhangi biri verileri nasıl sakladığınıza dair varsayımlarda bulunacak. Sahte veriler içeren bazı gerçek tablo şemaları yararlı olabilir.
katma yazar Tanner, kaynak
Çıktının ne olması gerektiği ile ilgili birden çok ikincil aralık için bazı basit örnek verileri gösterebilir misiniz?
katma yazar Tanner, kaynak
Çıktının ne olması gerektiği ile ilgili birden çok ikincil aralık için bazı basit örnek verileri gösterebilir misiniz?
katma yazar Tanner, kaynak
Sorunu şimdi biraz daha iyi anlayabiliyorum, ancak verilerin nasıl depolandığını bize gösterseniz, şu anda depolanıyorsa yardımcı olur mu? Bunun çözülebileceğini düşünüyorum, ancak yanıtlayan herhangi biri verileri nasıl sakladığınıza dair varsayımlarda bulunacak. Sahte veriler içeren bazı gerçek tablo şemaları yararlı olabilir.
katma yazar Tanner, kaynak
Sorunu şimdi biraz daha iyi anlayabiliyorum, ancak verilerin nasıl depolandığını bize gösterseniz, şu anda depolanıyorsa yardımcı olur mu? Bunun çözülebileceğini düşünüyorum, ancak yanıtlayan herhangi biri verileri nasıl sakladığınıza dair varsayımlarda bulunacak. Sahte veriler içeren bazı gerçek tablo şemaları yararlı olabilir.
katma yazar Tanner, kaynak
Çıktının ne olması gerektiği ile ilgili birden çok ikincil aralık için bazı basit örnek verileri gösterebilir misiniz?
katma yazar Tanner, kaynak
İlk önce tüm sn azaltın. en azından A'dan başlayıp en çok B'den bitene kadar olan aralıklarla sanırım sn. aralıklar birbiriyle örtüşmüyor mu?
katma yazar Gert Arnold, kaynak
İlk önce tüm sn azaltın. en azından A'dan başlayıp en çok B'den bitene kadar olan aralıklarla sanırım sn. aralıklar birbiriyle örtüşmüyor mu?
katma yazar Gert Arnold, kaynak
İlk önce tüm sn azaltın. en azından A'dan başlayıp en çok B'den bitene kadar olan aralıklarla sanırım sn. aralıklar birbiriyle örtüşmüyor mu?
katma yazar Gert Arnold, kaynak
Bu sadece A-B eksi uzunluğu içerdiği tüm aralıkların uzunluğu değil, 0'dan düşük değil mi?
katma yazar Gert Arnold, kaynak
Bu sadece A-B eksi uzunluğu içerdiği tüm aralıkların uzunluğu değil, 0'dan düşük değil mi?
katma yazar Gert Arnold, kaynak
Çoklu aralıkların kaynağı nedir? Birden fazla sütuna sahip tek bir kayıt mı, yoksa her biri kendi başlangıç ​​/ bitişine sahip birden fazla kayıt mı, yoksa birden çok aralık veritabanının bir parçası değil mi?
katma yazar Robert McKee, kaynak
Çoklu aralıkların kaynağı nedir? Birden fazla sütuna sahip tek bir kayıt mı, yoksa her biri kendi başlangıç ​​/ bitişine sahip birden fazla kayıt mı, yoksa birden çok aralık veritabanının bir parçası değil mi?
katma yazar Robert McKee, kaynak
Çoklu aralıkların kaynağı nedir? Birden fazla sütuna sahip tek bir kayıt mı, yoksa her biri kendi başlangıç ​​/ bitişine sahip birden fazla kayıt mı, yoksa birden çok aralık veritabanının bir parçası değil mi?
katma yazar Robert McKee, kaynak
@just_name. Beklediğiniz sonuç, iyi bir cevap için çok önemlidir. Yalnızca bir sayı (dakikaların SUM ) hesaplanmasının, bu SUM 'a katkıda bulunan aralıkların tam bir listesini oluşturmaktan daha verimli bir şekilde yapılabilir.
katma yazar Vladimir Baranov, kaynak
@just_name. Beklediğiniz sonuç, iyi bir cevap için çok önemlidir. Yalnızca bir sayı (dakikaların SUM ) hesaplanmasının, bu SUM 'a katkıda bulunan aralıkların tam bir listesini oluşturmaktan daha verimli bir şekilde yapılabilir.
katma yazar Vladimir Baranov, kaynak
@just_name, SQL Server sürümüne sahip etiket çok iyi, fakat en önemli şey beklediğiniz sonucu eklemektir. Hangi mantığı uygulamak istediğinizi şahsen anlayamıyorum. Bu soruyu küçümsemedim, ama aynı problemi yaşayanların da olması mümkün. Table1 'da emp_num = 547 ' da 3 satır ve Table2 'da 2 satır ile aynı emp_num = 547 ' yi gösterirsiniz. Peki, sorgu bu verilerle hangi sonucu elde etmelidir? Bir satır? İki sıra mı? Üç sıra mı? Güzel görünümlü grafik gerçekten yardımcı olmuyor.
katma yazar Vladimir Baranov, kaynak
@just_name, SQL Server sürümüne sahip etiket çok iyi, fakat en önemli şey beklediğiniz sonucu eklemektir. Hangi mantığı uygulamak istediğinizi şahsen anlayamıyorum. Bu soruyu küçümsemedim, ama aynı problemi yaşayanların da olması mümkün. Table1 'da emp_num = 547 ' da 3 satır ve Table2 'da 2 satır ile aynı emp_num = 547 ' yi gösterirsiniz. Peki, sorgu bu verilerle hangi sonucu elde etmelidir? Bir satır? İki sıra mı? Üç sıra mı? Güzel görünümlü grafik gerçekten yardımcı olmuyor.
katma yazar Vladimir Baranov, kaynak
@just_name, SQL Server sürümüne sahip etiket çok iyi, fakat en önemli şey beklediğiniz sonucu eklemektir. Hangi mantığı uygulamak istediğinizi şahsen anlayamıyorum. Bu soruyu küçümsemedim, ama aynı problemi yaşayanların da olması mümkün. Table1 'da emp_num = 547 ' da 3 satır ve Table2 'da 2 satır ile aynı emp_num = 547 ' yi gösterirsiniz. Peki, sorgu bu verilerle hangi sonucu elde etmelidir? Bir satır? İki sıra mı? Üç sıra mı? Güzel görünümlü grafik gerçekten yardımcı olmuyor.
katma yazar Vladimir Baranov, kaynak
@just_name. Beklediğiniz sonuç, iyi bir cevap için çok önemlidir. Yalnızca bir sayı (dakikaların SUM ) hesaplanmasının, bu SUM 'a katkıda bulunan aralıkların tam bir listesini oluşturmaktan daha verimli bir şekilde yapılabilir.
katma yazar Vladimir Baranov, kaynak
@just_name, lütfen daha önce soruna dahil etmiş olduğunuz verilere en az 5 farklı temelde örnek ekleyin. Masalarınıza daha fazla satır ekleyin. Aralıkların üst üste gelmesinin çeşitli kombinasyonlarını dahil edin. En önemlisi, örnek verileriniz için beklenen doğru sonucu ekleyin. Bu, görevinizin mantığını anlamanıza yardımcı olacaktır. Ayrıca, kullandığınız SQL Server sürümüyle ilgili soruya bir etiket ekleyin.
katma yazar Vladimir Baranov, kaynak
@just_name, lütfen daha önce soruna dahil etmiş olduğunuz verilere en az 5 farklı temelde örnek ekleyin. Masalarınıza daha fazla satır ekleyin. Aralıkların üst üste gelmesinin çeşitli kombinasyonlarını dahil edin. En önemlisi, örnek verileriniz için beklenen doğru sonucu ekleyin. Bu, görevinizin mantığını anlamanıza yardımcı olacaktır. Ayrıca, kullandığınız SQL Server sürümüyle ilgili soruya bir etiket ekleyin.
katma yazar Vladimir Baranov, kaynak
@just_name, lütfen daha önce soruna dahil etmiş olduğunuz verilere en az 5 farklı temelde örnek ekleyin. Masalarınıza daha fazla satır ekleyin. Aralıkların üst üste gelmesinin çeşitli kombinasyonlarını dahil edin. En önemlisi, örnek verileriniz için beklenen doğru sonucu ekleyin. Bu, görevinizin mantığını anlamanıza yardımcı olacaktır. Ayrıca, kullandığınız SQL Server sürümüyle ilgili soruya bir etiket ekleyin.
katma yazar Vladimir Baranov, kaynak

8 cevap

Bazı zamanlama verilerini sindirmek için bu sorunu çözmek zorunda kaldım. Bu, birden fazla çevrimiçi süreye izin verir, ancak üst üste gelmediklerini varsayar.

select convert(datetime,'1/1/2015 5:00 AM') StartDateTime,  convert(datetime,'1/1/2015 5:00 PM') EndDateTime, convert(varchar(20),'Online') IntervalType into #CapacityIntervals
insert into #CapacityIntervals select '1/1/2015 4:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 5:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 10:00 AM' StartDateTime,  '1/1/2015 12:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 11:00 AM' StartDateTime,  '1/1/2015 1:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 4:00 PM' StartDateTime,  '1/1/2015 6:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 1:30 PM' StartDateTime,  '1/1/2015 2:00 PM' EndDateTime, 'Offline' IntervalType

    --Populate your Offline table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Offline
    from #CapacityIntervals
    where IntervalType in ('Offline','Cleanout')
    group by StartDateTime, EndDateTime

    --Populate your Online table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Online
    from #CapacityIntervals
    where IntervalType not in ('Offline','Cleanout')


    --If you have overlapping online intervals... check for those here and consolidate.


    -------------------------------
    --find overlaping offline times
    -------------------------------
    declare @Finished as tinyint
    set @Finished = 0

    while @Finished = 0
    Begin
        update #Offline
        set #Offline.EndDateTime = OverlapEndDates.EndDateTime
        from #Offline
        join
            (
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum <= Overlap.Rownum
            group by #Offline.Rownum
            ) OverlapEndDates
        on #Offline.Rownum = OverlapEndDates.Rownum

        --Remove Online times completely inside of online times
        delete #Offline
        from #Offline
        join #Offline Overlap
        on #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.Rownum > Overlap.Rownum

        --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
        IF NOT EXISTS(
                select #Offline.Rownum,
                    MAX(Overlap.EndDateTime) EndDateTime
                from #Offline
                join #Offline Overlap
                on  Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                    and #Offline.Rownum < Overlap.Rownum
                group by #Offline.Rownum
                )
            SET @Finished = 1
    END

    -------------------------------
    --Modify Online times with offline ranges
    -------------------------------

    --delete any Online times completely inside offline range
    delete #Online 
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range at the beginning
    update #Online
    set #Online.StartDateTime = #Offline.EndDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime >= #Offline.EndDateTime

    --Find Online Times with offline range at the end
    update #Online
    set #Online.EndDateTime = #Offline.StartDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime <= #Offline.StartDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range punched in the middle
    select #Online.Rownum, 
        #Offline.Rownum OfflineRow,
        #Offline.StartDateTime,
        #Offline.EndDateTime,
        ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
    into #OfflineHoles
    from #Online
    join #Offline
    on #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
        and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

    declare @HoleNumber as integer
    select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

    --Punch the holes out of the online times
    While @HoleNumber > 0
    Begin
        insert into #Online 
        select
            -1 Rownum,
            #OfflineHoles.EndDateTime StartDateTime,
            #Online.EndDateTime EndDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        update #Online
        set #Online.EndDateTime = #OfflineHoles.StartDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        set @[email protected]
    end

--Output total hours
select SUM(datediff(second,StartDateTime, EndDateTime))/3600.0 TotalHr
from #Online

--see how it split up the online intervals
select * 
from #Online
order by StartDateTime, EndDateTime
3
katma
Bu kod tam olarak ne yaptığınıza uymalıdır. Çalışan sayısını, tüm katılımların anahtarı olarak ekleyebilirsiniz. #Online'ı ilk tablonuzla ve #offline'ı ikinci tablonuzla değiştirin. Tüm veri kümenizi aynı anda sindirmelidir. İyi çalışıyor ... aylardır sorunsuz kullanıyoruz.
katma yazar Brian Pressler, kaynak

Bazı zamanlama verilerini sindirmek için bu sorunu çözmek zorunda kaldım. Bu, birden fazla çevrimiçi süreye izin verir, ancak üst üste gelmediklerini varsayar.

select convert(datetime,'1/1/2015 5:00 AM') StartDateTime,  convert(datetime,'1/1/2015 5:00 PM') EndDateTime, convert(varchar(20),'Online') IntervalType into #CapacityIntervals
insert into #CapacityIntervals select '1/1/2015 4:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 5:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 10:00 AM' StartDateTime,  '1/1/2015 12:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 11:00 AM' StartDateTime,  '1/1/2015 1:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 4:00 PM' StartDateTime,  '1/1/2015 6:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 1:30 PM' StartDateTime,  '1/1/2015 2:00 PM' EndDateTime, 'Offline' IntervalType

    --Populate your Offline table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Offline
    from #CapacityIntervals
    where IntervalType in ('Offline','Cleanout')
    group by StartDateTime, EndDateTime

    --Populate your Online table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Online
    from #CapacityIntervals
    where IntervalType not in ('Offline','Cleanout')


    --If you have overlapping online intervals... check for those here and consolidate.


    -------------------------------
    --find overlaping offline times
    -------------------------------
    declare @Finished as tinyint
    set @Finished = 0

    while @Finished = 0
    Begin
        update #Offline
        set #Offline.EndDateTime = OverlapEndDates.EndDateTime
        from #Offline
        join
            (
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum <= Overlap.Rownum
            group by #Offline.Rownum
            ) OverlapEndDates
        on #Offline.Rownum = OverlapEndDates.Rownum

        --Remove Online times completely inside of online times
        delete #Offline
        from #Offline
        join #Offline Overlap
        on #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.Rownum > Overlap.Rownum

        --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
        IF NOT EXISTS(
                select #Offline.Rownum,
                    MAX(Overlap.EndDateTime) EndDateTime
                from #Offline
                join #Offline Overlap
                on  Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                    and #Offline.Rownum < Overlap.Rownum
                group by #Offline.Rownum
                )
            SET @Finished = 1
    END

    -------------------------------
    --Modify Online times with offline ranges
    -------------------------------

    --delete any Online times completely inside offline range
    delete #Online 
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range at the beginning
    update #Online
    set #Online.StartDateTime = #Offline.EndDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime >= #Offline.EndDateTime

    --Find Online Times with offline range at the end
    update #Online
    set #Online.EndDateTime = #Offline.StartDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime <= #Offline.StartDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range punched in the middle
    select #Online.Rownum, 
        #Offline.Rownum OfflineRow,
        #Offline.StartDateTime,
        #Offline.EndDateTime,
        ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
    into #OfflineHoles
    from #Online
    join #Offline
    on #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
        and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

    declare @HoleNumber as integer
    select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

    --Punch the holes out of the online times
    While @HoleNumber > 0
    Begin
        insert into #Online 
        select
            -1 Rownum,
            #OfflineHoles.EndDateTime StartDateTime,
            #Online.EndDateTime EndDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        update #Online
        set #Online.EndDateTime = #OfflineHoles.StartDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        set @[email protected]
    end

--Output total hours
select SUM(datediff(second,StartDateTime, EndDateTime))/3600.0 TotalHr
from #Online

--see how it split up the online intervals
select * 
from #Online
order by StartDateTime, EndDateTime
3
katma
Bu kod tam olarak ne yaptığınıza uymalıdır. Çalışan sayısını, tüm katılımların anahtarı olarak ekleyebilirsiniz. #Online'ı ilk tablonuzla ve #offline'ı ikinci tablonuzla değiştirin. Tüm veri kümenizi aynı anda sindirmelidir. İyi çalışıyor ... aylardır sorunsuz kullanıyoruz.
katma yazar Brian Pressler, kaynak

Bazı zamanlama verilerini sindirmek için bu sorunu çözmek zorunda kaldım. Bu, birden fazla çevrimiçi süreye izin verir, ancak üst üste gelmediklerini varsayar.

select convert(datetime,'1/1/2015 5:00 AM') StartDateTime,  convert(datetime,'1/1/2015 5:00 PM') EndDateTime, convert(varchar(20),'Online') IntervalType into #CapacityIntervals
insert into #CapacityIntervals select '1/1/2015 4:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 5:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 10:00 AM' StartDateTime,  '1/1/2015 12:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 11:00 AM' StartDateTime,  '1/1/2015 1:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 4:00 PM' StartDateTime,  '1/1/2015 6:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 1:30 PM' StartDateTime,  '1/1/2015 2:00 PM' EndDateTime, 'Offline' IntervalType

    --Populate your Offline table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Offline
    from #CapacityIntervals
    where IntervalType in ('Offline','Cleanout')
    group by StartDateTime, EndDateTime

    --Populate your Online table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Online
    from #CapacityIntervals
    where IntervalType not in ('Offline','Cleanout')


    --If you have overlapping online intervals... check for those here and consolidate.


    -------------------------------
    --find overlaping offline times
    -------------------------------
    declare @Finished as tinyint
    set @Finished = 0

    while @Finished = 0
    Begin
        update #Offline
        set #Offline.EndDateTime = OverlapEndDates.EndDateTime
        from #Offline
        join
            (
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum <= Overlap.Rownum
            group by #Offline.Rownum
            ) OverlapEndDates
        on #Offline.Rownum = OverlapEndDates.Rownum

        --Remove Online times completely inside of online times
        delete #Offline
        from #Offline
        join #Offline Overlap
        on #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.Rownum > Overlap.Rownum

        --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
        IF NOT EXISTS(
                select #Offline.Rownum,
                    MAX(Overlap.EndDateTime) EndDateTime
                from #Offline
                join #Offline Overlap
                on  Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                    and #Offline.Rownum < Overlap.Rownum
                group by #Offline.Rownum
                )
            SET @Finished = 1
    END

    -------------------------------
    --Modify Online times with offline ranges
    -------------------------------

    --delete any Online times completely inside offline range
    delete #Online 
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range at the beginning
    update #Online
    set #Online.StartDateTime = #Offline.EndDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime >= #Offline.EndDateTime

    --Find Online Times with offline range at the end
    update #Online
    set #Online.EndDateTime = #Offline.StartDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime <= #Offline.StartDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range punched in the middle
    select #Online.Rownum, 
        #Offline.Rownum OfflineRow,
        #Offline.StartDateTime,
        #Offline.EndDateTime,
        ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
    into #OfflineHoles
    from #Online
    join #Offline
    on #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
        and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

    declare @HoleNumber as integer
    select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

    --Punch the holes out of the online times
    While @HoleNumber > 0
    Begin
        insert into #Online 
        select
            -1 Rownum,
            #OfflineHoles.EndDateTime StartDateTime,
            #Online.EndDateTime EndDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        update #Online
        set #Online.EndDateTime = #OfflineHoles.StartDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        set @[email protected]
    end

--Output total hours
select SUM(datediff(second,StartDateTime, EndDateTime))/3600.0 TotalHr
from #Online

--see how it split up the online intervals
select * 
from #Online
order by StartDateTime, EndDateTime
3
katma
Bu kod tam olarak ne yaptığınıza uymalıdır. Çalışan sayısını, tüm katılımların anahtarı olarak ekleyebilirsiniz. #Online'ı ilk tablonuzla ve #offline'ı ikinci tablonuzla değiştirin. Tüm veri kümenizi aynı anda sindirmelidir. İyi çalışıyor ... aylardır sorunsuz kullanıyoruz.
katma yazar Brian Pressler, kaynak

Cevabımı veri örneğinizle güncelledim ve grafiğinizden vaka 2 ve 5'i kullanan bir çalışan 248 için başka bir örnek daha ekliyorum.

--load example data for emply 547
select CONVERT(int, 547) emp_num, 
    Convert(datetime, '2015-4-1') day_date, 
    Convert(datetime, '2015-4-1 08:00') work_st,
    Convert(datetime, '2015-4-1 16:00') work_end, 
    Convert(datetime, '2015-4-1 07:45') check_in, 
    Convert(datetime, '2015-4-1 12:10') check_out, 
    'W' day_state
into #SecondaryIntervals
insert into #SecondaryIntervals select 547, '2015-4-1', '2015-4-1 08:00', '2015-4-1 16:00', '2015-4-1 12:45', '2015-4-1 17:24', 'W'
insert into #SecondaryIntervals select 547, '2015-4-2', '2015-4-2 00:00', '2015-4-2 00:00', '2015-4-2 07:11', '2015-4-2 13:11', 'E'

select CONVERT(int, 547) emp_num, 
    Convert(datetime, '2015-4-1') day_date, 
    Convert(datetime, '2015-4-1 15:00') mission_in,
    Convert(datetime, '2015-4-1 21:30') mission_out
into #MainIntervals
insert into #MainIntervals select 547, '2015-4-2', '2015-4-2 8:00', '2015-4-2 14:00'

--load more example data for an employee 548 with overlapping secondary intervals
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 9:00', '2015-4-1 10:00', 'W'
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 10:30', '2015-4-1 12:30', 'W'
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 13:15', '2015-4-1 16:00', 'W'

insert into #MainIntervals select 548, '2015-4-1', '2015-4-1 8:00', '2015-4-1 14:00'

--Populate your Offline table with the intervals in #SecondaryIntervals
select 
    ROW_NUMBER() over (Order by emp_num, day_date, StartDateTime, EndDateTime) Rownum,
    emp_num,
    day_date,
    StartDateTime, 
    EndDateTime
into #Offline
from 
    (select emp_num,
        day_date,
        work_st StartDateTime, 
        work_end EndDateTime
    from #SecondaryIntervals
    where day_state = 'W'
    Group by emp_num,
        day_date,
        work_st, 
        work_end
    union
    select 
        emp_num,
        day_date,
        check_in StartDateTime, 
        check_out EndDateTime
    from #SecondaryIntervals
    Group by emp_num,
        day_date,
        check_in, 
        check_out
    ) SecondaryIntervals


--Populate your Online table
select 
    ROW_NUMBER() over (Order by emp_num, day_date, mission_in, mission_out) Rownum,
    emp_num,
    day_date,
    mission_in StartDateTime, 
    mission_out EndDateTime
into #Online
from #MainIntervals
group by emp_num,
    day_date,
    mission_in,
    mission_out


-------------------------------
--find overlaping offline times
-------------------------------
declare @Finished as tinyint
set @Finished = 0

while @Finished = 0
Begin
    update #Offline
    set #Offline.EndDateTime = OverlapEndDates.EndDateTime
    from #Offline
    join
        (
        select #Offline.Rownum,
            MAX(Overlap.EndDateTime) EndDateTime
        from #Offline
        join #Offline Overlap
        on #Offline.emp_num = Overlap.emp_num
            and #Offline.day_date = Overlap.day_date
            and Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
            and #Offline.Rownum <= Overlap.Rownum
        group by #Offline.Rownum
        ) OverlapEndDates
    on #Offline.Rownum = OverlapEndDates.Rownum

    --Remove Online times completely inside of online times
    delete #Offline
    from #Offline
    join #Offline Overlap
    on #Offline.emp_num = Overlap.emp_num
        and #Offline.day_date = Overlap.day_date
        and #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
        and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
        and #Offline.Rownum > Overlap.Rownum

    --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
    IF NOT EXISTS(
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on #Offline.emp_num = Overlap.emp_num
                and #Offline.day_date = Overlap.day_date
                and Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum < Overlap.Rownum
            group by #Offline.Rownum
            )
        SET @Finished = 1
END

-------------------------------
--Modify Online times with offline ranges
-------------------------------

--delete any Online times completely inside offline range
delete #Online 
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
    and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

--Find Online Times with offline range at the beginning
update #Online
set #Online.StartDateTime = #Offline.EndDateTime
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
    and #Online.EndDateTime >= #Offline.EndDateTime

--Find Online Times with offline range at the end
update #Online
set #Online.EndDateTime = #Offline.StartDateTime
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime <= #Offline.StartDateTime
    and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

--Find Online Times with offline range punched in the middle
select #Online.Rownum, 
    #Offline.Rownum OfflineRow,
    #Offline.StartDateTime,
    #Offline.EndDateTime,
    ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
into #OfflineHoles
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
    and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

declare @HoleNumber as integer
select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

--Punch the holes out of the online times
While @HoleNumber > 0
Begin
    insert into #Online 
    select
        -1 Rownum,
        #Online.emp_num,
        #Online.day_date,
        #OfflineHoles.EndDateTime StartDateTime,
        #Online.EndDateTime EndDateTime
    from #Online
    join #OfflineHoles
    on #Online.Rownum = #OfflineHoles.Rownum
    where OfflineHoleNumber = @HoleNumber

    update #Online
    set #Online.EndDateTime = #OfflineHoles.StartDateTime
    from #Online
    join #OfflineHoles
    on #Online.Rownum = #OfflineHoles.Rownum
    where OfflineHoleNumber = @HoleNumber

    set @[email protected]
end

--Output total hours
select emp_num, day_date, 
    SUM(datediff(second,StartDateTime, EndDateTime))/3600.0 TotalHr, 
    SUM(datediff(second,StartDateTime, EndDateTime))/60.0 TotalMin
from #Online
group by emp_num, day_date
order by 1, 2

--see how it split up the online intervals
select emp_num, day_date, StartDateTime, EndDateTime
from #Online
order by 1, 2, 3, 4

Çıktı:

emp_num     day_date                TotalHr                                 TotalMin
----------- ----------------------- --------------------------------------- ---------------------------------------
547         2015-04-01 00:00:00.000 4.100000                                246.000000
547         2015-04-02 00:00:00.000 0.816666                                49.000000
548         2015-04-01 00:00:00.000 0.750000                                45.000000

(3 row(s) affected)

emp_num     day_date                StartDateTime           EndDateTime
----------- ----------------------- ----------------------- -----------------------
547         2015-04-01 00:00:00.000 2015-04-01 17:24:00.000 2015-04-01 21:30:00.000
547         2015-04-02 00:00:00.000 2015-04-02 13:11:00.000 2015-04-02 14:00:00.000
548         2015-04-01 00:00:00.000 2015-04-01 12:30:00.000 2015-04-01 13:15:00.000

(3 row(s) affected)

Başka bir cevabımı da bıraktım, çünkü başkası tarafından yakalanmayı istemesi daha genel. Görüyorum ki bu soruya bir ödül eklediniz. Cevabımla ilgili, sizi tatmin etmeyen özel bir şey varsa bana bildirin ve size yardım etmeye çalışacağım. Binlerce aralık bu yöntemle işler ve sadece birkaç saniye içinde geri döner.

2
katma
Her iki cevabınızı da oyluyorum ve birçok davamda onları test edeceğim, çok teşekkürler
katma yazar Anyname Donotcare, kaynak

Cevabımı veri örneğinizle güncelledim ve grafiğinizden vaka 2 ve 5'i kullanan bir çalışan 248 için başka bir örnek daha ekliyorum.

--load example data for emply 547
select CONVERT(int, 547) emp_num, 
    Convert(datetime, '2015-4-1') day_date, 
    Convert(datetime, '2015-4-1 08:00') work_st,
    Convert(datetime, '2015-4-1 16:00') work_end, 
    Convert(datetime, '2015-4-1 07:45') check_in, 
    Convert(datetime, '2015-4-1 12:10') check_out, 
    'W' day_state
into #SecondaryIntervals
insert into #SecondaryIntervals select 547, '2015-4-1', '2015-4-1 08:00', '2015-4-1 16:00', '2015-4-1 12:45', '2015-4-1 17:24', 'W'
insert into #SecondaryIntervals select 547, '2015-4-2', '2015-4-2 00:00', '2015-4-2 00:00', '2015-4-2 07:11', '2015-4-2 13:11', 'E'

select CONVERT(int, 547) emp_num, 
    Convert(datetime, '2015-4-1') day_date, 
    Convert(datetime, '2015-4-1 15:00') mission_in,
    Convert(datetime, '2015-4-1 21:30') mission_out
into #MainIntervals
insert into #MainIntervals select 547, '2015-4-2', '2015-4-2 8:00', '2015-4-2 14:00'

--load more example data for an employee 548 with overlapping secondary intervals
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 9:00', '2015-4-1 10:00', 'W'
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 10:30', '2015-4-1 12:30', 'W'
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 13:15', '2015-4-1 16:00', 'W'

insert into #MainIntervals select 548, '2015-4-1', '2015-4-1 8:00', '2015-4-1 14:00'

--Populate your Offline table with the intervals in #SecondaryIntervals
select 
    ROW_NUMBER() over (Order by emp_num, day_date, StartDateTime, EndDateTime) Rownum,
    emp_num,
    day_date,
    StartDateTime, 
    EndDateTime
into #Offline
from 
    (select emp_num,
        day_date,
        work_st StartDateTime, 
        work_end EndDateTime
    from #SecondaryIntervals
    where day_state = 'W'
    Group by emp_num,
        day_date,
        work_st, 
        work_end
    union
    select 
        emp_num,
        day_date,
        check_in StartDateTime, 
        check_out EndDateTime
    from #SecondaryIntervals
    Group by emp_num,
        day_date,
        check_in, 
        check_out
    ) SecondaryIntervals


--Populate your Online table
select 
    ROW_NUMBER() over (Order by emp_num, day_date, mission_in, mission_out) Rownum,
    emp_num,
    day_date,
    mission_in StartDateTime, 
    mission_out EndDateTime
into #Online
from #MainIntervals
group by emp_num,
    day_date,
    mission_in,
    mission_out


-------------------------------
--find overlaping offline times
-------------------------------
declare @Finished as tinyint
set @Finished = 0

while @Finished = 0
Begin
    update #Offline
    set #Offline.EndDateTime = OverlapEndDates.EndDateTime
    from #Offline
    join
        (
        select #Offline.Rownum,
            MAX(Overlap.EndDateTime) EndDateTime
        from #Offline
        join #Offline Overlap
        on #Offline.emp_num = Overlap.emp_num
            and #Offline.day_date = Overlap.day_date
            and Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
            and #Offline.Rownum <= Overlap.Rownum
        group by #Offline.Rownum
        ) OverlapEndDates
    on #Offline.Rownum = OverlapEndDates.Rownum

    --Remove Online times completely inside of online times
    delete #Offline
    from #Offline
    join #Offline Overlap
    on #Offline.emp_num = Overlap.emp_num
        and #Offline.day_date = Overlap.day_date
        and #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
        and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
        and #Offline.Rownum > Overlap.Rownum

    --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
    IF NOT EXISTS(
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on #Offline.emp_num = Overlap.emp_num
                and #Offline.day_date = Overlap.day_date
                and Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum < Overlap.Rownum
            group by #Offline.Rownum
            )
        SET @Finished = 1
END

-------------------------------
--Modify Online times with offline ranges
-------------------------------

--delete any Online times completely inside offline range
delete #Online 
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
    and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

--Find Online Times with offline range at the beginning
update #Online
set #Online.StartDateTime = #Offline.EndDateTime
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
    and #Online.EndDateTime >= #Offline.EndDateTime

--Find Online Times with offline range at the end
update #Online
set #Online.EndDateTime = #Offline.StartDateTime
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime <= #Offline.StartDateTime
    and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

--Find Online Times with offline range punched in the middle
select #Online.Rownum, 
    #Offline.Rownum OfflineRow,
    #Offline.StartDateTime,
    #Offline.EndDateTime,
    ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
into #OfflineHoles
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
    and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

declare @HoleNumber as integer
select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

--Punch the holes out of the online times
While @HoleNumber > 0
Begin
    insert into #Online 
    select
        -1 Rownum,
        #Online.emp_num,
        #Online.day_date,
        #OfflineHoles.EndDateTime StartDateTime,
        #Online.EndDateTime EndDateTime
    from #Online
    join #OfflineHoles
    on #Online.Rownum = #OfflineHoles.Rownum
    where OfflineHoleNumber = @HoleNumber

    update #Online
    set #Online.EndDateTime = #OfflineHoles.StartDateTime
    from #Online
    join #OfflineHoles
    on #Online.Rownum = #OfflineHoles.Rownum
    where OfflineHoleNumber = @HoleNumber

    set @[email protected]
end

--Output total hours
select emp_num, day_date, 
    SUM(datediff(second,StartDateTime, EndDateTime))/3600.0 TotalHr, 
    SUM(datediff(second,StartDateTime, EndDateTime))/60.0 TotalMin
from #Online
group by emp_num, day_date
order by 1, 2

--see how it split up the online intervals
select emp_num, day_date, StartDateTime, EndDateTime
from #Online
order by 1, 2, 3, 4

Çıktı:

emp_num     day_date                TotalHr                                 TotalMin
----------- ----------------------- --------------------------------------- ---------------------------------------
547         2015-04-01 00:00:00.000 4.100000                                246.000000
547         2015-04-02 00:00:00.000 0.816666                                49.000000
548         2015-04-01 00:00:00.000 0.750000                                45.000000

(3 row(s) affected)

emp_num     day_date                StartDateTime           EndDateTime
----------- ----------------------- ----------------------- -----------------------
547         2015-04-01 00:00:00.000 2015-04-01 17:24:00.000 2015-04-01 21:30:00.000
547         2015-04-02 00:00:00.000 2015-04-02 13:11:00.000 2015-04-02 14:00:00.000
548         2015-04-01 00:00:00.000 2015-04-01 12:30:00.000 2015-04-01 13:15:00.000

(3 row(s) affected)

Başka bir cevabımı da bıraktım, çünkü başkası tarafından yakalanmayı istemesi daha genel. Görüyorum ki bu soruya bir ödül eklediniz. Cevabımla ilgili, sizi tatmin etmeyen özel bir şey varsa bana bildirin ve size yardım etmeye çalışacağım. Binlerce aralık bu yöntemle işler ve sadece birkaç saniye içinde geri döner.

2
katma
Her iki cevabınızı da oyluyorum ve birçok davamda onları test edeceğim, çok teşekkürler
katma yazar Anyname Donotcare, kaynak

Benim çözümüm Vladimir Baranov'a çok benziyor.

Link to .NetFiddle

Genel fikir

Algoritmam, aralık ağacının değiştirilmesine dayanır. En küçük zaman biriminin 1 dakika olduğunu varsayar (değiştirmesi kolaydır).

Her bir ağaç düğümü 3 durumdan birindedir: ziyaret edilmemiş, ziyaret edilmiş ve kullanılmış. Algoritma, aşağıdaki adımlarla açıklanabilen özyinelemeli Ara işlevine dayanır:

  1. Eğer düğüm kullanılmışsa ya da arama aralığı boşsa, boş alanı döndürün.
  2. Düğüm görünmüyorsa ve düğüm aralığı aralığına eşitse, geçerli düğümü kullanılmış olarak işaretleyin ve düğüm aralığını döndürün.
  3. Düğümü ziyaret edildi olarak işaretleyin, seraching aralığını bölün ve sol ve sağ çocuklar için Ara toplamı döndürün.

Adımlar halinde çözüm

  1. Calculate biggest interval.
  2. Add to tree "secondary intervals".
  3. Add to tree "main interval".
  4. Calculate sum of intervals.

    Please note I assume that intervals are [start; end], i.e. both intervals are inclusive, what is easy to change.

Gereksinimleri

varsayarsak

n - "ikincil aralık" sayısı

m - ana birimde maksimum süre

İnşaat O (2n) depolama alanı gerektirir ve O (n log n + m) zamanında çalışır.

İşte kodum

  public class Interval
    {
        public int Start { get; set; }

        public int End { get; set; }
    };
    enum Node
    {
        Unvisited = 0,
        Visited = 1,
        Used = 2
    };
    Node[] tree;

    public void Calculate()
    {
        var secondryIntervalsAsDates = new List> { new Tuple( new DateTime(2015, 03, 15, 4, 0, 0), new DateTime(2015, 03, 15, 5, 0, 0))};
        var mainInvtervalAsDate = new Tuple(new DateTime(2015, 03, 15, 3, 0, 0), new DateTime(2015, 03, 15, 7, 0, 0));
       //calculate biggest interval
        var startDate = secondryIntervalsAsDates.Union( new List>{mainInvtervalAsDate}).Min(s => s.Item1).AddMinutes(-1);
        var endDate = secondryIntervalsAsDates.Union(new List> { mainInvtervalAsDate }).Max(s => s.Item2);
        var mainInvterval = new Interval { Start = (int)(mainInvtervalAsDate.Item1 - startDate).TotalMinutes, End = (int)(mainInvtervalAsDate.Item2 - startDate).TotalMinutes };
        var wholeInterval = new Interval { Start = 1, End = (int)(endDate - startDate).TotalMinutes};
        //convert intervals to minutes
        var secondaryIntervals = secondryIntervalsAsDates.Select(s => new Interval { Start = (int)(s.Item1 - startDate).TotalMinutes, End = (int)(s.Item2 - startDate).TotalMinutes}).ToList();
        tree = new Node[wholeInterval.End * 2 + 1];
        //insert secondary intervals
        secondaryIntervals.ForEach(s => Search(wholeInterval, s, 1));
        //insert main interval
        var result = Search(wholeInterval, mainInvterval, 1);
        //calculate result
        var minutes = result.Sum(r => r.End - r.Start) + result.Count();
    }

    public IEnumerable Search(Interval current, Interval searching, int index)
    {
        if (tree[index] == Node.Used || searching.End < searching.Start)
        {
            return new List();
        }
        if (tree[index] == Node.Unvisited && current.Start == searching.Start && current.End == searching.End)
        {
            tree[index] = Node.Used;
            return new List { current };
        }
        tree[index] = Node.Visited;
        return Search(new Interval { Start = current.Start, End = current.Start + (current.End - current.Start)/2 },
                  new Interval { Start = searching.Start, End = Math.Min(searching.End, current.Start + (current.End - current.Start)/2)  }, index * 2).Union(
            Search(new Interval { Start = current.Start + (current.End - current.Start)/2 + 1 , End = current.End},
              new Interval { Start = Math.Max(searching.Start, current.Start + (current.End - current.Start)/2 + 1), End = searching.End }, index * 2 + 1));
    }
2
katma

İşte tam bir sorgu ile SQLFiddle .

Her emp_num, day_date için dakika sayısını döndüren bir sorgu oluşturduğumu göstereceğim. Belirli bir emp_num, day_date için hiç dakika kalmadığı ortaya çıkarsa, sonuçta 0 olan bir satır olmazsa, sonuç şöyle olur: hiç böyle bir satır yok.

Genel fikir

Bir sayı tablosunu kullanacağım . Yalnızca 24 * 60 = 1440 numaralarına ihtiyacımız olacak, ancak bu tür bir tablonun veritabanında başka raporlar için olması iyi bir fikirdir. Şahsen 100.000 satırla aldım. Farklı yöntemleri karşılaştıran bir çok iyi bir makale 'dir. Böyle bir tablo oluşturmak için.

Her aralık için sayı tablosunu kullanarak bir satır kümesi üreteceğim - aralıktaki her dakika için bir satır. Aralıkların olduğunu düşünüyorum [start; son) , yani başlangıç ​​dakikası kapsayıcıdır, bitiş dakikası özeldir. Örneğin, 07:00 ile 08:00 arasındaki aralık 61 değil 60 dakikadır.

Sayı tablosu oluştur

DECLARE @Numbers TABLE (N int);
INSERT INTO @Numbers(N)
SELECT TOP(24*60)
    ROW_NUMBER() OVER(ORDER BY S.object_id) - 1 AS N
FROM
    sys.all_objects AS S
ORDER BY N
;

Bu görev için 0'dan başlayan sayılara sahip olmak daha iyidir. Normalde N 'de birincil anahtar bulunan kalıcı bir tablo olur.

Örnek veriler

DECLARE @Missions TABLE (emp_num int, day_date datetime, mission_in datetime, mission_out datetime);
DECLARE @Periods TABLE (emp_num int, day_date datetime, work_st datetime, work_end datetime, check_in datetime, check_out datetime, day_state char(1));

INSERT INTO @Missions (emp_num, day_date, mission_in, mission_out) VALUES
(547, '2015-04-01', '2015-04-01 15:00:00', '2015-04-01 21:30:00'),
(547, '2015-04-02', '2015-04-02 08:00:00', '2015-04-02 14:00:00');

INSERT INTO @Periods (emp_num, day_date, work_st, work_end, check_in, check_out, day_state) VALUES
(547, '2015-04-01', '2015-04-01 08:00:00', '2015-04-01 16:00:00', '2015-04-01 07:45:00', '2015-04-01 12:10:00', 'W'),
(547, '2015-04-01', '2015-04-01 08:00:00', '2015-04-01 16:00:00', '2015-04-01 12:45:00', '2015-04-01 17:24:00', 'W'),
(547, '2015-04-02', '2015-04-02 00:00:00', '2015-04-02 00:00:00', '2015-04-02 07:11:00', '2015-04-02 13:11:00', 'E');

Çözümüm day_state sütununu kullanmayacak. Hem work_st hem de work_end için 00:00:00 olmasını bekliyoruz. Çözüm, aynı satırdaki tarih bileşeninin aynı olduğunu ve day_date öğesinin zaman bileşenine sahip olmadığını düşünüyor.

Şemayı bu görev için tasarlarsam iki yerine üç tablom olur: Görevler , WorkPeriods ve CheckPeriods . work_st ve work_end 'in birkaç satırda tekrarlanmaması için tablonuzu Dönemler ' e ikiye bölerim. Ancak bu çözüm şu anki şemanıza değinecek ve esasen bu üçüncü tabloyu anında oluşturacak. Uygulamada performansın arttırılabileceği anlamına gelir.

Görev dakikaları

WITH
CTE_MissionMinutes
AS
(
    SELECT emp_num, day_date, N.N
    FROM
        @Missions AS M
        CROSS JOIN @Numbers AS N
    WHERE
        N.N >= DATEDIFF(minute, M.day_date, M.mission_in) AND
        N.N < DATEDIFF(minute, M.day_date, M.mission_out)
)

@Missions öğesindeki her orijinal satır, (mission_in, mission_out) aralığının her dakikası için bir satır kümesine dönüşür.

Çalışma dönemleri

,CTE_WorkPeriods
AS
(
    SELECT P.emp_num, P.day_date, P.work_st, P.work_end
    FROM @Periods AS P
    GROUP BY P.emp_num, P.day_date, P.work_st, P.work_end
)

Üçüncü bir yardımcı tablo oluşturun - her emp_num, day_date, work_st, work_end için bir satır - (work_st, work_end) için tüm aralıklar.

Çalış ve Kontrol dakikaları

,CTE_WorkMinutes
AS
(
    SELECT emp_num, day_date, N.N
    FROM
        CTE_WorkPeriods
        CROSS JOIN @Numbers AS N
    WHERE
        N.N >= DATEDIFF(minute, CTE_WorkPeriods.day_date, CTE_WorkPeriods.work_st) AND
        N.N < DATEDIFF(minute, CTE_WorkPeriods.day_date, CTE_WorkPeriods.work_end)
)
,CTE_CheckMinutes
AS
(
    SELECT emp_num, day_date, N.N
    FROM
        @Periods AS P
        CROSS JOIN @Numbers AS N
    WHERE
        N.N >= DATEDIFF(minute, P.day_date, P.check_in) AND
        N.N < DATEDIFF(minute, P.day_date, P.check_out)
)

Aynen Görevler ile aynı.

Birlik "ikincil aralıklar"

,CTE_UnionPeriodMinutes
AS
(
    SELECT emp_num, day_date, N
    FROM CTE_WorkMinutes

    UNION ALL -- can be not ALL here, but ALL is usually faster

    SELECT emp_num, day_date, N
    FROM CTE_CheckMinutes
)

İkincil aralıkları birincil alandan çıkarma

,CTE_FinalMinutes
AS
(
    SELECT emp_num, day_date, N
    FROM CTE_MissionMinutes

    EXCEPT

    SELECT emp_num, day_date, N
    FROM CTE_UnionPeriodMinutes
)

Dakika sayısını toplayın

SELECT
    emp_num
    ,day_date
    ,COUNT(*) AS FinalMinutes
FROM CTE_FinalMinutes
GROUP BY emp_num, day_date
ORDER BY emp_num, day_date;

Son sorguyu yapmak için sadece tüm CTE'leri bir araya getirin.

Sonuç seti

emp_num day_date                FinalMinutes
547     2015-04-01 00:00:00.000 246
547     2015-04-02 00:00:00.000 49

There are 246 minutes between 17:24 and 21:30.
There are  49 minutes between 13:11 and 14:00.

İşte tam bir sorgu ile SQLFiddle .

Bu SUM dakikaya neden olan gerçek aralıkları göstermek oldukça kolaydır, ancak yalnızca SUM ihtiyacınız olduğunu söylediniz.

1
katma

Muhtemelen en basit çözümü öğrendim.

.netFiddle

  1. Sort "Secondary intervals" by start date.
  2. Look for gaps in "secondary intervals" (simple iteration)
  3. Compare gaps with "main interval".

        //declare intervals
    var secondryIntervals = new List> {
            new Tuple( new DateTime(2015, 03, 15, 4, 0, 0), new DateTime(2015, 03, 15, 5, 0, 0)),
            new Tuple( new DateTime(2015, 03, 15, 4, 10, 0), new DateTime(2015, 03, 15, 4, 40, 0)),
            new Tuple( new DateTime(2015, 03, 15, 4, 40, 0), new DateTime(2015, 03, 15, 5, 20, 0))};
    var mainInterval = new Tuple(new DateTime(2015, 03, 15, 3, 0, 0), new DateTime(2015, 03, 15, 7, 0, 0));
    // add two empty intervals before and after main interval
    secondryIntervals.Add(new Tuple(mainInterval.Item1.AddMinutes(-1), mainInterval.Item1.AddMinutes(-1)));
    secondryIntervals.Add(new Tuple(mainInterval.Item2.AddMinutes(1), mainInterval.Item2.AddMinutes(1)));
    secondryIntervals = secondryIntervals.OrderBy(s => s.Item1).ToList();
    // endDate will rember 'biggest' end date
    var endDate = secondryIntervals.First().Item1;
    var result = secondryIntervals.Select(s =>
    {
        var temp = endDate;
        endDate = endDate < s.Item2 ? s.Item2 : endDate;
        if (s.Item1 > temp)
        {
            return new Tuple(temp < mainInterval.Item1 ? mainInterval.Item1 : temp,
                                                 mainInterval.Item2 < s.Item1 ? mainInterval.Item2 : s.Item1);
        }
        return null;
    })
       //remove empty records
                    .Where(s => s != null && s.Item2 > s.Item1).ToList();
    var minutes = result.Sum(s => (s.Item2 - s.Item1).TotalMinutes);
    

Algoritma, ek depolama ve varsayımlar olmadan O (n log n) zaman (sıralama için) gerektirir.

1
katma