두 날짜 사이의 분 계산 시간 차이 (만 근무 시간)

나는 데이터베이스 내에서 이벤트에 대해 "활성 분"의 수를 계산해야합니다. 시작 시간은 잘 알려져있다.

합병증이 활성 분 만 작업 일 동안 계산해야한다는 것입니다 - 월 - 금 오전 9시에서 오후 6시 반 사이, 주말을 제외하고 (알려진) 휴가 일의 목록

시작 또는 "현재"시간 밖에 근무 시간 수 있지만, 여전히 근무 시간이 계산됩니다.

이는 SQL 서버 2005, 그래서 T-SQL 또는 어셈블리 관리하는이 사용될 수있다.

해결법

==============================
1.당신이 그것을 순수 SQL을 수행 할 경우 여기에 한 가지 방법이다

당신이 그것을 순수 SQL을 수행 할 경우 여기에 한 가지 방법이다
```
CREATE TABLE working_hours (start DATETIME, end DATETIME);
```
자 ~, 가산 기간에 연간 250 개 행을 근무 시간 테이블을 채 웁니다.

당신은 시간을 시작하고 간단한 쿼리를 시간을 종료합니다 (@event_start, @event_end) 이벤트가있는 경우
```
SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end
```
충분합니다.

반면에 이벤트가 근무 시간 및 / 또는 끝을 시작하면 쿼리가 더 복잡
```
SELECT SUM(duration) 
FROM 
(
   SELECT SUM(end-start) as duration
   FROM working_hours
   WHERE start >= @event_start AND end <= @event_end
UNION ALL
   SELECT end-@event_start
   FROM working_hours
   WHERE @event_start between start AND end
UNION ALL
   SELECT @event_end - start
   FROM working_hours
   WHERE @event_end between start AND end
) AS u
```
노트:

편집하다: MSSQL에서는 위의 각 공제 (분)을 얻을 수 DATEDIFF (마일, 시작, 종료)를 사용할 수 있습니다.

==============================

2.미치게하다의 훌륭한 출발점을 사용하여, 여기에 SQL 서버 2012에 대한 TSQL 구현입니다.

미치게하다의 훌륭한 출발점을 사용하여, 여기에 SQL 서버 2012에 대한 TSQL 구현입니다.

첫 번째 SQL은 주말 및 공휴일을 제외한 우리의 일의 날짜와 시간에 테이블을 채 웁니다

declare @dteStart date
declare @dteEnd date
declare @dtStart smalldatetime
declare @dtEnd smalldatetime
Select @dteStart = '2016-01-01'
Select @dteEnd = '2016-12-31'

CREATE TABLE working_hours (starttime SMALLDATETIME, endtime SMALLDATETIME);

while @dteStart <= @dteEnd
BEGIN
   IF    datename(WEEKDAY, @dteStart) <> 'Saturday' 
     AND DATENAME(WEEKDAY, @dteStart) <> 'Sunday'
     AND @dteStart not in ('2016-01-01' --New Years
                          ,'2016-01-18' --MLK Jr
                          ,'2016-02-15' --President's Day
                          ,'2016-05-30' --Memorial Day
                          ,'2016-07-04' --Fourth of July
                          ,'2016-09-05' --Labor Day
                          ,'2016-11-11' --Veteran's Day
                          ,'2016-11-24' --Thanksgiving
                          ,'2016-11-25' --Day after Thanksgiving
                          ,'2016-12-26' --Christmas
                          )
      BEGIN
        select @dtStart = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),8,0) --8:00am
        select @dtEnd   = SMALLDATETIMEFROMPARTS(year(@dteStart),month(@dteStart),day(@dteStart),17,0) --5:00pm
        insert into working_hours values (@dtStart,@dtEnd)
      END
   Select @dteStart = DATEADD(day,1,@dteStart)
END

이제 여기 INT로 분을 반환하기 위해 노력 논리는 다음과 같습니다

declare @event_start datetime2
declare @event_end datetime2
select @event_start = '2016-01-04 8:00'
select @event_end = '2016-01-06 16:59'

SELECT SUM(duration) as minutes
FROM 
(
   SELECT DATEDIFF(mi,@event_start,@event_end) as duration
   FROM working_hours
   WHERE @event_start >= starttime 
     AND @event_start <= endtime
     AND @event_end <= endtime
UNION ALL
   SELECT DATEDIFF(mi,@event_start,endtime)
   FROM working_hours
   WHERE @event_start >= starttime 
     AND @event_start <= endtime
     AND @event_end > endtime
UNION ALL
   SELECT DATEDIFF(mi,starttime,@event_end)
   FROM working_hours
   WHERE @event_end >= starttime 
     AND @event_end <= endtime
     AND @event_start < starttime
UNION ALL
   SELECT SUM(DATEDIFF(mi,starttime,endtime))
   FROM working_hours
   WHERE starttime > @event_start
     AND endtime < @event_end
) AS u

이 올바르게 세 9시간 작업 일의 수줍음 1 분 반환

==============================
3.나는 매우 비슷한 질문에 대한 답을 찾고 여기 온 - 내가 2 날짜가 주말을 제외하고 08 : 30 18:00 밖에서 시간을 제외 사이의 분을 얻을 필요가 있었다. 주변 해킹의 조금 후에, 나는 내가 그것을 정렬 있다고 생각. 아래는 내가 한 방법이다. 생각은 환영합니다 - 아는 사람, 어쩌면 언젠가는이 사이트에 가입 할 수 있습니다 :

나는 매우 비슷한 질문에 대한 답을 찾고 여기 온 - 내가 2 날짜가 주말을 제외하고 08 : 30 18:00 밖에서 시간을 제외 사이의 분을 얻을 필요가 있었다. 주변 해킹의 조금 후에, 나는 내가 그것을 정렬 있다고 생각. 아래는 내가 한 방법이다. 생각은 환영합니다 - 아는 사람, 어쩌면 언젠가는이 사이트에 가입 할 수 있습니다 :
```
create function WorkingMinutesBetweenDates(@dteStart datetime, @dteEnd datetime)
returns int
as
begin

declare @minutes int
set @minutes = 0

while @dteEnd>=@dteStart
    begin

        if  datename(weekday,@dteStart) <>'Saturday' and  datename(weekday,@dteStart)<>'Sunday'
            and (datepart(hour,@dteStart) >=8 and datepart(minute,@dteStart)>=30 )
            and (datepart(hour,@dteStart) <=17)

            begin
                set @minutes = @minutes + 1
            end     

        set @dteStart = dateadd(minute,1,@dteStart)
    end

return @minutes
end
go
```

==============================

4.나는 미치게하다 게시하고 좋은 출발 무슨 작업을 시작했다. 나는이 SQL 서버는 테스트 및 모든 시간이 촬영되는 것을 발견했다. 나는 이벤트가 시작하고 같은 날 종료 할 때 문제가 주로 생각. 이 솔루션은 나를 위해 충분히 잘 작동하는 것 같군

나는 미치게하다 게시하고 좋은 출발 무슨 작업을 시작했다. 나는이 SQL 서버는 테스트 및 모든 시간이 촬영되는 것을 발견했다. 나는 이벤트가 시작하고 같은 날 종료 할 때 문제가 주로 생각. 이 솔루션은 나를 위해 충분히 잘 작동하는 것 같군

