그룹에 대해 이전에 채워진 날짜의 데이터 값으로 누락 된 날짜를 채 웁니다. 티켓이 열려있는 각 티켓에 대해 하루 말에

부서간에 전송되는 그림 헬프 데스크 티켓. 티켓이 열려있는 각 티켓에 대해 하루 말에 부서가 무엇인지 알고 싶습니다. 이 표에는 부서에 변경 사항이있는 각 티켓에 대한 각 티켓의 마지막 부서가 포함되어 있습니다 (티켓을 처음 연 날짜와 마감 날짜에 대한 행 포함). 데이터 테이블은 다음과 같습니다.

CREATE TABLE TicketAssigment (
    TicketId     INT NOT NULL,
    AssignedDate DATE NOT NULL,
    DepartmentId INT NOT NULL);

필요한 것은 Date로 정렬 된 이전 TicketAssigment 행의 DepartmentId를 사용하여 각 TicketId에 대해 누락 된 날짜를 채우는 것입니다.

TicketAssigment 행이 다음과 같은 경우 :

1, '1/1/2016', 123 -- Opened
1, '1,4,2016', 456 -- Transferred and closed
2, '1/1/2016', 25  -- Opened
2, '1/2/2016', 52  -- Transferred
2, '1/4/2016', 25  -- Transferred and closed

나는이 출력을 원한다 :

1, '1/1/2016', 123
1, '1/2/2016', 123
1, '1/3/2016', 123
1, '1/4/2016', 456
2, '1/1/2016', 25
2, '1/2/2016', 52
2, '1/3/2016', 52
2, '1/4/2016', 25

이것은 내가 필요한 것에 가깝게 보이지만 끝내기 위해 인내심이 없었으며 예상 계획 비용은 6 자리입니다.

SELECT  l.TicketId, c.Date, MIN(l.DepartmentId)
FROM    dbo.Calendar c
        OUTER APPLY (SELECT TOP 1 TicketId, DepartmentId FROM TicketAssigment WHERE AssignedDate <= c.Date ORDER BY AssignedDate DESC) l
WHERE   c.Date <= (SELECT MAX(AssignedDate) FROM TicketAssigment)
GROUP   BY l.TicketId, c.Date
ORDER   BY l.TicketId, c.Date;

LAG와 창 프레임을 사용 하여이 작업을 수행 할 수있는 방법이 있다고 생각하지만 잘 이해하지 못했습니다. 요구 사항을 충족시키는보다 효율적인 방법은 무엇입니까?

답변

LEAD()TicketId 파티션 내에서 다음 행을 얻는 데 사용하십시오 . 그런 다음 캘린더 테이블에 가입하여 사이의 모든 날짜를 가져옵니다.

WITH TAwithnext AS
(SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY TicketID ORDER BY AssignmentDate) AS NextAssignmentDate
 FROM TicketAssignment
)
SELECT t.TicketID, c.Date, t.DepartmentID
FROM dbo.Calendar c
JOIN TAwithnext t
    ON c.Date BETWEEN t.AssignmentDate AND ISNULL(DATEADD(day,-1,t.NextAssignmentDate),t.AssignmentDate)
;

캘린더 테이블을 얻는 모든 방법 …

답변

이것은 빠른 수행 방법입니다 (성능 또는 확장 성을 테스트하지 않았습니다)

-달력 테이블 만들기

-- borrowed from @Aaron's post http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3 
CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY);

INSERT dbo.Calendar(d) SELECT TOP (365)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20160101')
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number;

— 테스트 테이블을 만듭니다

CREATE TABLE dbo.TicketAssigment (
    TicketId     INT NOT NULL,
    AssignedDate DATE NOT NULL,
    DepartmentId INT NOT NULL);

--  truncate table dbo.TicketAssigment;

insert into dbo.TicketAssigment values (1   ,   '1-1-2016'  ,   123 )
insert into dbo.TicketAssigment values (1   ,   '1-4-2016'  ,   456 )
insert into dbo.TicketAssigment values (2   ,   '1-1-2016'  ,   25  )
insert into dbo.TicketAssigment values (2   ,   '1-2-2016'  ,   52  )
insert into dbo.TicketAssigment values (2   ,   '1-4-2016'  ,   25  )

— 원하는 출력을 얻기위한 쿼리

;with Cte as
(
  select TicketID,
         min(AssignedDate) minAD, -- This is the min date
         max(AssignedDate) maxAD  -- This is the max date
  from TicketAssigment
  group by TicketID
)
select Cte.TicketID,
       c.d as AssignedDate,

       ( -- Get DeptID
       select top(1) T.departmentID
       from dbo.TicketAssigment as T
       where T.TicketID = cte.TicketID and
             T.AssignedDate <= c.d
       order by T.AssignedDate desc
       ) as DepartmentID
from Cte
  left outer join dbo.Calendar as c
      on c.d between Cte.minAD and Cte.maxAD
    order by Cte.TicketID

여기에 이미지 설명을 입력하십시오

답변