SQL窗口函数/rownum/order by

Question

我正在尝试查看客户请求重新激活其Internet帐户的频率。

问题是，我们只捕获有限的一组数据进行分组。下面是我的数据集。

我尝试从第一次创建重新激活请求到第一次完成，一旦完成完成请求所需的天数，并计算在这段时间内发生的未完成和已发送状态的数量。

下面是示例数据以及表的sql的图像。希望有人能提供一点帮助。(使用SQL server 2005兼容性)

​

​

CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
)
;

INSERT INTO #temp
VALUES('64074558792','20160729','20160805','Re-Activattion','SENT');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160812','20160812','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Re-Activattion','COMP');
INSERT INTO #temp
VALUES('64074558792','20160811','20160813','Re-Activattion','N-CO');
INSERT INTO #temp
VALUES ('61030203647','20160427','20160427','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('61030203647','20160425','20160425','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('61030203647','20160422','20160422','Re-Activattion', 'N-CO');
INSERT INTO #temp
VALUES('61030203647','20170210','20170210','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('61030203688','20170409','20170210','Re-Activattion', 'SENT');
INSERT INTO #temp
VALUES('61030203699','20170409','20170210','De-Activattion', 'COMP');

Answer 1

我不确定这是否满足了你的要求，

select identifier,count(1) as cnt,sum(case when sn_status = 'N-CO' then 1 else 0 end) as non_com_cnt ,sum(case when sn_status = 'SENT' then 1 else 0 end) as
 sent_cnt, datediff(dd,min(case when sn_status = 'SENT' then createddate end),max(case when sn_status = 'COMP' then completeddate end)) as diff,min(case when sn_status = 'SENT' then createddate end) as start_date,max(case when sn_status = 'COMP' then completeddate end)  from #temp where sn_type = 'Re-Activattion' group by identifier;