Question Comment faire le calcul sur des intervalles de temps?


J'ai un problème, je le résous mais j'ai écrit une longue procédure et je ne suis pas sûr que cela couvre tous les cas possibles.

Le problème:

Si j'ai un temps d'intervalle principal (From A to B), et temps d'intervalle secondaire (Beaucoup ou non)

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

je veux SOMME toutes les parties de l'intervalle de temps principal (AB) hors de intervalles secondaires dans minutes en efficacité et le moins de conditions (procédure SQL Server et méthode C #)?

Par exemple: Si mon intervalle principal De 02:00 to 10:30 Et dire un intervalle secondaire De 04:00 to 08:00

Maintenant je veux ce résultat: ((04:00 - 02:00) + (10:30 -08:00))* 60

Exemple avec graphique:

dans le premier cas le résultat sera:

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

et ce sera plus compliqué quand j'ai plusieurs périodes secondaires.

REMARQUE:

Peut-être le chevauchement entre les intervalles secondaires se produisant seulement lorsque je dois comparer la période principale [A, B] à la période SYNDICAT de au plus deux ensembles parallèles d'intervalles secondaires .le premier ensemble ne doit contenir qu'un seul intervalle secondaire et le second ensemble contient (plusieurs ou aucun) des intervalles secondaires. Par exemple, dans le graphique comparant [A,B] à (ensembles de 2,5) le premier ensemble (2) se compose d'un intervalle secondaire et le deuxième ensemble (5) se compose de trois intervalles secondaires. et c'est le pire des cas, je dois gérer.

Par exemple :

SI mon intervalle principal est [15:00,19:40] et j'ai deux séries d'intervalles secondaires. Selon ma règle, au moins un de ces ensembles doit être constitué d'un intervalle secondaire. dire que le premier ensemble est [11:00 ,16:00] et le deuxième ensemble est constitué de deux intervalles secondaires [10:00,15:00],[16:30,17:45] Maintenant, je veux le résultat (16:30 -16:00) +(19:40 -17:45)


Selon les commentaires:

Ma table est comme ceci:

Le premier tableau contient des périodes secondaires, au maximum deux séries de périodes secondaires à la même date pour un employé spécifique. le premier ensemble ne contient qu'une seule période secondaire dans la journée de travail (W)  [work_st,work_end], et cet ensemble sera vide si le jour est le week-end [E] et dans ce cas, pas de chevauchement entre les périodes secondaires. et le second ensemble peut contenir plusieurs périodes secondaires à la même date [check_in,check_out] , parce que l'employé peut vérifier plusieurs fois dans la même journée.

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

Le deuxième tableau contient la période principale[A,B] et c'est une période pour cet employé à ce jour (un enregistrement)

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

Dans l'exemple précédent, si j'ai une procédure ou une méthode requise, cette procédure doit prendre deux paramètres:

  • La date
  • L'emp_num

dans l'exemple précédent, il devrait être comme ça ('2015-4-1' ,547)

Selon mon explication:

  • La période principale (période de mission) [A,B] à partir du deuxième tableau: Devrait être une seule période dans cette date pour cet employé

    [15:00,21:30]

  • La période secondaire pour la date de passage ('2015-4-1') pour cet employé était deux ensembles de périodes secondaires (le pire des cas) du premier tableau

    Le premier ensemble ne doit contenir qu’une seule période secondaire (ou zéro périodes) [08:00,16:00]le deuxième ensemble pourrait contenir beaucoup de secondaire périodes (ou périodes nulles)

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

La sortie devrait être [17: 24,21: 30] convertie en minutes

Remarque

tout day_date,mission_in,mission_out,work_st,work_end,check_in,check_out sont datetime champs mais je mets juste le temps dans l'exemple pour la simplification, je veux ignorer la partie de la date sauf le day_date parce que c'est la date que je calcule sur la base en plus de la emp_num.

enter image description here


18
2018-04-09 21:11


origine


Réponses:


J'ai mis à jour ma réponse avec votre exemple de données et j'ajoute un autre exemple pour un employé 248 qui utilise les cas 2 et 5 de votre graphique.

--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 @HoleNumber=@HoleNumber-1
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

Le résultat est:

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)

J'ai laissé mon autre réponse affichée car elle est plus générique au cas où quelqu'un d'autre voudrait l'enregistrer. Je vois que vous avez ajouté une prime à cette question. Dites-moi s'il y a quelque chose de précis dans ma réponse qui ne vous satisfait pas et je vais essayer de vous aider. Je traite des milliers d'intervalles avec cette méthode et elle retourne en quelques secondes.


2
2018-04-15 00:24



J'ai dû résoudre ce problème pour digérer certaines données de planification. Cela permet plusieurs temps en ligne, mais suppose qu'ils ne se chevauchent pas.

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 @HoleNumber=@HoleNumber-1
    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
2018-04-09 22:56



Ma solution est assez similaire à Vladimir Baranov.

Lié à .NetFiddle

Idée générale

Mon algorithme est basé sur une modification de arbre d'intervalle. Il suppose que la plus petite unité de temps est 1 minute (facile à modifier).

Chaque nœud d'arbre est dans 1 de 3 états: non visité, visité et utilisé. L'algorithme est basé sur récursif Chercher fonction qui peut être décrite par les étapes suivantes:

  1. Si le noeud est utilisé ou l'intervalle de recherche est vide puis retourne un intervalle vide.
  2. Si le noeud est non visité et l'intervalle des nœuds est égal à l'intervalle de recherche, puis marque le nœud actuel comme utilisé et renvoyer un intervalle de nœud.
  3. Marquer le noeud comme a visité, fractionner les intervalles de recherche et retourner la somme de Chercher pour les enfants de gauche et de droite.

