选择打开，关闭和积压 [英] Select for Opened, Closed and Backlog
问题描述
我有下表：
+ ------------- + - ------------------- + --------------------- +
|状态| open_time | close_time |
+ ------------- + --------------------- + --------- ------------ +
|关闭| 01-11-2014 19:32:44 | 01-11-2014 20:32:44 |
|打开| 01-12-2014 22:33:49 | 02-12-2014 22:33:49 |
|打开| 01-23-2014 22:08:24 | 03-23-2014 22:08:24 |
|关闭| 02-01-2014 22:33:57 | 03-01-2014 22:33:57 |
|打开| 02-01-2013 22:37:34 | 02-01-2013 23:37:34 |
|关闭| 04-20-2013 15:23:00 | 05-20-2013 15:23:00 |
|打开| 04-20-2013 12:21:49 | 05-20-2013 12:21:49 |
|关闭| 04-25-2013 11:22:00 | 06-25-2013 11:22:00 |
|关闭| 05-20-2013 14:23:49 | 10-20-2013 14:23:49 |
|关闭| 04-20-2013 16:33:49 | 04-25-2013 16:33:49 |
+ ------------- + --------------------- + --------- ------------ *
我想展示这个结果：
+ ------------- + ----------- ---- + -------------- + --------- +
|年份|月|打开案例|封闭案例|积压|
+ ------------- + --------------- + -------------- + --------- +
| 2014 | 4 | 10 | 5 | 62 | （57 + 5）
| 2014 | 3 | 9 | 7 | 57 | （52 + 2）
| 2014 | 2 | 15 | 20 | 52 | （57 - 5）
| 2014 | 1 | 12 | 1 | 57 | （46 + 11）
| 2013 | 12 | 10 | 9 | 46 | （45 + 1）
| 2013 | 11 | 50 | 5 | 45 | （45）
+ -------------- + -------------- + ------------ - + --------- +
其实我面对两种情况：
-
情况＃1：我无法为封闭案例获得正确的值。试图在没有运气的情况下使用子句。
情况＃2：在找到Closed Cases之后，我应该执行（Opened-Closed）并累积在过去的几个月中，这将是积压。
对于情况＃1：
如果我做了以下选择：
SELECT
YEAR（open_time） AS Ano，
MONTH（open_time）AS Mes，
sum（CASE WHAT DATEPART（YYYY，open_time）= 2013 AND DATEPART（MM，open_time）= 11 THEN 1 ELSE 0 END）Abertos，
sum（CASE WHAT DATEPART（YYYY，close_time）= 2013 AND DATEPART（MM，close_time）= 11 THEN 1 ELSE 0 END）Fechados
FROM
TABLE
WHERE
GROUPDESC = 'SUPPORT'
GROUP BY
MONTH（open_time），
YEAR（open_time）
ORDER BY
Ano DESC，
Mes DESC
我得到了（这是封闭案例的正确值）：
+ ------------- + --------------- + -------- ------ +
|年份|月|打开案例|封闭案例|
+ ------------- + --------------- + -------------- +
| 2014 | 4 | 0 | 0 |
| 2014 | 3 | 0 | 0 |
| 2014 | 2 | 0 | 0 |
| 2014 | 1 | 0 | 0 |
| 2013 | 12 | 0 | 0 |
| 2013 | 11 | 50 | 5 |
+ -------------- + -------------- + -------------- +
但如果我这样做：
<$ p $ （open_time）AS $，
MONTH（open_time）AS Mes，
sum（CASE WHAT DATEPART（YYYY，open_time）= YEAR（open_time） AND DATEPART（MM，open_time）= MONTH（open_time）THEN 1 ELSE 0 END）Abertos，
sum（当DATEPART（YYYY，close_time）= YEAR（close_time）和DATEPART（MM，close_time）= YEAR（close_time ）THEN 1 ELSE 0 END）Fechados
FROM
TABLE
WHERE
GROUPDESC ='SUPPORT'
GROUP BY
MONTH（open_time），
YEAR（open_time）
ORDER BY
Ano DESC，
Mes DESC
我得到：
+ ------------- + ----- ---------- + -------------- +
|年份|月|打开案例|封闭案例|
+ ------------- + --------------- + -------------- +
| 2014 | 4 | 0 | 0 |
| 2014 | 3 | 0 | 0 |
| 2014 | 2 | 0 | 0 |
| 2014 | 1 | 0 | 0 |
| 2013 | 12 | 0 | 0 |
| 2013 | 11 | 50 | 50 |
+ -------------- + -------------- + -------------- +
以下是解决此问题的一种方法：每月开放计数和每月所有结束计数。然后全部加入他们。累计是一个所谓的累计总额，您可以使用SUM OVER。
select
coalesce（已打开。 ano，closed.ano）as ao，
coalesce（opened.mes，closed.mes）as mes，
coalesce（opened.cnt，0）as opened_case，
coalesce（closed.cnt ，0）as closed_cases，
sum（coalesce（opened.cnt，0） - coalesce（closed.cnt，0））over（by coalesce（opened.ano，closed.ano），coalesce（opened.mes ，closed.mes））作为积压
从
（
选择
年（open_time）作为ano，
月（open_time）作为mes，
计数（*）as cnt
from probsummarym1
其中groupdesc ='SUPPORT'
按年分组（open_time），月份（open_time）
）已打开
完全外部连接
（
）选择
year（close_time）作为ano，
month（close_time）作为mes，
作为cnt计数（*）作为cnt
从probsummarym1
其中groupdesc ='SUPPORT'
和status ='closed'
按年份（close_time），月份（close_time）
）在opens.ano = closed.ano上关闭
，并打开.mes = closed.mes
通过合并（opened.ano，closed.ano）命令解散，合并（opened.mes，closed。 mes）desc;
以下是SQL小提琴： http://sqlfiddle.com/#!6/68dcf/7 。
另一种方法是使用UNION ALL将结束事件与开幕式事件粘合起来，然后计数：
select
ano，
$ b $（
）
已结束$选择
年（fecha）作为ano，
月（fecha）作为mes，
总和（evento ='打开'，然后1 else 0结束时）为opened_cases，
sum （事件='关闭'的情况下，然后1其他0结束）作为closed_cases
从
（
选择'打开'作为事件，open_time作为fecha
从probsummarym1
其中groupdesc ='SUPPORT'
union all
从probsummarym1
中选择'closed'作为evento，close_time作为fecha
其中groupdesc ='SUPPORT'
和status ='closed'
）x
按年份（fecha），月份（fecha），
）y
按ano desc，mes desc排序;
这里是第二个SQL小提琴： http://sqlfiddle.com/#!6/68dcf/18 。
编辑：没有SUM OVER？这太糟糕了。所以你将不得不重新计数。这很慢，因为每个月都必须再次扫描表。
对于每个月，我们都必须查找所有开始日期，直到此时为止。由于在开始之前永远不会发生结束，我们可以选择开始日期匹配的所有记录并对它们进行计数。在这些记录中，我们也会找到所有可能的结束日期。我们计算这些（与总和和case），减去，我们完成了。
所以你将不得不取代 SUM（）OVER作为积压部分：
（
select
count（*）
-
sum
（
case
when eventsuntil.status ='closed'
and year（eventsuntil.close_time）* 100 + month（eventsuntil。 close_time）> =
y.ano * 100 + y.mes
then 1 else 0
end
）
from probsummarym1 eventsuntil
where eventsuntil。 groupdesc ='SUPPORT'
和year（eventsuntil.open_time）* 100 + month（eventsuntil.open_time）> =
y.ano * 100 + y.mes
）作为待办事项
但是，即使在版本2012中，SQL Server也无法执行此操作，我认为这是dbms缺陷。对于第一个语句，y.ano和y.mes（或coalesce（opened.ano，closed.ano）和coalesce（opened.mes，closed.mes））应该被视为内部查询的常量，因为评估是按照每个外部记录，即月份，但它们不是。我不知道如何克服这个问题。也许一些SQL Server专家可以帮助你。
以下是导致语法错误的两个小提琴： http://sqlfiddle.com/#!6/68dcf/32 和 http://sqlfiddle.com/#!6/68dcf/31 。对不起，我无法再帮你了。
I have the following table:
+-------------+---------------------+---------------------+
| status | open_time | close_time |
+-------------+---------------------+---------------------+
| closed | 01-11-2014 19:32:44 | 01-11-2014 20:32:44 |
| open | 01-12-2014 22:33:49 | 02-12-2014 22:33:49 |
| open | 01-23-2014 22:08:24 | 03-23-2014 22:08:24 |
| closed | 02-01-2014 22:33:57 | 03-01-2014 22:33:57 |
| open | 02-01-2013 22:37:34 | 02-01-2013 23:37:34 |
| closed | 04-20-2013 15:23:00 | 05-20-2013 15:23:00 |
| open | 04-20-2013 12:21:49 | 05-20-2013 12:21:49 |
| closed | 04-25-2013 11:22:00 | 06-25-2013 11:22:00 |
| closed | 05-20-2013 14:23:49 | 10-20-2013 14:23:49 |
| closed | 04-20-2013 16:33:49 | 04-25-2013 16:33:49 |
+-------------+---------------------+---------------------*
I would like to show this result:
+-------------+---------------+--------------+---------+
| Year | Month | Opened Cases | Closed Cases | Backlog |
+-------------+---------------+--------------+---------+
| 2014 | 4 | 10 | 5 | 62 | (57 + 5)
| 2014 | 3 | 9 | 7 | 57 | (52 + 2)
| 2014 | 2 | 15 | 20 | 52 | (57 - 5)
| 2014 | 1 | 12 | 1 | 57 | (46 + 11)
| 2013 | 12 | 10 | 9 | 46 | (45 + 1)
| 2013 | 11 | 50 | 5 | 45 | (45)
+--------------+--------------+--------------+---------+
Actually I am facing 2 situations:
Situation #1: I am not able to get the right value for the Closed Cases. Tried to work with clauses in where but no luck.
Situation #2: After retrive the Closed Cases, I should do the (Opened - Closed) and accumulate it over the months, so this will be the backlog.
For the Situation #1:
If I do the following select:
SELECT
YEAR(open_time) AS Ano,
MONTH(open_time) AS Mes,
sum(CASE WHEN DATEPART(YYYY, open_time)= 2013 AND DATEPART(MM, open_time)= 11 THEN 1 ELSE 0 END) Abertos,
sum(CASE WHEN DATEPART(YYYY, close_time)= 2013 AND DATEPART(MM, close_time)= 11 THEN 1 ELSE 0 END) Fechados
FROM
TABLE
WHERE
GROUPDESC= 'SUPPORT'
GROUP BY
MONTH(open_time),
YEAR(open_time)
ORDER BY
Ano DESC,
Mes DESC
I get (this is the right value for closed cases):
+-------------+---------------+--------------+
| Year | Month | Opened Cases | Closed Cases |
+-------------+---------------+--------------+
| 2014 | 4 | 0 | 0 |
| 2014 | 3 | 0 | 0 |
| 2014 | 2 | 0 | 0 |
| 2014 | 1 | 0 | 0 |
| 2013 | 12 | 0 | 0 |
| 2013 | 11 | 50 | 5 |
+--------------+--------------+--------------+
But if I do:
SELECT
YEAR(open_time) AS Ano,
MONTH(open_time) AS Mes,
sum(CASE WHEN DATEPART(YYYY, open_time)= YEAR(open_time) AND DATEPART(MM, open_time)= MONTH(open_time) THEN 1 ELSE 0 END) Abertos,
sum(CASE WHEN DATEPART(YYYY, close_time)= YEAR(close_time) AND DATEPART(MM, close_time)= YEAR(close_time) THEN 1 ELSE 0 END) Fechados
FROM
TABLE
WHERE
GROUPDESC= 'SUPPORT'
GROUP BY
MONTH(open_time),
YEAR(open_time)
ORDER BY
Ano DESC,
Mes DESC
I get:
+-------------+---------------+--------------+
| Year | Month | Opened Cases | Closed Cases |
+-------------+---------------+--------------+
| 2014 | 4 | 0 | 0 |
| 2014 | 3 | 0 | 0 |
| 2014 | 2 | 0 | 0 |
| 2014 | 1 | 0 | 0 |
| 2013 | 12 | 0 | 0 |
| 2013 | 11 | 50 | 50 |
+--------------+--------------+--------------+
Here is one way to approach the problem: select all opening counts per month and all closing counts per month. Then full outer join them. The cumulation is a so-called running total, which you get with SUM OVER.
select
coalesce(opened.ano, closed.ano) as ano,
coalesce(opened.mes, closed.mes) as mes,
coalesce(opened.cnt, 0) as opened_cases,
coalesce(closed.cnt, 0) as closed_cases,
sum(coalesce(opened.cnt, 0) - coalesce(closed.cnt, 0)) over (order by coalesce(opened.ano, closed.ano), coalesce(opened.mes, closed.mes)) as backlog
from
(
select
year(open_time) as ano,
month(open_time) as mes,
count(*) as cnt
from probsummarym1
where groupdesc = 'SUPPORT'
group by year(open_time), month(open_time)
) opened
full outer join
(
select
year(close_time) as ano,
month(close_time) as mes,
count(*) as cnt
from probsummarym1
where groupdesc = 'SUPPORT'
and status = 'closed'
group by year(close_time), month(close_time)
) closed
on opened.ano = closed.ano and opened.mes = closed.mes
order by coalesce(opened.ano, closed.ano) desc, coalesce(opened.mes, closed.mes) desc;
Here is the SQL fiddle: http://sqlfiddle.com/#!6/68dcf/7.
Another way would be to glue the opening events with the closing events with UNION ALL and then count:
select
ano,
mes,
opened_cases,
closed_cases,
sum(opened_cases - closed_cases) over (order by ano, mes) as backlog
from
(
select
year(fecha) as ano,
month(fecha) as mes,
sum(case when evento = 'opened' then 1 else 0 end) as opened_cases,
sum(case when evento = 'closed' then 1 else 0 end) as closed_cases
from
(
select 'opened' as evento, open_time as fecha
from probsummarym1
where groupdesc = 'SUPPORT'
union all
select 'closed' as evento, close_time as fecha
from probsummarym1
where groupdesc = 'SUPPORT'
and status = 'closed'
) x
group by year(fecha), month(fecha)
) y
order by ano desc, mes desc;
And here is the second SQL fiddle: http://sqlfiddle.com/#!6/68dcf/18.
EDIT: No SUM OVER? That's too bad. So you will have to subselect the count. That is slow, because the table must be scanned again for every single month.
For every month we must find all start dates until then and all end dates till then. As an end can never occur before start, we can select all records where the start date matches and count these. Within these records we will also find all potential end dates. We count these (with sum and case), subtract, and we're done.
So you would have to replace the SUM() OVER as backlog part thus:
(
select
count(*)
-
sum
(
case
when eventsuntil.status = 'closed'
and year(eventsuntil.close_time) * 100 + month(eventsuntil.close_time) >=
y.ano * 100 + y.mes
then 1 else 0
end
)
from probsummarym1 eventsuntil
where eventsuntil.groupdesc = 'SUPPORT'
and year(eventsuntil.open_time) * 100 + month(eventsuntil.open_time) >=
y.ano * 100 + y.mes
) as backlog
However, SQL Server, even in version 2012, is not capable of executing this, which I consider a dbms flaw. y.ano and y.mes (or coalesce(opened.ano, closed.ano) and coalesce(opened.mes, closed.mes) for the first statement) should be considered constants for the inner query, because the evaluation is done per outer record, i.e. month, but they are not. I don't know how to overcome this problem. Maybe some expert on SQL Server can help you here.
Here are the two fiddles that result in syntax errors: http://sqlfiddle.com/#!6/68dcf/32 and http://sqlfiddle.com/#!6/68dcf/31. Sorry I cannot help you here any further.
这篇关于选择打开，关闭和积压的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
