代码之家 › 专栏 › 技术社区 › MX313

SqlServer:结果不正确:每天“未解决”问题的数量

sql-server sql

MX313 · 技术社区 · 7 年前

我将此作为先前的问题发布,并澄清了一些错误。我发布了一个新问题,因为现在我正在处理糟糕的结果。我试图计算给定日期内的“未结”问题(理想情况下是在一个时间范围内,但目前只计算有条目的日期)。

我只在每天的基础上正确地返回输出,但它没有考虑在特定日期仍然打开的以前的问题(遗留问题)。

SELECT
    created_date,
    aOpen_Today + Legacy_Open_Issue - Resolved_Issue as totopen
FROM(
SELECT 
    convert(varchar(10), cast(i.CreationDate as date), 101) as created_date, 
    sum( case when YEAR(i.CompletionDate)=1900 then 1 else 0 end) as aOpen_Today,
    sum( case when (YEAR(i.CompletionDate)<>1900 AND (i.CompletionDate >= i.CreationDate)) then 1 else 0 end ) as Legacy_Open_Issue,
    coalesce(tot,0) as Resolved_Issue
FROM Issues i 
LEFT JOIN (
    SELECT count(IssueID) as tot, CompletionDate as resolved 
    FROM Issues 
    WHERE YEAR(CompletionDate)<>1900 group by CompletionDate
)x ON  x.resolved = i.CreationDate
GROUP BY convert(varchar(10), cast(i.CreationDate as date), 101), coalesce(tot,0)
) AS y;

My Data is
IssueID CreationDate    CompletionDate
1   1/15/2019   1/1/1900
2   1/16/2019   1/17/2019
3   1/16/2019   1/1/1900
4   1/20/2019   1/21/2019
5   1/28/2019   1/1/1900
6   1/30/2019   1/1/1900


My Output is
created_date    totopen
1/15/2019   1
1/16/2019   2
1/20/2019   1
1/28/2019   1
1/30/2019   1


My Output SHOULD be
created_date    totopen
1/15/2019   1
1/16/2019   3
1/20/2019   3
1/28/2019   3
1/30/2019   4

2 回复 | 直到 7 年前

Gordon Linoff 7 年前

我想你想要:

with i as (
      select creationdate as dte, 1 as inc
      from issues
      union all
      select completiondate, -1 as inc
      from issues
     )
select dte, sum(sum(inc)) over (order by dte)
from t
group by dte
order by dte;

Cato 7 年前

  SELECT DISTINCT CreationDate,DQ.OpenIssues  FROM Issues I
  CROSS APPLY
  (SELECT COUNT(0) OpenIssues FROM Issues I2 
          WHERE I2.CreationDate <= I.CreationDate AND
                     (I.CreationDate < I2.CompletionDate or YEAR(I2.CompletionDate) = 1900)
   ) DQ

推荐文章