ReimburseID same data after a time on minus [AuditTime] [AuditTime] according to the data for a few days how few minutes show

CodePudding user response:

Lag and lead these two functions

CodePudding user response:

 
If object_id (' tempdb for.. # # WFAudiOpinions') is not null drop table WFAudiOpinions 
The create table # WFAudiOpinions (ID int identity (1, 1), ReimburseID int, AuditTime datetime) 

Insert into # WFAudiOpinions (ReimburseID, AuditTime) 
Select '1', '2019-06-12 10:30:39' union all 
Select '1', '2019-06-12 12:03:17' union all 
Select '1', '2019-06-12 12:14:33' union all 
Select '1', '2019-06-12 12:46:50' union all 
Select '1', '2019-06-12 12:54:46' union all 
Select '1', '2019-06-12 12:54:54' union all 
Select '1', '2019-06-14 04:33:54' union all 
Select the '5', '2019-06-12 23:49:34' union all 
Select '2', '2019-06-12 23:53:06' union all 
Select '3', 'the 2019-06-13 09:00:32' 

- the method one: support SqlServer2012 and above version 
Select ID, ReimburseID, AuditTime, 
Cast as nvarchar (minute/60/24 (50)) + 'day' + cast (24 minute/60% as nvarchar (50)) + 'when' + cast (60 minute % as nvarchar (50)) + 'points' 
The from (
Select ID, ReimburseID, AuditTime Previous_AuditTime, 
(case when ReimburseID=Previous_ReimburseID then datediff (minute, AuditTime, Previous_AuditTime) else 'end) as minute 
The from (
Select *, 
Lead (AuditTime, 1) over (order by ReimburseID) as Previous_AuditTime, 
Lead (ReimburseID, 1) over (order by ReimburseID) as Previous_ReimburseID 
The from # WFAudiOpinions 
) t 
Tt) 

- the method 2: support SqlServer2005 and above version 
If object_id (' tempdb for.. # # TAB ') is not null drop table TAB 
The select row_number () over (partition by ReimburseID order by AuditTime) as num, * into # # TAB from WFAudiOpinions 
Select ID, ReimburseID, AuditTime, 
Cast as nvarchar (minute/60/24 (50)) + 'day' + cast (24 minute/60% as nvarchar (50)) + 'when' + cast (60 minute % as nvarchar (50)) + 'points' 
The from (
The select Anderson D, a.R eimburseID, a.A uditTime, b.A uditTime as Previous_AuditTime, 
Datediff (minute, a.A uditTime, b.A uditTime) as minute 
The from # TAB a 
Left the join # TAB on b a.R eimburseID=b.R eimburseID and a.n um=(b.n um - 1) 
) t 

CodePudding user response:

Upstairs method is very good, they want the stupid method, computational time bit by bit,,,,
Learning to learn