2008 R2 Adding a columm with time differences between transactions per employee/date

  • Source of the transaction table. How do I build the "Results" column to see the time difference between an employees' daily transactions from one to another. I appreciate any help and thanks in advance.

    Store_

    BrNumStore_

    Emp_Num Store_

    Trn_Dt Store_Trn

    _Time Results Column

    00356164072001-05-2517500

    00356164072001-05-2517533

    00356164072001-05-2517585

    00403166492001-05-2517500

    00344193602001-05-2509000

    00344193602001-05-25091515

    00741277982001-05-2511150

    00741277982001-05-251750635

    00329317562001-05-2508570

    00329317562001-05-25090043

    00329317562001-05-251158258

    00329317562001-05-251750592

    00325327342001-05-2517500

    00325327342001-05-2517599

    00357352512001-05-2513000

    00357352512001-05-251750450

    00356164072001-05-2614210

    00356164072001-05-261538117

    00356164072001-05-26160062

    00403166492001-05-2617500

    00403166492001-05-2617533

    00344193602001-05-2610000

    00344193602001-05-26104848

    00329317562001-05-2611150

    00329317562001-05-26114833

    00329317562001-05-2611491

    00329317562001-05-261318169

    00325327342001-05-2608000

    00325327342001-05-261300500

    00357352512001-05-2611000

    00357352512001-05-2611055

    Char(5) Char(5), Date, Char(4),

    ,NullNullNull Null

  • Found this article:

    http://www.sqlservercentral.com/scripts/LAG/89769/

    If you had 2012, you could use LAG or LEAD to look at previous/next records and do math on values between records, but since you can't...

  • ;WITH cte (BrNum,Emp_Num,Trn_Dt,_Time,Trn_Dt_Time,RowNum)

    AS (

    SELECTBrNum,Emp_Num,Trn_Dt,_Time,

    CAST(CONVERT(char(10),Trn_Dt,120)+' '+STUFF(_Time,3,0,':') as datetime),

    ROW_NUMBER() OVER (PARTITION BY BrNum,Emp_Num,Trn_Dt ORDER BY _Time ASC)

    FROM Store_

    )

    SELECT a.BrNum,a.Emp_Num,a.Trn_Dt,a._Time,

    ISNULL(CAST(STUFF(CONVERT(char(5),DATEADD(minute,DATEDIFF(minute,b.Trn_Dt_Time,a.Trn_Dt_Time),0),114),3,1,'') as int),0)

    FROM cte a

    LEFT JOIN cte b ON b.BrNum = a.BrNum AND b.Emp_Num = a.Emp_Num AND b.Trn_Dt = a.Trn_Dt AND b.RowNum = a.RowNum - 1

    ORDER BY a.Trn_Dt ASC,a.Emp_Num ASC

    Far away is close at hand in the images of elsewhere.
    Anon.

  • @david-2 thank you but it didn't work and run for almost 1 hours, my intentions were to get a time comparison between the last record from a same employee number and from a time perspective for if the was 0800 and next was 0900 the time difference was 1 hour, another example if it was 1005 next was 1020 then the difference was 20 minutes. The sample I gave was misunderstood - so I provided the true fields below.

    Here is an example of what I was hoping to get back from the rows off data

    TLR_Br_Num|TLR_Emp_Num|TLR_Trn_Dt|TLR_Trn_Tm|Date_Time|RowNum

    00001|12345|01052015|0820|20150105 08:20:00|0

    00001|12345|01052015|0824|20150105 08:24:00|4 a difference of 4 minutes from the last transaction

    00001|23456|01052015|0825|20150105 08:25:00|0 New Employee

    00001|23456|01052015|0830|20150105 08:30:00|5

    SQL Query

    ;WITH cte_TELLER(TLR_Br_Num,TLR_Emp_Num,TLR_Trn_Dt,TLR_Trn_Tm, Date_Time,RowNum)

    AS (

    SELECT TLR_Br_Num,TLR_Emp_Num,TLR_Trn_Dt,TLR_Trn_Tm,

    CAST(CONVERT(char(10),TLR_Trn_Dt,120)+' '+STUFF(TLR_Trn_Tm,3,0,':') as datetime) AS Date_Time,

    ROW_NUMBER() OVER (PARTITION BY TLR_Br_Num,TLR_Emp_Num,TLR_Trn_Dt,TLR_Trn_Tm ORDER BY TLR_Trn_Tm ASC)AS RowNum

    FROM dbo.TLR

    WHERE TLR_BR_Num = '00001' and TLR_Mo = ' 1' and TLR_Yr = '2015'

    )

    SELECT a.TLR_Br_Num,a.TLR_Emp_Num,a.TLR_Trn_Dt,a.TLR_Trn_Tm,

    ISNULL(CAST(STUFF(CONVERT(char(5),DATEADD(minute,DATEDIFF(minute,b.TLR_Trn_Tm,a.TLR_Trn_Tm),0),114),3,1,'') as int),0)

    FROM cte_TELLER a LEFT JOIN cte_TELLER b ON b.TLR_Br_Num = a.TLR_Br_Num AND b.TLR_Emp_Num = a.TLR_Emp_Num

    AND b.TLR_Trn_Dt = a.TLR_Trn_Dt AND b.TLR_Trn_Tm = a.TLR_Trn_Tm AND b.RowNum = a.RowNum - 1

    ORDER BY a.TLR_Trn_Dt ASC,a.TLR_Emp_Num ASC

  • but it didn't work

    My query (with changed column names) against the new sample produced the results shown.

    run for almost 1 hours

    This solution will not scale well as the joining the cte will cause table scans, the more data there is the longer it will take.

    If this is a one off query then the time does not matter but if it is a regularly used query I would suggest changing the design to add the last transaction datetime or calculated time difference each time a row is inserted

    Far away is close at hand in the images of elsewhere.
    Anon.

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply