SQL 시간이 여러 중복 간격에서 경과 찾을 수

MSSQL 또는 DB2 또는 Oracle을 사용하지 않습니다. 아니 CTE. 중첩 술어 없습니다. 어떤 간격 데이터 유형이 없습니다. 상황 : 차량에 작업이 될 때까지 시작할 수 없습니다 수리하기 작업에 주문한 모든 부품이 접수되었습니다. 일부는 사전 수리의 시작으로 여러 번 주문할 수 있습니다. 우리는 "부품 보유"차량에 있던되는 시간을 추출해야

그래서 아이디 = 1로 확인 된 차량 부품 주문 (D1) 및 (4 개) 다른 경우에 (D2)받은

    ID     d1     d2
     1     8/1    8/8
     1     8/2    8/6
     1     8/12   8/14
     1     8/3    8/10

 8/1                             8/8
  d1                              d2   
  |-------------------------------|  
         8/2             8/6                    8/12      8/14                  
         d1               d2                     d1        d2     
          |---------------|                      |----------|    
                   8/3                 8/10
                   d1                    d2
                   |---------------------|   
 8/1                                                       8/14
  |---------------------------------------------------------|  = 13 days
                                        8/10    8/12
  |--------------------------------------|    +  |----------|  = parts hold  = 11 days

위에서 본 바와 같이, 대기 시간은 작업을 시작합니다 (같은 8/1 가정 차량이) 작업 가능했다있는 날짜 13 일이었다. 부품을 기다리고 소요 된 실제 시간은 숫자 인 11 일이었다 우리는 데이터에서 파생 할 필요가있다. 실제 날짜 데이터는 우리가 시간을 추출합니다있는 타임 스탬프 것 우리는 표현의 단순화를 위해이 샘플 데이터에서 날짜를 사용했다. 우리는 (안 PSM하지 UDF하지 커서) 기반 솔루션을 세트를 생성하는 데 어려움을 겪고있다. TIA

해결법

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

1.이 SQL 문은 (t는 sampe 테이블의 테이블 이름입니다) 원하는 것을 얻을 것 같다 :

이 SQL 문은 (t는 sampe 테이블의 테이블 이름입니다) 원하는 것을 얻을 것 같다 :

SELECT
   d.id, 
   d.duration, 
   d.duration - 
   IFNULL(
      ( SELECT Sum( timestampdiff( SQL_TSI_DAY, 
                                   no_hold.d2, 
                                   ( SELECT min(d1) FROM t t4 
                                     WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 )))
        FROM ( SELECT DISTINCT id, d2 FROM t t1 
               WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2, 1, 0 ) ) 
                       FROM t t2 WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0 
             And d2 <> ( select max( d2 ) from t t3 where t3.id = t1.id )) no_hold
        WHERE no_hold.id = d.id ),
      0 ) "parts hold"
FROM 
   ( SELECT id, timestampdiff( SQL_TSI_DAY, min( d1 ), max( d2 ) ) duration
     FROM t GROUP BY id ) d

외부 쿼리는 수리 작업의 지속 시간을 가져옵니다. 복잡한 서브 쿼리는 일의 총 수는 부품 기다리지 않고 계산합니다. 이것은 차량 부품을 기다리고, 그리고 다시 부품 대기 시작 때까지의 일 수를 계산하지 않는 시작 날짜를 위치하면됩니다 :

// 1) The query for finding the starting dates when the vehicle is not waiting for parts, 
// i.e. finding all d2 that is not within any date range where the vehicle is waiting for part.
// The DISTINCT is needed to removed duplicate starting "no hold" period.

SELECT DISTINCT id, d2 
FROM t t1
WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2, 1, 0 ) ) from t t2 
        WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0 AND 
      d2 <> ( SELECT max( d2 ) FROM t t3 WHERE t3.id = t1.id ) )

// 2)는 차량이 부분을 기다리고 다시 부분 // 대기 차량까지 위의 쿼리에서 날짜하지되는 일

timestampdiff( SQL_TSI_DAY, no_hold.d2, ( SELECT min(d1) FROM t t4 WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 ) )

위의 두 가지를 결합하고 이러한 모든 기간을 집계하면 차량 부품을 기다리고되지 않는 일 수 있습니다. 최종 질의는 외부 쿼리의 각 ID에 대한 계산 결과와 추가 조건을 추가한다.

이것은 아마도 많은 ID가 매우 큰 테이블에 대단히 효율적이지 않습니다. ID가 하나 또는 몇 가지로 제한하면 그것을 잘해야한다.

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

2.나는 일에 @ 알렉스 W의 쿼리를 가져올 수 없습니다. 그것이 (내가 테스트 할 수 있습니다) SQL 서버와 호환되도록 재를 많이 필요 그래서, 표준 SQL이 아니다. 그러나 그것은 저에게 나가에 확대 영감을 제공했다.

나는 일에 @ 알렉스 W의 쿼리를 가져올 수 없습니다. 그것이 (내가 테스트 할 수 있습니다) SQL 서버와 호환되도록 재를 많이 필요 그래서, 표준 SQL이 아니다. 그러나 그것은 저에게 나가에 확대 영감을 제공했다.

중단 대기의 모든 시대의 모든 시작 지점을 찾기 :

SELECT DISTINCT
    t1.ID,
    t1.d1 AS date,
    -DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d1) AS n
FROM Orders t1
LEFT JOIN Orders t2                   -- Join for any events occurring while this
    ON t2.ID = t1.ID                  -- is starting. If this is a start point,
    AND t2.d1 <> t1.d1                -- it won't match anything, which is what
    AND t1.d1 BETWEEN t2.d1 AND t2.d2 -- we want.
GROUP BY t1.ID, t1.d1, t1.d2
HAVING COUNT(t2.ID) = 0

그리고 엔드 포인트에 해당 :

SELECT DISTINCT
    t1.ID,
    t1.d2 AS date,
    DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d2) AS n
FROM Orders t1
LEFT JOIN Orders t2
    ON t2.ID = t1.ID
    AND t2.d2 <> t1.d2
    AND t1.d2 BETWEEN t2.d1 AND t2.d2
GROUP BY t1.ID, t1.d1, t1.d2
HAVING COUNT(t2.ID) = 0

여기서 n은 시간에 몇 가지 일반적인 시점 이후 일 수있다. 시작 포인트는 음의 값을 가지고, 최종 점은 양의 값을 갖는다. 우리가 사이의 일 수를 얻기 위해 그들을 추가 할 수 있도록이다.

span = end - start
span = end + (-start)
span1 + span2 = end1 + (-start1) + end2 + (-start2)

마지막으로, 우리는 단지 물건을 추가해야합니다 :

SELECT ID, SUM(n) AS hold_days
FROM (
   SELECT DISTINCT
       t1.id,
       t1.d1 AS date,
       -DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d1)  AS n
   FROM Orders t1
   LEFT JOIN Orders t2
      ON t2.ID = t1.ID
      AND t2.d1 <> t1.d1
      AND t1.d1 BETWEEN t2.d1 AND t2.d2
   GROUP BY t1.ID, t1.d1, t1.d2
   HAVING COUNT(t2.ID) = 0
   UNION ALL
   SELECT DISTINCT
       t1.id,
       t1.d2 AS date,
       DATEDIFF(DAY, (SELECT MIN(d1) FROM Orders), t1.d2) AS n
   FROM Orders t1
   LEFT JOIN Orders t2
      ON t2.ID = t1.ID
      AND t2.d2 <> t1.d2
      AND t1.d2 BETWEEN t2.d1 AND t2.d2
   GROUP BY t1.ID, t1.d1, t1.d2
   HAVING COUNT(t2.ID) = 0
   ORDER BY ID, date
) s
GROUP BY ID;

입력 테이블 (주문) :

ID   d1           d2
 1   2011-08-01   2011-08-08
 1   2011-08-02   2011-08-06
 1   2011-08-03   2011-08-10
 1   2011-08-12   2011-08-14
 2   2011-08-01   2011-08-03
 2   2011-08-02   2011-08-06
 2   2011-08-05   2011-08-09

산출:

ID   hold_days
 1          11
 2           8

또는, 저장 프로 시저와 함께이 작업을 수행 할 수 있습니다.

CREATE PROCEDURE CalculateHoldTimes
    @ID int = 0
AS
BEGIN
    DECLARE Events CURSOR FOR
    SELECT *
    FROM (
        SELECT d1 AS date, 1 AS diff
        FROM Orders
        WHERE ID = @ID
        UNION ALL
        SELECT d2 AS date, -1 AS diff
        FROM Orders
        WHERE ID = @ID
    ) s
    ORDER BY date;

    DECLARE @Events_date date,
            @Events_diff int,
            @Period_start date,
            @Period_accum int,
            @Total_start date,
            @Total_count int;

    OPEN Events;

    FETCH NEXT FROM Events
    INTO @Events_date, @Events_diff;

    SET @Period_start = @Events_date;
    SET @Period_accum = 0;
    SET @Total_start = @Events_date;
    SET @Total_count = 0;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Period_accum = @Period_accum + @Events_diff;

        IF @Period_accum = 1 AND @Events_diff = 1
            -- Start of period
            SET @Period_start = @Events_date;
        ELSE IF @Period_accum = 0 AND @Events_diff = -1
            -- End of period
            SET @Total_count = @Total_count +
                DATEDIFF(day, @Period_start, @Events_date);

        FETCH NEXT FROM Events
        INTO @Events_date, @Events_diff;
    END;

    SELECT
        @Total_start AS d1,
        @Events_date AS d2,
        @Total_count AS hold_time;
END;

그것을 함께 전화 :

EXEC CalculateHoldTimes 1;

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

3.

USE [DnnMasterShoraSystem]
GO
/****** Object:  StoredProcedure [dbo].[CalculateHoldTimes]    Script Date: 12/8/2014 1:36:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CalculateHoldTimes]
    @PID int    
AS
BEGIN      
CREATE TABLE #tblTemp(
    [ID] [int] NOT NULL,
    [PID] [int] NOT NULL,
    [BID] [int] NOT NULL,
    [Active] [bit] NULL,
    [WorkStartDate] [nvarchar](10) NULL,
    [WorkEndDate] [nvarchar](10) NULL,
    [jobStateID] [int] NULL,
    [RegisterType] [int] NULL,
    [RegisterState] [int] NULL,
    [En_time] [datetime] NULL,
    [Fa_time] [nvarchar](40) NULL,
    [Status] [nvarchar](100) NULL,
    [PortalId] [int] NULL,
    [ModuleId] [int] NULL,
    [UserId] [int] NULL,
    [BrName] [nvarchar](150) NULL,
    [BrCode] [nvarchar](20) NULL,
    [WorkEndDate_New] [nvarchar](10) NULL
) ON [PRIMARY]

insert into #tblTemp
select * from [dbo].[Shora.Personel_Branch_Copy] 
        where WorkStartDate is not null 
        --and [dbo].[ShamsiToMiladi](WorkStartDate) <GETDATE() 
        --and [dbo].[ShamsiToMiladi](WorkEndDate) <GETDATE() 
        and PID=@PID
        --and [dbo].[ShamsiToMiladi](WorkEndDate)<[dbo].[ShamsiToMiladi](@NewDate)
        order by WorkStartDate

DECLARE Events CURSOR FOR
    SELECT [dbo].[ShamsiToMiladi](WorkStartDate) AS StartDate,[dbo].[ShamsiToMiladi](WorkEndDate) AS EndDate
        FROM #tblTemp        
    ORDER BY StartDate;

--drop table #tblTemp

    DECLARE @SDate date,
            @EDate date,
            @Period_Start date,
            @Period_End date,
            @Total int,
            @OldSDate date,
            @OldEDate date


    OPEN Events;

    FETCH NEXT FROM Events
    INTO @SDate, @EDate;

    set @Total=0
    SET @Period_Start =@SDate
    set @Period_End=@EDate

    WHILE @@FETCH_STATUS = 0
    BEGIN      
    if @OldSDate>@Period_End
        begin
            set @Period_Start=@SDate            

            if @Period_End>=@Period_Start
            set @Total+=DATEDIFF(DAY,@Period_Start,@Period_End)
        end
    else if @SDate<@Period_End
        begin       
        set @Period_Start=@Period_Start     
            set @Total=DATEDIFF(DAY,@Period_Start,@Period_End)
        end

        set @OldSDate=@SDate 
        set @OldEDate=@EDate

        FETCH NEXT FROM Events
        INTO @SDate, @EDate;

        if  @Period_End<@EDate
        set @Period_End=@EDate

    END;

INSERT INTO [dbo].[PersonelDays]
           (PID
           ,[Total_Start]
           ,[Total_End]
           ,[Total_count])
     VALUES
           (@PID,           
            @Period_Start,
            @Period_End,
            @Total
           )

drop table #tblTemp
CLOSE Events
DEALLOCATE Events
END;

from https://stackoverflow.com/questions/7224792/sql-to-find-time-elapsed-from-multiple-overlapping-intervals by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL에서 선택 특정 행 번호 [중복] (0)	2020.07.04
[SQL] C #는 SQL 매개 변수 속기 (0)	2020.07.04
[SQL] 이 행의 위치를 포함하도록 업데이트 칼럼 (0)	2020.07.04
[SQL] 삽입 여기서 일하지 않는 무시 내 테이블에서 중복 레코드를 방지하는 방법 (0)	2020.07.04
[SQL] SQL : 연속 기록에 그룹으로 (0)	2020.07.04

복붙노트

[SQL] SQL 시간이 여러 중복 간격에서 경과 찾을 수

SQL 시간이 여러 중복 간격에서 경과 찾을 수

해결법

1.이 SQL 문은 (t는 sampe 테이블의 테이블 이름입니다) 원하는 것을 얻을 것 같다 :

2.나는 일에 @ 알렉스 W의 쿼리를 가져올 수 없습니다. 그것이 (내가 테스트 할 수 있습니다) SQL 서버와 호환되도록 재를 많이 필요 그래서, 표준 SQL이 아니다. 그러나 그것은 저에게 나가에 확대 영감을 제공했다.

3.

'SQL' 카테고리의 다른 글

티스토리툴바