I currently have the following table:
我目前有下表:
+-----+-----------------------------+------------------------------+
| ID | StartDate | EndDate |
+-----+-----------------------------+------------------------------|
| 1 | 2017-07-24 08:00:00.000 | 2017-07-29 08:00:00.000 |
| 2 | 2017-07-25 08:00:00.000 | 2017-07-28 08:00:00.000 |
| 3 | 2017-07-25 08:00:00.000 | 2017-07-26 08:00:00.000 |
+-----+-----------------------------+------------------------------+
I would like to know the count of the ID's that were not Closed on each date. So for example, I wan't to know the count of open ID's on 2017-07-26 00:00:00.000
. This would be all 3
in this case.
我想知道每个日期没有关闭的ID的计数。例如,我不知道2017-07-26 00:00:00.000的开放ID计数。在这种情况下,这将全部为3。
Another example: I wan't to know the count of open ID's on 2017-07-29 00:00:00.000
. Which would be result to 1
. Only ID=1 is Not yet closed at that date.
另一个例子:我想知道2017-07-29 00:00:00000的开放ID的数量。这将导致1.仅ID = 1尚未在该日期关闭。
I have tried using another solution here on *, but I can't quite figure why it is giving me false results.
我已尝试在*上使用另一种解决方案,但我无法理解为什么它会给我错误的结果。
declare @dt date, @dtEnd date
set @dt = getdate()-7
set @dtEnd = dateadd(day, 100, @dt);
WITH CTEt1 (SupportCallID, StartDate, EndDate, Onhold)
as
(SELECT SupportCallID
,OpenDate
,MAX(CASE WHEN StatusID IN('19381771-8E81-40C5-8E36-62A7DB0A2A99', '95C7A5FB-2389-4D14-9DAE-A08BFCC3B09A', 'D5429790-3B43-4462-9E1E-2466EA29AC74') then CONVERT(DATE, LastChangeDate) end) EndDate
,OnHold
FROM [ClienteleITSM_Prod_Application].[dbo].[SupportCall]
group by SupportCallID, OpenDate, OnHold
)
SELECT dates.myDate,
(SELECT COUNT(*)
FROM CTEt1
WHERE myDate BETWEEN StartDate and EndDate
)
FROM
(select dateadd(day, number, @dt) mydate
from
(select distinct number from master.dbo.spt_values
where name is null
) n
where dateadd(day, number, @dt) < @dtEnd) dates
1 个解决方案
#1
0
If you use a cte
to create a table of dates that span the range of dates in your source table, you can easily left join
from that to your source table and count
up the rows returned:
如果使用cte创建一个跨越源表中日期范围的日期表,则可以轻松地将其从该表连接到源表并计算返回的行:
declare @t table(ID int,StartDate datetime,EndDate datetime);
insert into @t values (1,'2017-07-24 08:00:00.000','2017-07-29 08:00:00.000'),(2,'2017-07-25 08:00:00.000','2017-07-28 08:00:00.000'),(3,'2017-07-25 08:00:00.000','2017-07-26 08:00:00.000');
declare @StartDate datetime = (select min(StartDate) from @t);
declare @EndDate datetime = (select max(EndDate) from @t);
-- Table with 10 rows in to be joined together to create a large tally table (10 * 10 * 10 * etc)
with t(t) as (select t from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(t))
-- Add the row_number of the tally table to your start date to generate all dates within your data range
,d(d) as (select top(datediff(d,@StartDate,@EndDate)+1) dateadd(d,row_number() over (order by (select null))-1,@StartDate) from t t1,t t2,t t3)
select d.d
,count(t.ID) as OpenIDs
from d
left join @t as t
on(d.d between cast(t.StartDate as date) and t.EndDate)
group by d.d
order by d.d;
Output:
+-------------------------+---------+
| d | OpenIDs |
+-------------------------+---------+
| 2017-07-24 08:00:00.000 | 1 |
| 2017-07-25 08:00:00.000 | 3 |
| 2017-07-26 08:00:00.000 | 3 |
| 2017-07-27 08:00:00.000 | 2 |
| 2017-07-28 08:00:00.000 | 2 |
| 2017-07-29 08:00:00.000 | 1 |
+-------------------------+---------+
#1
0
If you use a cte
to create a table of dates that span the range of dates in your source table, you can easily left join
from that to your source table and count
up the rows returned:
如果使用cte创建一个跨越源表中日期范围的日期表,则可以轻松地将其从该表连接到源表并计算返回的行:
declare @t table(ID int,StartDate datetime,EndDate datetime);
insert into @t values (1,'2017-07-24 08:00:00.000','2017-07-29 08:00:00.000'),(2,'2017-07-25 08:00:00.000','2017-07-28 08:00:00.000'),(3,'2017-07-25 08:00:00.000','2017-07-26 08:00:00.000');
declare @StartDate datetime = (select min(StartDate) from @t);
declare @EndDate datetime = (select max(EndDate) from @t);
-- Table with 10 rows in to be joined together to create a large tally table (10 * 10 * 10 * etc)
with t(t) as (select t from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(t))
-- Add the row_number of the tally table to your start date to generate all dates within your data range
,d(d) as (select top(datediff(d,@StartDate,@EndDate)+1) dateadd(d,row_number() over (order by (select null))-1,@StartDate) from t t1,t t2,t t3)
select d.d
,count(t.ID) as OpenIDs
from d
left join @t as t
on(d.d between cast(t.StartDate as date) and t.EndDate)
group by d.d
order by d.d;
Output:
+-------------------------+---------+
| d | OpenIDs |
+-------------------------+---------+
| 2017-07-24 08:00:00.000 | 1 |
| 2017-07-25 08:00:00.000 | 3 |
| 2017-07-26 08:00:00.000 | 3 |
| 2017-07-27 08:00:00.000 | 2 |
| 2017-07-28 08:00:00.000 | 2 |
| 2017-07-29 08:00:00.000 | 1 |
+-------------------------+---------+