J'essaie d'obtenir la différence entre deux dates en quelques minutes, est-il possible pour moi d'exclure les heures de déjeuner de ce calcul, c'est-à-dire 13: 30-14: 00 uniquement dans mon instruction SQL. Ce que je fais actuellement est ci-dessous:

Exemple de ligne

SetupStart                  StartTime                  SetupTime 
----------------------------------------------------------------
2017-01-23 12:56:42.000     2017-01-23 14:41:06.000     105

Ma déclaration actuelle:

DATEDIFF(MINUTE, UserData.Orders.SetupStart, UserData.Orders.StartTime)  AS[SetupTime]

ÉDITER:--------------

Voici ma déclaration Select actuelle

SELECT DISTINCT 
                  TOP (100) PERCENT UserData.Resources.Name AS Resource, UserData.Orders.OrderNo, UserData.Orders.StringAttribute4 AS Customer, UserData.Orders.Product, 
                  CEILING(UserData.Orders.OpNo10Quantity) AS OpNo10Quantity, UserData.Orders.NumericalAttribute12 AS Speed, UserData.Orders.SetupStart, UserData.Orders.StartTime, 
                  UserData.Orders.EndTime, CASE ToggleAttribute1 WHEN 1 THEN 'Yes' ELSE 'No' END AS Packed, UserData.Orders.StringAttribute5 AS OrderInstructions, UserData.Orders.NextResource, 
                  UserData.Orders.DatasetId, UserData.Orders_Dataset.name AS ScheduleName, UserData.Orders.ShowOnReport, dbo.tblPreactorExportFullv10.Length AS L, 
                  dbo.tblPreactorExportFullv10.Thickness AS T, dbo.tblPreactorExportFullv10.fWidth AS W,
 DATEDIFF(MINUTE, UserData.Orders.SetupStart, UserData.Orders.StartTime) AS [SAM SetupTime]
FROM         UserData.Orders INNER JOIN
                  UserData.Resources ON UserData.Orders.Resource = UserData.Resources.ResourcesId AND UserData.Orders.Resource = UserData.Resources.ResourcesId INNER JOIN
                  UserData.Orders_Dataset ON UserData.Orders.DatasetId = UserData.Orders_Dataset.DatasetId AND UserData.Orders.DatasetId = UserData.Orders_Dataset.DatasetId INNER JOIN
                  dbo.tblPreactorExportFullv10 ON UserData.Orders.PartNo = dbo.tblPreactorExportFullv10.ProductCode
WHERE     (UserData.Orders.DatasetId = 15) AND (UserData.Resources.Name = 'Moulder 6') AND (UserData.Orders.ShowOnReport = 1)
          AND (UserData.Orders.OperationProgress <> 5)
 ORDER BY UserData.Orders.SetupStart
1
Bunion 24 janv. 2017 à 19:43

4 réponses

Meilleure réponse

Je suis sûr que la réponse de Gordon sera plus rapide, mais une autre option est la suivante:

(Edit: Juste pour le plaisir, a exécuté ceci sur 20000 enregistrements et est revenu en 238 millisecondes)

Declare @YourTable table (SetupStart datetime, StartTime datetime)
Insert Into @YourTable values
('2017-01-23 12:56:42.000','2017-01-23 14:41:06.000'),
('2017-01-23 15:00:00.000','2017-01-23 18:30:00.000'),   -- No Lunch
('2017-01-23 23:51:00.000','2017-01-23 23:53:46.000'),   -- Anomoly mentioned
('2017-01-23 13:15:00.000','2017-01-23 13:45:00.000')    -- Started After Lunch

Select A.*
      ,B.*
 From  ( ... your complex query here ... ) A
 Cross Apply (
              Select SetupTime = count(*)
                From (Select Top (DateDiff(MINUTE,A.SetupStart,A.StartTime)) T=cast(DateAdd(MINUTE,Row_Number() Over (Order By Number)-1,A.SetupStart) as time)
                       From  master..spt_values ) S
                Where  (cast(A.SetupStart as time)<'13:30' and cast(A.StartTime as time)>'14:00' and T not between '13:30' and '14:00')
                   or  (cast(A.SetupStart as time) > '13:30'  )
                   or  (cast(A.StartTime as time)  < '14:00' )
       ) B

