I have table with data like:
我有数据表,如:
Id | Start | End | Used
----------------------------------------
1 | 27-04-17 2:00 |27-04-17 0:00 | 1:30
---------------------------------------
2 | 27-04-17 2:00 |27-04-17 0:00 | 23:00
---------------------------------------
3 | 27-04-17 2:00 |27-04-17 0:00 | 1:00
---------------------------------------
4 | 28-04-17 2:00 |28-04-17 0:00 | 0:30
---------------------------------------
5 | 30-04-17 2:00 |30-04-17 0:00 | 3:30
---------------------------------------
I want to set it like
我想设置它
Id | Start | End | Used
----------------------------------------
1 | 27-04-17 2:00 |27-04-17 3:30 | 1:30
---------------------------------------
2 | 27-04-17 3:30 |28-04-17 2:30 | 23:00
---------------------------------------
3 | 28-04-17 2:30 |28-04-17 3:30 | 1:00
---------------------------------------
4 | 28-04-17 3:30 |28-04-17 4:00 | 0:30
---------------------------------------
5 | 30-04-17 2:00 |30-04-17 4:30 | 2:30
---------------------------------------
I want to set End time of previous id as Start time of next id, where Start time of first id is set by user.
End time is just sum of Start time and Used Time I am using vb.net data table and SQL server for database.
To set value in first row I am using SQL function DATEADD(), through which two hours are added and same function for adding time in end date.
I want to copy my End date of previous row in next row, so that whole calculation works properly.
Can I do it through SQL Only? or I will need a function to do it in Vb.net Data table from where it will be used for reports.
Note: Id sequence can be changed
Thanks for help.
我想将前一个id的结束时间设置为下一个id的开始时间,其中第一个id的开始时间由用户设置。结束时间只是开始时间和使用时间的总和我使用vb.net数据表和SQL服务器用于数据库。要在第一行中设置值,我使用SQL函数DATEADD(),通过该函数添加两个小时,并在结束日期添加时间相同的函数。我想复制下一行中前一行的结束日期,以便整个计算正常工作。我可以通过SQL进行吗?或者我需要一个函数来在Vb.net数据表中执行它,它将用于报告。注意:ID序列可以更改感谢您的帮助。
1 个解决方案
#1
0
Below recursive CTE logic will give you the desired output.
下面的递归CTE逻辑将为您提供所需的输出。
create table #tmp
(
ID int,
starttime datetime2,
endtime datetime2,
used varchar(5)
)
insert into #tmp values
(1,'17-Apr-2017 2:00','17-Apr-2017','1:30'),
(2,'17-Apr-2017 2:00','17-Apr-2017','2:00'),
(3,'17-Apr-2017 2:00','17-Apr-2017','1:00'),
(4,'17-Apr-2017 2:00','17-Apr-2017','0:30'),
(5,'28-Apr-2017 2:00','28-Apr-2017','3:30')
;with CTE as (
select ID,starttime,endtime,used,levels FROM
(select row_number() over (partition by cast(starttime as date) order by starttime) RID,ID,starttime,dateadd(hour,cast(substring(used,1,charindex(':',used)-1) as int),dateadd(mi,cast(right(used,2) as int),starttime))
endtime,used,0 levels
from #tmp ) T
where RID=1
union all
select T.ID,C.endtime,dateadd(hour,cast(substring(T.used,1,charindex(':',T.used)-1) as int),dateadd(mi,cast(right(T.used,2) as int),C.endtime))
endtime,T.used,C.levels+1
from CTE C inner join #tmp T on T.ID=C.ID+1
where datediff(d,C.starttime,T.starttime)=0
)
select ID,StartTime,EndTime,Used from CTE order by ID
drop table #tmp
#1
0
Below recursive CTE logic will give you the desired output.
下面的递归CTE逻辑将为您提供所需的输出。
create table #tmp
(
ID int,
starttime datetime2,
endtime datetime2,
used varchar(5)
)
insert into #tmp values
(1,'17-Apr-2017 2:00','17-Apr-2017','1:30'),
(2,'17-Apr-2017 2:00','17-Apr-2017','2:00'),
(3,'17-Apr-2017 2:00','17-Apr-2017','1:00'),
(4,'17-Apr-2017 2:00','17-Apr-2017','0:30'),
(5,'28-Apr-2017 2:00','28-Apr-2017','3:30')
;with CTE as (
select ID,starttime,endtime,used,levels FROM
(select row_number() over (partition by cast(starttime as date) order by starttime) RID,ID,starttime,dateadd(hour,cast(substring(used,1,charindex(':',used)-1) as int),dateadd(mi,cast(right(used,2) as int),starttime))
endtime,used,0 levels
from #tmp ) T
where RID=1
union all
select T.ID,C.endtime,dateadd(hour,cast(substring(T.used,1,charindex(':',T.used)-1) as int),dateadd(mi,cast(right(T.used,2) as int),C.endtime))
endtime,T.used,C.levels+1
from CTE C inner join #tmp T on T.ID=C.ID+1
where datediff(d,C.starttime,T.starttime)=0
)
select ID,StartTime,EndTime,Used from CTE order by ID
drop table #tmp