获取2个日期之间的数据 [英] Get data between 2 dates

问题描述

我写了一个查询，它从表格的开头给我记录。我想从当前日期获得最后1天的结果。



以下是有和没有日期的输出



我尝试过：

 选择 
（MonitorData.DesktopGroup.Name） as  '  Delivery'，sum（MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount） as  ' 总计' 
 来自 MonitorData.DesktopGroup 
  join  MonitorData.DesktopOSDesktopSummary  on  MonitorData.DesktopOSDesktopSummary.DesktopGroupId = MonitorData.DesktopGroup.Id 
  group   by  MonitorData.DesktopGroup.Name 

输出：

呼叫中心Appleton Dev 77 
呼叫中心Spokane Dev 146 
 Impact Base Dev 686 
 Impact HN Dev 2480 
 Impact LS Dev 48 
 Windows 10 VDI 916 

我修改如下

  select  
（MonitorData.DesktopGroup.Name） as  '   Delivery'，sum（MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount） as  ' 总计' 
 来自 MonitorData.DesktopGroup 
  join  MonitorData.DesktopOSDesktopSummary  on  MonitorData.DesktopOSDesktopSummary.DesktopGroupId = MonitorData.DesktopGroup.Id 
 < span class =code-keyword> whe重新 MonitorData.DesktopOSDesktopSummary.ModifiedDate  BETWEEN  '  2017-07-27'  AND  '  2017-07-28' 
  group   by  MonitorData.DesktopGroup.Name 

但它没有返回预期值。

预期是

电话Center Appleton Dev 0 
呼叫中心Spokane Dev 1 
 Impact Base Dev 0 
 Impact HN Dev 0 
 Impact LS Dev 0 
 Windows 10 VDI 1 

即将到来

影响HN Dev 13 

解决方案

将日期替换为日期时间或取自日期前一天

< pre lang =SQL> Selec t 
（MonitorData.DesktopGroup.Name）为'Delivery'，sum（MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount）为来自MonitorData.DesktopGroup的$ total 
 
加入MonitorData.DesktopOSDesktopSummary上的MonitorData.DesktopOSDesktopSummary。 DesktopGroupId = MonitorData.DesktopGroup.Id 
其中MonitorData.DesktopOSDesktopSummary.ModifiedDate BETWEEN   '2017-07-27 00：00：00.000'AND'2017-07-28 23:59 ：由MonitorData.DesktopGroup.Name组成的59.000' 
组
 
或
 
选择
（MonitorData.DesktopGroup.Name）作为'交付'，sum（MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount）作为'Total'
来自MonitorData.DesktopGroup 
加入MonitorData.DesktopOSDesktopSummary on MonitorData.DesktopOSDesktopSummary.DesktopGroupId = MonitorData.DesktopGroup.Id 
其中MonitorData.DesktopOSDesktopSummary .ModifiedDate BETWEEN   '2017-07-26'和'2017-07-28'
 group by MonitorData.DesktopGroup.Name 

I wrote a query which is giving me records from the beginning from the table. I want to get the result for last 1 day from the current date.

Below are the output with and without date

What I have tried:

select 
(MonitorData.DesktopGroup.Name) as 'Delivery',sum(MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount) as 'Total'
from MonitorData.DesktopGroup
join MonitorData.DesktopOSDesktopSummary on MonitorData.DesktopOSDesktopSummary.DesktopGroupId = MonitorData.DesktopGroup.Id
group by MonitorData.DesktopGroup.Name

Output:

call Center Appleton Dev	77
Call Center Spokane Dev	        146
Impact Base Dev	                686
Impact HN Dev	                2480
Impact LS Dev	                48
Windows 10 VDI	                916

I modified like below

select 
(MonitorData.DesktopGroup.Name) as 'Delivery',sum(MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount) as 'Total'
from MonitorData.DesktopGroup
join MonitorData.DesktopOSDesktopSummary on MonitorData.DesktopOSDesktopSummary.DesktopGroupId = MonitorData.DesktopGroup.Id
where MonitorData.DesktopOSDesktopSummary.ModifiedDate BETWEEN '2017-07-27' AND '2017-07-28'
group by MonitorData.DesktopGroup.Name

but it is not returning the expected value.
expected is

call Center Appleton Dev	0
Call Center Spokane Dev	        1
Impact Base Dev	                0
Impact HN Dev	                0
Impact LS Dev	                0
Windows 10 VDI	                1

It is coming as

Impact HN Dev	                13

解决方案

Replace date with datetime or take from date as one day before

<pre lang="SQL">Select 
(MonitorData.DesktopGroup.Name) as 'Delivery',sum(MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount) as 'Total'
from MonitorData.DesktopGroup
join MonitorData.DesktopOSDesktopSummary on MonitorData.DesktopOSDesktopSummary.DesktopGroupId = MonitorData.DesktopGroup.Id
where MonitorData.DesktopOSDesktopSummary.ModifiedDate BETWEEN '2017-07-27 00:00:00.000' AND '2017-07-28 23:59:59.000'
group by MonitorData.DesktopGroup.Name

or

Select 
(MonitorData.DesktopGroup.Name) as 'Delivery',sum(MonitorData.DesktopOSDesktopSummary.PeakConcurrentInstanceCount) as 'Total'
from MonitorData.DesktopGroup
join MonitorData.DesktopOSDesktopSummary on MonitorData.DesktopOSDesktopSummary.DesktopGroupId = MonitorData.DesktopGroup.Id
where MonitorData.DesktopOSDesktopSummary.ModifiedDate BETWEEN '2017-07-26' AND '2017-07-28'
group by MonitorData.DesktopGroup.Name

这篇关于获取2个日期之间的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！