Solution par étapes

  1. Calculer le plus grand intervalle.
  2. Ajouter à l'arbre "intervalles secondaires".
  3. Ajouter à l'arborescence "intervalle principal".
  4. Calculer la somme des intervalles.

    Notez s'il vous plaît  Je suppose que les intervalles sont [démarrer; end], c’est-à-dire que les deux intervalles sont inclusifs, ce qui est facile à changer.

Exigences

En supposant

n - nombre de "intervalles secondaires"

m - temps maximum dans l'unité de base

La construction nécessite un espace de stockage O (2n) et fonctionne en temps O (n log n + m).

Voici mon code

  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<Tuple<DateTime,DateTime>> { new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 0, 0), new DateTime(2015, 03, 15, 5, 0, 0))};
        var mainInvtervalAsDate = new Tuple<DateTime, DateTime>(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<Tuple<DateTime,DateTime>>{mainInvtervalAsDate}).Min(s => s.Item1).AddMinutes(-1);
        var endDate = secondryIntervalsAsDates.Union(new List<Tuple<DateTime, DateTime>> { 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<Interval> Search(Interval current, Interval searching, int index)
    {
        if (tree[index] == Node.Used || searching.End < searching.Start)
        {
            return new List<Interval>();
        }
        if (tree[index] == Node.Unvisited && current.Start == searching.Start && current.End == searching.End)
        {
            tree[index] = Node.Used;
            return new List<Interval> { 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
2018-04-17 12:25



Voici SQLFiddle avec requête complète

Je vais montrer comment j'ai construit une requête qui renvoie le nombre de minutes pour chaque emp_num, day_date. S'il s'avère qu'il ne reste plus de minutes pour un emp_num, day_date, alors le résultat serait ne pas faire la queue avec 0, il n'y en aura pas du tout.

Idée générale

Je vais utiliser un table des nombres. Nous n'aurons besoin que de 24*60=1440 chiffres, mais c'est une bonne idée d'avoir une telle table dans votre base de données pour d'autres rapports. Je l'ai personnellement avec 100 000 lignes. Voici une très bon article comparer différentes méthodes pour générer une telle table.

Pour chaque intervalle, je vais générer un ensemble de lignes en utilisant la table de nombres - une ligne pour chaque minute de l'intervalle. Je suppose que les intervalles sont [start; end), c.-à-d. la minute de début est inclusive, la minute de fin est exclusive. Par exemple, intervalle de 07:00 à 08:00 est 60 minutes, pas 61.

Générer une table de nombres

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
;

Pour cette tâche, il est préférable d’avoir des nombres qui commencent à 0. Normalement, vous auriez une table permanente avec une clé primaire sur N.

Données d'échantillon

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');

Ma solution n'utilisera pas le day_state colonne. Je pense que vous auriez 00:00:00 pour les deux work_st et work_end. Solution s'attend à ce que le composant de date dans la même ligne soit le même et que day_date n'a pas de composant temps.

Si j'ai conçu le schéma pour cette tâche, j'aurais trois tables au lieu de deux: Missions, WorkPeriods et CheckPeriods. Je partagerais ta table Periods en deux pour éviter de répéter work_st et work_end en plusieurs lignes. Mais cette solution traitera de votre schéma actuel et générera essentiellement cette troisième table à la volée. En pratique, cela signifie que la performance peut être améliorée.

Minutes de mission

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)
)

Chaque rangée d'origine de @Missions se transforme en un ensemble de lignes, une pour chaque minute de l'intervalle (mission_in, mission_out).

Périodes de travail

,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
)

Générer une troisième table d'aide - une ligne pour chaque emp_num, day_date, work_st, work_end  - tous les intervalles pour (work_st, work_end).

Minutes de travail et de vérification

,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)
)

Exactement la même que pour Missions.

Union "intervalles secondaires"

,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
)

Soustraire les intervalles secondaires du primaire

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

    EXCEPT

    SELECT emp_num, day_date, N
    FROM CTE_UnionPeriodMinutes
)

Nombre total de minutes

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

Pour faire la requête finale, mettez simplement tous les CTE ensemble.

Jeu de résultats

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.

Voici SQLFiddle avec requête complète

Il est assez facile de montrer les intervalles réels qui mènent à cette SUM de minutes, mais vous avez dit que vous aviez besoin de la SUM.


1
2018-04-15 12:30



J'ai probablement trouvé la solution la plus simple.

.netFiddle

  1. Trier "Intervalles secondaires" par date de début.
  2. Rechercher des lacunes dans les "intervalles secondaires" (itération simple)
  3. Comparer les écarts avec "intervalle principal".

        //declare intervals
    var secondryIntervals = new List<Tuple<DateTime, DateTime>> {
            new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 0, 0), new DateTime(2015, 03, 15, 5, 0, 0)),
            new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 10, 0), new DateTime(2015, 03, 15, 4, 40, 0)),
            new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 40, 0), new DateTime(2015, 03, 15, 5, 20, 0))};
    var mainInterval = new Tuple<DateTime, DateTime>(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<DateTime, DateTime>(mainInterval.Item1.AddMinutes(-1), mainInterval.Item1.AddMinutes(-1)));
    secondryIntervals.Add(new Tuple<DateTime, DateTime>(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<DateTime, DateTime>(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);
    

L'algorithme nécessite O (n log n) temps (pour le tri) sans stockage et hypothèses supplémentaires.


1
2018-04-19 09:42