February 25, 2015 at 12:47 pm
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
February 26, 2015 at 9:40 pm
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...
February 27, 2015 at 4:12 am
;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.
February 27, 2015 at 3:18 pm
@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
March 2, 2015 at 2:08 am
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.
March 5, 2015 at 5:02 pm
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