SQLSERVER 2012:
SQL查询返回24小时数据,其中小时从0-23开始。该查询正在处理的问题是,当您选择一个日期范围时,例如说“ 1-1-2014”和“ 1-2-2014”,该报告应获取上午9点至第二天8点之间的数据。
查询应计算我们选择的任何日期范围,但应仅选择第二天/月/年的上午9点到上午8点之间的小时数。
我的表结构/列在数据库中:ReadingDate小时:0-23数据1数据2数据3
输出
Date | Hours| Data 1| Data 2| Data 3
sSelect = "Select P.ReadingDate,P.Hour,P.Data1,P.Data2,P.Data3 FROM Operations P WHERE P.ReadingDate = '" & startdate & "' AND P.Hour Between 9 AND 23 Group by P.ReadingDate,P.Hour,P.Data1,P.Data2,P.Data3
Union
Select P.ReadingDate,P.Hour,P.Data1,P.Data2,P.Data3 FROM Operations P WHERE P.ReadingDate = '" & Enddate & " ' AND P.Hour Between 0 And 8 group by P.ReadingDate,P.Hour,P.Data1,P.Data2,P.Data3 "
2015-1-15 12:00 AM| 9| 1900|2000|300
2015-1-15 12:00 AM| 10|
2015-1-15 12:00 AM| 11|
2015-1-15 12:00 AM| 12|
2015-1-15 12:00 AM| 13|
2015-1-15 12:00 AM| 14|
2015-1-15 12:00 AM| 15|
2015-1-15 12:00 AM| 16|
2015-1-15 12:00 AM| 17|
2015-1-15 12:00 AM| 18|
2015-1-15 12:00 AM| 19|
2015-1-15 12:00 AM| 20|
2015-1-15 12:00 AM| 21|
2015-1-15 12:00 AM| 22|
2015-1-15 12:00 AM| 23|
2015-1-16 12:00 AM| 0|
2015-1-16 12:00 AM| 1|
2015-1-16 12:00 AM| 2|
2015-1-16 12:00 AM| 3|
2015-1-16 12:00 AM| 4|
2015-1-16 12:00 AM| 5|
2015-1-16 12:00 AM| 6|
2015-1-16 12:00 AM| 7|
2015-1-16 12:00 AM| 8|
我能够获取日期范围“ 1-1-2015”和“ 1-2-2015”的1天数据,但是如果我为月份“ 12-1-2014”到2014/12/31选择日期范围'它没有给我全部数据,而又给了我1天的数据。
这是考虑到SQL Server的:
DECLARE @BeginDate datetime = '2014-01-01'; --List only as a starting date; do not include the time
DECLARE @EndDate datetime = '2014-01-02'; --List only as a end date; do not include the time; should not be the same as @BeginDate;
--Could change this to expect the same date, but will need to add a full day to @EndDate below in addition to hour
SET @BeginDate = DATEADD(HOUR, 9, @BeginDate); --Sets the starting time at 9:00 AM per business rules
SET @EndDate = DATEADD(HOUR, 8, @EndDate); --Sets the ending time at 8:00 AM per business rules
SELECT
YourTable.ReadingDate,
YourTable.Hours,
YourTable.Data1,
YourTable.Data2,
YourTable.Data3
FROM
YourTable
WHERE
DATEADD(HOUR, YourTable.Hours, YourTable.ReadingDate) BETWEEN @BeginDate AND @EndDate;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句