SQL Server 2008查询，每个状态下的时间

Question

我想知道有没有人能帮我解决我正在处理的问题。我正在尝试从我的呼叫活动表中收集“每个状态的时间”的信息。

我需要设置3天的时间范围：<3天，4-5天，6+天，返回每个CallID在每个状态下花费的天数。

我遇到的问题是，我需要从下表中确定何时发生了状态更改。此表记录呼叫的所有活动，即更改的客户详细信息，而不仅仅是状态更改的时间。

抱歉，如果这是不清楚，让我知道如果你需要进一步的细节。

我使用的是SQL Server 2008。下面是我正在使用的表和相关的值：

CREATE TABLE Activity ( CallID varchar(30), Call_Date datetime, [User] varchar(30), Status varchar(10) );

INSERT INTO Activity VALUES (366,'2013/09/27 12:24:33',13,9);
INSERT INTO Activity VALUES (366,'2013/09/28 17:36:14',13,9);
INSERT INTO Activity VALUES (366,'2013/09/29 07:29:18',13,10);
INSERT INTO Activity VALUES (366,'2013/09/30 06:22:12',13,-1);
INSERT INTO Activity VALUES (367,'2013/09/27 12:13:16',9,6);
INSERT INTO Activity VALUES (367,'2013/09/27 12:25:03',9,6);
INSERT INTO Activity VALUES (367,'2013/09/29 12:25:29',9,6);
INSERT INTO Activity VALUES (367,'2013/09/30 12:45:55',9,7);
INSERT INTO Activity VALUES (367,'2013/10/01 12:46:04',9,8);
INSERT INTO Activity VALUES (367,'2013/10/02 15:12:27',9,-1);
INSERT INTO Activity VALUES (368,'2013/08/01 15:09:01',5,10);
INSERT INTO Activity VALUES (368,'2013/08/02 14:11:20',5,13);
INSERT INTO Activity VALUES (368,'2013/08/04 16:41:11',5,13);
INSERT INTO Activity VALUES (368,'2013/08/05 01:12:56',5,-1); 

所需输出1:例如，如果CallID 35931花了2天时间从状态1变为状态2，则<3 column中的计数将增加2天

Status  <3 Days  4-5 days  6+ Days
------  -------  --------  -------
1       10       3         1
2       8        1         2
3       5        3         1

我停留在第一个阶段，试图识别状态发生变化的行，而忽略了其余的行。我正在处理一个子查询，它为每次状态更改选择截止日期。它带回了负值。请看这里：

select CallID, T2.[status], Call_Date,
sum(datediff(dd, nextDate, [Call_Date]) - (datediff(wk, nextDate, [Call_Date]) * 2) -
case when datepart(wk, nextDate) = 1 then 1 else 0 end +
case when datepart(wk, [Call_Date]) = 7 then 1 else 0 end) as TotalDays

from (select *,
(select MAX( T0.[Call_Date])
from [Activity] T0
where T0.[Call_Date] > T1.[Call_Date] and
T0.CallID = T1.CallID
) as nextDate
from [Activity] T1
) T2

where T2.[status] <> '-1'
group by Call_Date, T2.[status], CallID

提前感谢您的帮助。

Answer 1

首先，我认为您只需要具有每个id和status的最小日期的行，因为它们将显示状态更改。这可以通过CTE和使用ROW_NUMBER来完成。

然后，您应该以在同一记录上具有旧状态日期和新状态日期的方式连接结果。在第一次时，第一个状态会有空值。

;WITH CallsCTE AS
(
    SELECT CallId,
                   Call_Date,
                   Status,
                   ROW_NUMBER() OVER(PARTITION BY CallId, Status ORDER BY Call_Date) AS rn
    FROM Activity
),
StatusChangesCTE AS
(
    SELECT CallID,
                   Call_Date,
                   Status
    FROM CallsCTE
    WHERE rn = 1
)
SELECT Sold.*,
               Snew.*
FROM StatusChangesCTE Snew
            LEFT JOIN StatusChangesCTE Sold
            ON Snew.CallID = Sold.CallID
            AND Sold.Call_Date = (SELECT MAX(Call_Date) FROM StatusChangesCTE WHERE CallID = Sold.CallID AND Call_Date < Snew.Call_Date)

我认为您可以使用上面的方法，因为您可以在Snew.Call_Date和Sold.Call_Date上使用DateDiff来查找状态更改所需的时间。

如果你还需要任何帮助，请告诉我。