涉及res的复杂查询

Question

我有如下表格，我需要查询当天创建了多少任务，有多少任务已解决，我需要捕获从前一天以活动状态传递的任务。例如: TaskId 101创建于2014年11月10日，解析日期为2014年11月12日，因此它应显示在2014年11月11日和2014年11月12日的计数中。

TaskId CreateDate Status ResolvedDate
101 11/10/2014 Resolved 11/12/2014
102 11/10/2014 Resolved 11/10/2014
103 11/11/2014 Active NULL 
104 11/11/2014 Resolved 11/13/2014
105 11/13/2014 Active 11/13/2014

我想不出任何解决办法，请帮帮我。很抱歉，我试图以表格格式发布表格模式，但无法创建表格，而且我是这个论坛的新手。

Answer 1

您可以使用case based aggregation获得结果

CTE为给定范围生成日期列表。

对于每一天，使用case、max和group by计算当天的活动总数(已创建，之前已创建，但未解析)和已解析的总数

declare @start_date datetime = '2014-11-10'
declare @end_date datetime ='2014-11-13'


;WITH CTE AS
(
    SELECT @start_date AS date
    UNION ALL
    SELECT DATEADD(day, 1, date) as date
    FROM CTE
    WHERE DATEADD(day, 1, date) <= @end_date
)
select cte.date,
       sum( case when  createDate <= cte.date and ( resolveddate is null or  ResolvedDate >= cte.date) then 1 else 0 end
          ) as TotalActive,

       sum( case when cte.date = ResolvedDate then 1 else 0 end
          ) as TotalResolved
from cte
left join Table1 T
ON T.createDate <= cte.date
GROUP BY CTE.DATE

Answer 2

@雷达，你刚刚给了我查询的希望，我已经更改了查询，如下所示，它开始显示结果。但与TotalCreated相比，TotalActive显示的价值更小。

declare @start_date datetime = getdate()-30
declare @end_date datetime = getdate()
;WITH CTE AS
(
    SELECT @start_date AS date
    UNION ALL
    SELECT DATEADD(day, 1, date) as date
    FROM CTE
    WHERE DATEADD(day, 1, date) <= @end_date
)
select cte.date,
        sum( case when CONVERT(varchar, CreateDate, 101) <= CONVERT(varchar, cte.date, 101) and CONVERT(varchar, cte.date, 101) <= CONVERT(varchar, ResolveDate, 101) then 1 else 0 end
          ) as TotalActive,
          sum( case when  CONVERT(varchar, cte.date, 101) = CONVERT(varchar, CreateDate, 101) then 1 else 0 end
          ) as TotalCreated,
       sum( case when CONVERT(varchar, cte.date, 101) = CONVERT(varchar, ResolveDate, 101) then 1 else 0 end
          ) as TotalResolved

from cte
left join [WarehouseIncidents] T
ON CONVERT(varchar, CreateDate, 101) >= CONVERT(varchar, cte.date, 101)

GROUP BY CTE.DATE