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