计算不同行的两个日期之间的平均值 [英] Calculate Average between two dates of different rows

问题描述

我有下表

  BIDID         的AppID     其appStatus       时间

    23390 16
在回顾中 2017-07-03 

    23390 16
已批准 2017-09-03 

    23390 16
在审核中    2017-10-11 

    23390 16
已批准 2017-12-11 

    23390 16
已批准 2017-14-11 

我想根据以下逻辑计算时差



首先查找In Review状态和然后获得下一个批准状态，然后计算时差。然后获取下一个In Review状态，然后下一个Approved并计算时差。然后得到一个平均值。



所以在这种情况下我要找的是&bbsp; b

说 



    2017年7月3日  - 2017-09-03  - 2天b
       二零一七年十月一十一日  - 2017-12-11    -2天



最后批准被忽略，因为之前没有匹配的In评论



   然后得到总平均值（2 + 2）/ 2 = 2

$
有人可以告诉我如何实现这一目标。



谢谢你

解决方案

< blockquote>

您好Sharepoint99，

您可以使用ROW_NUMBER（）函数来实现这一目标。

创建表BID 
（
 BIDID varchar（64），
 AppID varchar（30），
 AppStatus varchar（128），
 [时间]日期
） 
 
插入BID值
（'23390'，'16'，'In Review'，'2017-03-07'），
（'23390'，'16 '，'已批准'，'2017-03-09'），
（'23390'，'16'，'审核中'，'2017-11-10'），
（'23390' ，'16'，'已批准'，'2017-11-12'），
（'23390'，'16'，'已批准'，'2018-11-14'）
 GO 
 
  - 查询
; WITH CTE AS 
（
 SELECT 
 BIDID，AppID，AppStatus，[Time]，
 ROW_NUMBER（）over（按[时间]按BIDID，AppID，AppStatus顺序分区）AS RN 
来自BID 
）
 SELECT 
 T.BIDID，T.AppID，
 --T。[时间] AS ReviewTime，T1。[时间] AS ApprovedTime 
 AVG（DATEDIFF（DAY，T。[Time]，T1。[Time]））AS [总平均值] 
来自CTE T 
 INNER JOIN CTE T1 ON T.BIDID = T1.BIDID 
 AND T.AppID = T1.AppID 
 AND T.AppStatus ='In Review'
 AND T1.AppStatus ='Approved'
 AND T.RN = T1 .RN 
 GROUP BY T.BIDID，T.AppID 

最好的问候，

将

I have the below table

 BIDID          AppID     AppStatus       Time
    23390 16 In Review 2017-07-03 
    23390 16 Approved 2017-09-03 
    23390 16 In Review   2017-10-11 
    23390 16 Approved 2017-12-11 
    23390 16 Approved 2017-14-11 

I want to calculate the time difference according to the following logic

First find the In Review status and then get the next approved status and then calculate the time difference. Then get the next In Review status and then next Approved and calculate the time difference. Then get an average.

So in this case what I am looking for is 

Say 

    2017-07-03  - 2017-09-03  - 2 days
        2017-10-11  - 2017-12-11   -2 days

Last Approved is ignored because there was no matching In review before it

    Then get total average (2 + 2) / 2 = 2

Can someone please tell me how I can achieve this.

Thanks

解决方案

Hi Sharepoint99,

You could use ROW_NUMBER() function to achieve that.

 create table BID
 (
 BIDID varchar(64),
 AppID varchar(30),
 AppStatus varchar(128),
 [Time] date
)

insert into BID values
('23390','16','In Review','2017-03-07'),
('23390','16','Approved','2017-03-09'),
('23390','16','In Review','2017-11-10'),
('23390','16','Approved','2017-11-12'),
('23390','16','Approved','2018-11-14')
GO

--Query
;WITH CTE AS
(
SELECT 
BIDID,AppID,AppStatus,[Time],
ROW_NUMBER() over (partition by BIDID,AppID,AppStatus order by [Time]) AS RN
FROM BID
)
SELECT 
T.BIDID,T.AppID,
--T.[Time] AS ReviewTime,T1.[Time] AS ApprovedTime
AVG(DATEDIFF(DAY,T.[Time],T1.[Time])) AS [total average]
FROM CTE T
INNER JOIN CTE T1 ON T.BIDID=T1.BIDID 
                  AND T.AppID=T1.AppID
				  AND T.AppStatus='In Review'
				  AND T1.AppStatus='Approved'
				  AND T.RN=T1.RN
GROUP BY T.BIDID,T.AppID

Best Regards,

Will

这篇关于计算不同行的两个日期之间的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！