SELECT * into

#TEMP_TRAN FROM

(select distinct

TLR_Br_Num as Branch,

TLR_Emp_Num as Employe,

TLR_Trn_Dt as Tran_Date,

TLR_Trn_Tm,

(Left (TLR_Trn_TM, 2) + ':' + RIGHT (TLR_Trn_TM,2)) as Tran_Time,

count (TLR_ID) as Num_Trans

From dbo.TLR

WHERE TLR_BR_Num = '00001'

and (Tlr_YR = '2015' and TLR_Mo = '1')

--and TLR_EMP_Num = '04269'

Group By

TLR_Br_Num,

TLR_Emp_Num,

TLR_Trn_Dt,

TLR_Trn_Tm,

(Left (TLR_Trn_TM, 2) + ':' + RIGHT (TLR_Trn_TM,2))

) data

Select * into #TIME

From

(

Select

ROW_NUMBER() OVER(order by Branch, Employe, Tran_Date, Tran_Time asc) as RN,

Branch,

Employe,

Tran_Date,

CAST(Tran_Time as time) as Tran_Time,

Num_Trans

from

#TEMP_TRAN

) data

select

t1.*,

(CASE WHEN (DATEDIFF(minute, t1.tran_time, t2.tran_time)) > 0

then (DATEDIFF(minute, t1.tran_time, t2.tran_time)) ELSE NULL END) as Time_to_Next_Tran_in_Minutes

from #TIME as t1

join #TIME as t2

on t1.RN = t2.RN - 1

order by t1.RN

drop table #TEMP_TRAN

drop table #TIME

Result Samples below - Note: This want done by a lot of help from one of my Associate from my internal SQL Community 🙂 Also, I wanted to say thank you very much for your support which direct me toward the end rersults.

RN BranchEmployeTran_Date Tran_Time Num_Trans Time_to_Next_Tran_in_Minutes

100001000002015-01-03 01:00:00.00000001390

200001000092015-01-02 07:30:00.000000031

300001000092015-01-02 07:31:00.0000000438

400001000092015-01-02 08:09:00.000000013

500001000092015-01-02 08:12:00.000000013

600001000092015-01-02 08:15:00.000000012

700001000092015-01-02 08:17:00.000000013

800001000092015-01-02 08:20:00.0000000115

900001000092015-01-02 08:35:00.0000000128

1000001000092015-01-02 09:03:00.0000000227

1100001000092015-01-02 09:30:00.000000027

1200001000092015-01-02 09:37:00.0000000119

1300001000092015-01-02 09:56:00.000000019