I am trying to make the report in which the time interval is minutes and i have the start time & end time and want to have the time interval between two times.
我正在尝试制作报告,其中时间间隔是分钟,我有开始时间和结束时间,并希望有两次之间的时间间隔。
For example.
Start Time:- 8:00PM END TIME:- 16:00PM
开始时间: - 晚上8:00结束时间: - 下午16:00
Table Structure may be like this.
表结构可能是这样的。
S.no ---------------------------Time Interval
1--------------------------------8:00 AM - 8:10 AM(ENTRY STARTS)
2--------------------------------8:10 AM- 8:20 AM
3------------------------------- 8:20 AM - 8:30 AM
Nth-----------------------------3:50PM - 4:00 PM(LAST ENTRY FOR
REPORT)
How can i do this using on SQL query and i want to use it in Asp.Net GridView?
我如何使用SQL查询,我想在Asp.Net GridView中使用它?
For example.
RE TABLE STRUCTURE
RE TABLE结构
using the below data now iam trying to put the no of keys between the start time of workdatetime and last of this workdatetime/
使用下面的数据现在iam试图在workdatetime的开始时间和此workdatetime的最后一个之间设置no键
Logic is SAme as PREVIOUS but unable to put the NO OF KEYS between the Time Interval. I wan to show the structure same as above only add one column No of keys with respect to the time interval/
逻辑是SAme为PREVIOUS但无法在时间间隔之间放置NO OF KEYS。我想显示与上面相同的结构只添加一列相对于时间间隔的键没有/
WorkdateTime **** NoOfKeys
WorkdateTime **** NoOfKeys
16:52:04 **** 54
16:52:04 **** 54
16:52:06 **** 0
16:52:06 **** 0
16:52:07 **** 2
16:52:07 **** 2
16:52:25 **** 0
16:52:25 **** 0
16:52:26 **** 0
16:52:26 **** 0
16:52:35 **** 0
16:52:35 **** 0
16:52:35 **** 0
16:52:35 **** 0
16:53:15 **** 0
16:53:15 **** 0
16:53:55 **** 0
16:53:55 **** 0
16:54:28 **** 11
16:54:28 **** 11
16:54:35 **** 0
16:54:35 **** 0
16:55:15 **** 0
16:55:15 **** 0
16:55:55 **** 0
16:55:55 **** 0
16:56:35 **** 0
16:56:35 **** 0
16:57:15 **** 0
16:57:15 **** 0
16:57:28 **** 103
16:57:28 **** 103
16:57:55 **** 0
16:57:55 **** 0
16:58:35 **** 0
16:58:35 **** 0
16:58:39 **** 2
16:58:39 **** 2
16:59:09 **** 19
16:59:09 **** 19
16:59:15 **** 0
16:59:15 **** 0
16:59:42 **** 40
16:59:42 **** 40
16:59:43 **** 2
16:59:43 **** 2
16:59:55 **** 0
16:59:55 **** 0
17:00:35 **** 0
17:00:35 **** 0
17:01:15 **** 0
17:01:15 **** 0
17:01:35 **** 4
17:01:35 **** 4
17:01:55 **** 0
17:01:55 **** 0
17:02:35 **** 0
17:02:35 **** 0
17:03:15 **** 0
17:03:15 **** 0
17:03:55 **** 0
17:03:55 **** 0
17:03:58 **** 2
17:03:58 **** 2
17:04:24 **** 3
17:04:24 **** 3
17:04:35 ****
17:05:15 **** 0
17:05:15 **** 0
17:05:40 **** 0
17:05:40 **** 0
17:05:46 **** 14
17:05:46 **** 14
17:05:47 **** 0
17:05:47 **** 0
17:05:50 **** 2
17:05:50 **** 2
17:06:30 **** 0
17:06:30 **** 0
17:06:37 **** 1
17:06:37 **** 1
17:07:10 **** 0
17:07:10 **** 0
17:07:50 **** 0
17:07:50 **** 0
17:08:12 **** 1
17:08:12 **** 1
17:08:30 **** 0
17:08:30 **** 0
17:09:10 **** 0
17:09:10 **** 0
17:09:27 **** 42
17:09:27 **** 42
17:09:50 **** 0
17:09:50 **** 0
3 个解决方案
#1
2
declare @Start time
declare @end time
declare @request int
set @Start = '08:00:00'
set @end = '16:00:00'
set @request = 1
;with Dates as (
select @request as reqId,@Start as reqDate
union all
select reqId+1,DATEADD(MINUTE,10,reqDate) from Dates
where reqDate < @end
)
select reqId,convert(varchar(8),reqDate,100)+'-'+convert(varchar(8),DATEADD(MINUTE,10,reqDate),100) "Time Interval" from Dates
Use this for ur reports
将此用于您的报告
#2
1
Take a look at DateDiff
看看DateDiff
SELECT DATEDIFF(second, '2000-01-01 08:00:00' , '2000-01-01 16:00:00');
Return the difference/interval in seconds. Divide by 60 to get minutes, and divide by 60 again to get hours.
以秒为单位返回差异/间隔。除以60得到分钟,再除以60得到几小时。
Since you are only using the time component, you can set the datepart to whatever you like.
由于您只使用时间组件,因此可以将datepart设置为您喜欢的任何组件。
EDIT:
Since I misread the question, maybe you want something like this:
既然我误解了这个问题,也许你想要这样的东西:
DateTime startAt = new DateTime(2000, 01, 01, 8, 0, 0);
DateTime endAt = new DateTime(2000, 01, 01, 16, 0, 0);
for (DateTime date = startAt; date < endAt; date = date.AddMinutes(10))
{
// create something with this interval
Console.WriteLine(string.Format("Interval start: {0}, Interval End: {1}", date.ToString("HH:mm"), date.AddMinutes(10).ToString("HH:mm")));
}
Just use the start date and end date and loop through it with 10min intervals.
只需使用开始日期和结束日期,然后以10分钟的间隔循环播放。
#3
0
Here is a solution using CTE
这是使用CTE的解决方案
DECLARE @StartDate DATETIME, @EndTime DATETIME
SELECT @StartDate = '8:00 PM'
SELECT @EndTime = '9:00 PM'
;WITH time_cte(StartTime, EndTime) AS
(
SELECT @StartDate StartTime, DATEADD(mi, 10, @StartDate) EndTime
UNION ALL
SELECT EndTime, DATEADD(mi, 10, EndTime) FROM time_cte
WHERE EndTime < @EndTime
)
SELECT * FROM time_cte
Check its working at SQL Fiddle
检查它在SQL Fiddle的工作
#1
2
declare @Start time
declare @end time
declare @request int
set @Start = '08:00:00'
set @end = '16:00:00'
set @request = 1
;with Dates as (
select @request as reqId,@Start as reqDate
union all
select reqId+1,DATEADD(MINUTE,10,reqDate) from Dates
where reqDate < @end
)
select reqId,convert(varchar(8),reqDate,100)+'-'+convert(varchar(8),DATEADD(MINUTE,10,reqDate),100) "Time Interval" from Dates
Use this for ur reports
将此用于您的报告
#2
1
Take a look at DateDiff
看看DateDiff
SELECT DATEDIFF(second, '2000-01-01 08:00:00' , '2000-01-01 16:00:00');
Return the difference/interval in seconds. Divide by 60 to get minutes, and divide by 60 again to get hours.
以秒为单位返回差异/间隔。除以60得到分钟,再除以60得到几小时。
Since you are only using the time component, you can set the datepart to whatever you like.
由于您只使用时间组件,因此可以将datepart设置为您喜欢的任何组件。
EDIT:
Since I misread the question, maybe you want something like this:
既然我误解了这个问题,也许你想要这样的东西:
DateTime startAt = new DateTime(2000, 01, 01, 8, 0, 0);
DateTime endAt = new DateTime(2000, 01, 01, 16, 0, 0);
for (DateTime date = startAt; date < endAt; date = date.AddMinutes(10))
{
// create something with this interval
Console.WriteLine(string.Format("Interval start: {0}, Interval End: {1}", date.ToString("HH:mm"), date.AddMinutes(10).ToString("HH:mm")));
}
Just use the start date and end date and loop through it with 10min intervals.
只需使用开始日期和结束日期,然后以10分钟的间隔循环播放。
#3
0
Here is a solution using CTE
这是使用CTE的解决方案
DECLARE @StartDate DATETIME, @EndTime DATETIME
SELECT @StartDate = '8:00 PM'
SELECT @EndTime = '9:00 PM'
;WITH time_cte(StartTime, EndTime) AS
(
SELECT @StartDate StartTime, DATEADD(mi, 10, @StartDate) EndTime
UNION ALL
SELECT EndTime, DATEADD(mi, 10, EndTime) FROM time_cte
WHERE EndTime < @EndTime
)
SELECT * FROM time_cte
Check its working at SQL Fiddle
检查它在SQL Fiddle的工作