TSQL选择where子句中的最小日期。

Here is a sample dataset:

这是一个样本数据集:

ID  DATE       STATUS        DURATIONTIME
1  30-08-2017  Done          220
1  30-08-2017  In progress   100
1  29-08-2017  In progress   50
1  28-08-2017  New           10
2  30-08-2017  Done          200
2  30-08-2017  In progress   100
2  29-08-2017  In progress   60
2  29-08-2017  New           50
2  28-08-2017  New           10

Result expected:

预期结果:

1    60
2    120

We want to calculate the sum of the duration times when status is not done and if status is In Progress, then only take the duration for the earliest date.

我们想要计算当状态未完成时的持续时间和如果状态正在进行时的持续时间的总和，然后只取最早的持续时间。

Here is the query I try:

以下是我尝试的问题:

SELECT        ID, SUM(DURATIONTIME) 
FROM          MYDB
WHERE         STATUS <> 'Done'
            AND DURATIONTIME = CASE
                WHEN (STATUS = 'In progress' AND DATE <> min(DATE)) 
                THEN 0
                ELSE DURATIONTIME END 
GROUP BY ID ASC

But the query fails... any idea how to rewrite this query?

但是查询失败…知道如何重写这个查询吗?

1 个解决方案

#1

Use DENSE_RANK window function

使用DENSE_RANK窗口函数

SELECT ID,
       Sum(DURATIONTIME)
FROM   (SELECT DENSE_RANK()OVER(partition BY ID, Status ORDER BY DATE asc) Rn,*
        FROM   yourtable
        WHERE  Status in ('New','In progress')) a
WHERE  (Rn = 1 and Status = 'In progress') or Status = 'New'
GROUP  BY ID 

• DEMO
• 演示