CREATE TABLE [dbo].[working_hours](
[wh_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[wh_starttime] [datetime] NULL,
[wh_endtime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
[wh_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,           ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

가다

CREATE FUNCTION [dbo].[udFWorkingMinutes] 
(
@startdate DATETIME
,@enddate DATETIME
)
RETURNS INT
AS
BEGIN


DECLARE @WorkingHours INT
SET @WorkingHours = 
(SELECT 
CASE WHEN COALESCE(SUM(duration),0) < 0 THEN 0 ELSE SUM(Duration) 
END AS Minutes
FROM 
(
    --All whole days
   SELECT ISNULL(DATEDIFF(mi,wh_starttime,wh_endtime),0) AS Duration
   FROM working_hours
   WHERE wh_starttime >= @startdate AND wh_endtime <= @enddate 
   UNION ALL
   --All partial days where event start after office hours and finish after office hours
   SELECT ISNULL(DATEDIFF(mi,@startdate,wh_endtime),0) AS Duration
   FROM working_hours
   WHERE @startdate > wh_starttime AND @enddate >= wh_endtime 
   AND (CAST(wh_starttime AS DATE) = CAST(@startdate AS DATE))
   AND @startdate < wh_endtime
   UNION ALL
   --All partial days where event starts before office hours and ends before day end
   SELECT ISNULL(DATEDIFF(mi,wh_starttime,@enddate),0) AS Duration
   FROM working_hours
   WHERE @enddate < wh_endtime 
   AND @enddate >= wh_starttime
   AND @startdate <= wh_starttime 
   AND (CAST(wh_endtime AS DATE) = CAST(@enddate AS DATE))
   UNION ALL  
    --Get partial day where intraday event
   SELECT ISNULL(DATEDIFF(mi,@startdate,@enddate),0) AS Duration
   FROM working_hours
   WHERE @startdate > wh_starttime AND @enddate < wh_endtime 
   AND (CAST(@startdate AS DATE)= CAST(wh_starttime AS DATE))
   AND (CAST(@enddate AS DATE)= CAST(wh_endtime AS DATE))
 ) AS u)

 RETURN @WorkingHours
END
GO

할 남아 ALLS 같은 뭔가 작업 시간 테이블을 채우는 것입니다

;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,'2014-01-01 9:00:00 AM') = 1 THEN '2014-01-01 9:00:00 AM' 
ELSE DATEADD(Day,DATEDIFF(Day,0,'2014-01-01 9:00:00 AM')+1,0) END AS      myStartDate,
CASE WHEN DATEPART(Day,'2014-01-01 5:00:00 PM') = 1 THEN '2014-01-01 5:00:00 PM' 
ELSE DATEADD(Day,DATEDIFF(Day,0,'2014-01-01 5:00:00 PM')+1,0) END AS myEndDate
UNION ALL
SELECT DATEADD(Day,1,myStartDate), DATEADD(Day,1,myEndDate)
FROM cte
WHERE DATEADD(Day,1,myStartDate) <=  '2015-01-01'
)
INSERT INTO working_hours
SELECT myStartDate, myEndDate
FROM cte
OPTION (MAXRECURSION 0)

delete from working_hours where datename(dw,wh_starttime) IN ('Saturday', 'Sunday')

--delete public holidays

delete from working_hours where CAST(wh_starttime AS DATE) = '2014-01-01'

내 첫 번째 게시물! 자비합니다.

==============================
5.전 세계적으로, 당신은 필요한 것 :

전 세계적으로, 당신은 필요한 것 :

(: 정말 가능성이 그 것이다 참고이 여러 일을 지속 할 수있는 이벤트가 가정합니다?)

다른 방법은 해당 기간 동안 활성 자체를 발견 할 때마다 (초 또는 분)을 새기는 경우 해당 시간 계산되어야하는지 여부를 매번 체크 이벤트, 내부 클록, 및 단위를 가질 것이다. 이것은 여전히 도우미 테이블을 필요로 적은 감사 (아마도)이 될 것입니다.

from https://stackoverflow.com/questions/3296923/calculate-time-difference-only-working-hours-in-minutes-between-two-dates by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 나는 T-SQL 그룹으로 어떻게 사용합니까 (0)	2020.07.07
[SQL] 어떻게 SQL Server의 평등을위한 두 개의 열을 비교합니까? (0)	2020.07.07
[SQL] ORA-01861 : 리터럴이 형식 문자열과 일치하지 않습니다 (0)	2020.07.06
[SQL] 만 MYSQL DATEDIFF의 시간을 보여 (0)	2020.07.06
[SQL] "DISTINCT SELECT는"서로 다른 경우를 무시 (0)	2020.07.06

복붙노트

[SQL] 두 날짜 사이의 분 계산 시간 차이 (만 근무 시간)

두 날짜 사이의 분 계산 시간 차이 (만 근무 시간)

해결법

1.당신이 그것을 순수 SQL을 수행 할 경우 여기에 한 가지 방법이다

2.미치게하다의 훌륭한 출발점을 사용하여, 여기에 SQL 서버 2012에 대한 TSQL 구현입니다.

5.전 세계적으로, 당신은 필요한 것 :

'SQL' 카테고리의 다른 글

티스토리툴바