Retour

SetupStart                StartTime                 SetupTime
2017-01-23 12:56:42.000   2017-01-23 14:41:06.000   75
2017-01-23 15:00:00.000   2017-01-23 18:30:00.000   210
2017-01-23 23:51:00.000   2017-01-23 23:53:46.000   2
2017-01-23 13:15:00.000   2017-01-23 13:45:00.000   30
1
John Cappelletti 24 janv. 2017 à 20:11

Le moyen le plus simple est ce que @Shakeer Hussain a recommandé

DATEDIFF(MINUTE, UserData.Orders.SetupStart, UserData.Orders.StartTime)  AS[SetupTime]

DEVIENT

DATEDIFF(MINUTE, UserData.Orders.SetupStart, DATEADD(MINUTE,-30,(UserData.Orders.StartTime))) AS[SetupTime]

De plus, vous pouvez exécuter deux fonctions DATEDIFF et les ajouter ensemble:

DATEDIFF(MINUTE, UserData.Orders.SetupStart, UserData.Orders.LunchStartTime)  
+ DATEDIFF(MINUTE, UserData.Orders.LunchEndTime, UserData.Orders.StartTime)  AS[SetupTime]

Si ces points de données n'existent pas, vous pouvez créer une table temporaire.

-1
Franz Stoneking 24 janv. 2017 à 16:56

C'est une douleur dans SQL Server, mais vous pouvez le faire:

(CASE WHEN CAST(o.SetupStart as time) > '14:00:00' OR
           CAST(o.StartTime as time) < '13:30:00'           
      THEN DATEDIFF(MINUTE, o.SetupStart, o.StartTime)
      WHEN CAST(o.SetupStart as time) >= '13:30:00' AND
           CAST(o.StartTime as time) <= '14:00:00'
      THEN 0
      WHEN CAST(o.SetupStart as time) >= '13:30:00'
      THEN DATEDIFF(MINUTE,
                    CAST(CAST(o.SetupStart as DATE) as DATETIME) + CAST('14:00:00' as TIME),
                    o.StartTime
                   )
      WHEN CAST(o.StartTime as Time) <= '14:00:00'
      THEN DATEDIFF(MINUTE,
                    o.SetStart,
                    CAST(CAST(o.StartTime as DATE) as DATETIME) + CAST('13:30:00' as TIME)
                   )
      ELSE DATEDIFF(MINUTE, o.SetupStart, o.StartTime) - 30
    END)  AS [SetupTime]

Cela gère les cas suivants:

  • Les heures sont à la fois avant ou après le déjeuner.
  • Les temps sont tous les deux pendant le déjeuner.
  • SetupStart est pendant le déjeuner.
  • StartTime est pendant le déjeuner.
  • SetupStart est avant le déjeuner et StartTime est après le déjeuner
0
Gordon Linoff 25 janv. 2017 à 02:33
declare 
     @ldt_from  time    =   '13:00'
    ,@ldt_to    time    =   '14:00';

--  dummy data prepare
;with [my_setups] as
(
    select [SetupStart] = cast('2017-01-23 13:56:42.000' as datetime), [StartTime] = cast('2017-01-23 14:41:06.000' as datetime)
    union all
    select [SetupStart] = cast('2017-01-23 12:45:00.000'  as datetime), [StartTime] = cast('2017-01-23 12:46:00.000' as datetime)
)
--  end dummy data prepare
select [minutes] =
      datediff(mi, [SetupStart],  [StartTime])
    - datediff(day, [SetupStart],  [StartTime]) * (datediff(mi, @ldt_from,  @ldt_to))
    + case when cast([SetupStart] as time) between @ldt_from and @ldt_to or cast([StartTime] as time) between @ldt_from and @ldt_to then
      datediff
      (
         mi
        ,case when cast([SetupStart] as time) > @ldt_from   then cast([SetupStart]  as time) else @ldt_from end
        ,case when cast([StartTime]  as time) < @ldt_to     then cast([StartTime]   as time) else @ldt_to   end     
      )
      else 0 end        
from 
    [my_setups]
0
Juozas 24 janv. 2017 à 20:31