如何在SQL中找到同一列中两个日期之间的差异 [英] How to find the difference between two dates in same column in SQL
问题描述
我有如下数据。
CREATE TABLE #TaskTable
（TaskId INT，CategoryID INT，[Type] INT，MailBoxId INT，[Date] DateTime，StatusName VarChar（50），StatusId INT）;
GO
INSERT INTO #TaskTable VALUES（3,1,5,1，'2017-06-05'，'PA'，189）;
INSERT INTO #TaskTable VALUES（3,1,5,1，'2017-06-07'，'IND'，199）;
INSERT INTO #TaskTable VALUES（ 8,1,8,1，'2017-06-18'，'PA'，189）;
INSERT INTO #TaskTable VALUES（8,1,8,1，'2017-06- 19'，'IND'，199）;
INSERT INTO #TaskTable VALUES（8,1,8,1，'2017-06-19'，'WFI'，190）;
INSERT INTO #TaskTable VALUES（8,1,8,1，'2017-06-19'，'CP'，191）;
INSERT INTO #TaskTable VALUES（3,1,5,1，'2017-06-19'，'WFI'，190）;
INSERT INTO #TaskTable VALUES（3,1,5,1） ，'2017-06-20'，'CP'，191）;
INSERT INTO #TaskTable VALUES（10,1,8,1，'2017-06-19'，'PA'，189）;
INSERT INTO #TaskTable VALUES（10,1,8,1，'2017-06-20'，'IND'，199）;
INSERT INTO #TaskTable VALUES（10,1,8,1， '2017-06-22'，'WFI'，190）;
INSERT INTO #TaskTable VALUES（3,1,5,1，'2017-06- 21'，'CLD'，197）;
INSERT INTO #TaskTable VALUES（8,1,8,1，'2017-06-21'，'CLD '，197）;
INSERT INTO #TaskTable VALUES（10,1,8,1，'2017-06-25'，'CLD'，197）;
INSERT INTO #TaskTable VALUES（11,1,5,1，'2017-06-21'，'PA'，189）;
INSERT INTO #TaskTable VALUES（11,1,5,1，'2017-06-22'，'IND'，199）;
INSERT INTO #TaskTable VALUES（11,1， 5,1，'2017-06-23'，'CLD'，197）;
Hi,
I have a following data as below.
CREATE TABLE #TaskTable
(TaskId INT, CategoryID INT, [Type] INT, MailBoxId INT, [Date] DateTime, StatusName VarChar(50), StatusId INT);
GO
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-05', 'PA', 189);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-07', 'IND', 199);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-18', 'PA', 189);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'IND', 199);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'WFI', 190);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'CP', 191);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-19', 'WFI', 190);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-20', 'CP', 191);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-19', 'PA', 189);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-20', 'IND', 199);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-22', 'WFI', 190);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-21', 'CLD', 197);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-21', 'CLD', 197);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-25', 'CLD', 197);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-21', 'PA', 189);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-22', 'IND', 199);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-23', 'CLD', 197);
TaskId CategoryId TypeId MailBoxId StartTime StatusName StatusTypeId
3 1 5 1 2017-06-05 PA 189
3 1 5 1 2017-06-07 IND 199
8 1 8 1 2017-06-18 PA 189
8 1 8 1 2017-06-19 IND 199
8 1 8 1 2017-06-19 WFI 190
8 1 8 1 2017-06-19 CP 191
3 1 8 1 2017-06-19 WFI 190
3 1 8 1 2017-06-20 CP 191
10 1 8 1 2017-06-19 PA 189
10 1 8 1 2017-06-20 IND 199
10 1 8 1 2017-06-22 WFI 190
3 1 5 1 2017-06-21 CLD 197
8 1 8 1 2017-06-21 CLD 197
10 1 8 1 2017-06-25 CLD 197
11 1 5 1 2017-06-21 PA 189
11 1 5 1 2017-06-22 IND 199
11 1 5 1 2017-06-23 CLD 197
我的要求是获取如下数据。
My requirement is to get data as below.
CategoryId TypeId MailBoxId PA-IND IND-CLD IND-WFI WFI-CLD
1 5 1 1 1.5 0 0
1 8 1 1 1.00 1 2
这里PA-IND，IND-CLD，IND-WFI，WFI-CLD是相同类型的平均天数。
是吗可以实现的。请帮忙。
我的尝试：
绘制数据 - [我需要的结果]是否可以通过枢轴来完成。
我尝试过如下枢轴示例。
Here PA-IND,IND-CLD,IND-WFI,WFI-CLD is the average days taken for same type.
Is it achievable. Please help.
What I have tried:
Plotting data - [result i require] is it can be done by pivot.
I tried sample of pivot as below.
CREATE TABLE yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59)
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
Below is the insert query for data into table.
CREATE TABLE #TaskTable
(TaskId INT, CategoryID INT, [Type] INT, MailBoxId INT, [Date] DateTime, StatusName VarChar(50), StatusId INT);
GO
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-05', 'PA', 189);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-07', 'IND', 199);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-18', 'PA', 189);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'IND', 199);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'WFI', 190);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-19', 'CP', 191);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-19', 'WFI', 190);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-20', 'CP', 191);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-19', 'PA', 189);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-20', 'IND', 199);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-22', 'WFI', 190);
INSERT INTO #TaskTable VALUES(3, 1, 5, 1, '2017-06-21', 'CLD', 197);
INSERT INTO #TaskTable VALUES(8, 1, 8, 1, '2017-06-21', 'CLD', 197);
INSERT INTO #TaskTable VALUES(10, 1, 8, 1, '2017-06-25', 'CLD', 197);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-21', 'PA', 189);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-22', 'IND', 199);
INSERT INTO #TaskTable VALUES(11, 1, 5, 1, '2017-06-23', 'CLD', 197);
Thanks in advance
推荐答案
假设我正确地理解了你的问题;您使用的是相对较新版本的Microsoft SQL Server;并且状态转换集是固定的;这样的事情应该有效：
Assuming I've understood your question correctly; that you're using a relatively recent version of Microsoft SQL Server; and that the set of status transitions is fixed; something like this should work:
WITH cteTasks As
(
SELECT
CategoryID,
Type,
MailBoxId,
StatusName As FromStatus,
LEAD(StatusName) OVER (PARTITION BY CategoryID, Type, MailBoxId ORDER BY [Date]) As ToStatus,
[Date] As StartDate,
LEAD([Date]) OVER (PARTITION BY CategoryID, Type, MailBoxId ORDER BY [Date]) As EndDate
FROM
#TaskTable
),
cteTime As
(
SELECT
CategoryID,
Type,
MailBoxId,
FromStatus + '-' + ToStatus As Status,
DateDiff(minute, StartDate, EndDate) / 1440. As DaysTaken
FROM
cteTasks
WHERE
ToStatus Is Not Null
)
SELECT
CategoryID,
Type,
MailBoxId,
[PA-IND],
[IND-CLD],
[IND-WFI],
[WFI-CLD],
[CLD-PA],
[CLD-WFI],
[WFI-CP],
[CP-PA],
[CP-CLD]
FROM
cteTime As T
PIVOT
(
SUM(DaysTaken)
FOR Status In ([PA-IND], [IND-CLD], [IND-WFI], [WFI-CLD], [CLD-PA], [CLD-WFI], [WFI-CP], [CP-PA], [CP-CLD])
) As P
;
使用PIVOT和UNPIVOT | Microsoft Docs [ ^ ]
LEAD（Transact-SQL）| Microsoft Docs [ ^ ]
使用您的示例数据，输出为：
Using PIVOT and UNPIVOT | Microsoft Docs[^]
LEAD (Transact-SQL) | Microsoft Docs[^]
Using your sample data, the output is:
CategoryID Type MailBoxId PA-IND IND-CLD IND-WFI WFI-CLD CLD-PA CLD-WFI WFI-CP CP-PA CP-CLD
-----------------------------------------------------------------------------------------------------------------------------
1 5 1 3 1 12 NULL 0 NULL 1 NULL 1
1 8 1 2 1 0 3 NULL 1 0 0 NULL
这篇关于如何在SQL中找到同一列中两个日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
