September 27, 2019 at 12:28 pm
Hi
I would like to get the time difference between rows per application and if the difference of time is less than 5 minutes then count as a single application.
input:
AppNumber Employee Date Time_generatedat
143270137 shiva 23/09/2019 12:24
143270137 shiva 23/09/2019 12:25
143270137 shiva 23/09/2019 12:27
143270137 shiva 23/09/2019 13:16
12339090 kalyan 23/09/2019 08:13
12339090 kalyan 23/09/2019 09:14
output:
AppNumber Employee Date Time_generatedat output
143270137 shiva 23/09/2019 12:24 1
143270137 shiva 23/09/2019 12:25 1
143270137 shiva 23/09/2019 12:27 1
143270137 shiva 23/09/2019 13:16 2
12339090 kalyan 23/09/2019 08:13 1
12339090 kalyan 23/09/2019 09:14 2
September 27, 2019 at 1:35 pm
You could use ROW_NUMBER() to partition by the AppNumber and order by the Time_GeneratedAt columns using SQL Server 2008 R2. Using the LAG() or LEAD() function would be more ideal on a newer version.
Here is a quick way to calculate this count using the following code (I'm on us_english, so I had to adjust the dates a bit):
IF OBJECT_ID(N'dbo.AppTimes', N'U') IS NOT NULL
DROP TABLE dbo.AppTimes;
IF OBJECT_ID(N'tempdb..#StageForAggregation', 'U') IS NOT NULL
DROP TABLE #StageForAggregation;
CREATE TABLE dbo.AppTimes
(
AppNumber bigint NOT NULL,
Employee nvarchar (32) NOT NULL,
Time_GeneratedAt datetime NOT NULL
);
INSERT INTO dbo.AppTimes (AppNumber, Employee, Time_GeneratedAt)
VALUES (143270137, 'shiva', '09/23/2019 12:24'),
(143270137, 'shiva', '09/23/2019 12:25'),
(143270137, 'shiva', '09/23/2019 12:27'),
(143270137, 'shiva', '09/23/2019 13:16'),
(12339090, 'kalyan', '09/23/2019 08:13'),
(12339090, 'kalyan', '09/23/2019 09:14');
WITH CTE
AS
(
SELECT AppNumber,
Employee,
Time_GeneratedAt,
ROW_NUMBER() OVER (PARTITION BY AppNumber ORDER BY Time_GeneratedAt) AS RowNumAsc
FROM dbo.AppTimes
),
CTE2
AS
(
SELECT AppNumber,
Time_GeneratedAt,
ROW_NUMBER() OVER (PARTITION BY AppNumber ORDER BY Time_GeneratedAt) AS RowNumAsc
FROM dbo.AppTimes
)
SELECT CTE.AppNumber,
CTE.Employee,
CTE.Time_GeneratedAt,
COALESCE(DATEDIFF(MINUTE, CTE2.Time_GeneratedAt, CTE.Time_GeneratedAt), 0) AS TimeDiff
INTO #StageForAggregation
FROM CTE
LEFT JOIN CTE2
ON CTE.AppNumber = CTE2.AppNumber
AND CTE.RowNumAsc = CTE2.RowNumAsc + 1;
SELECT *
FROM #StageForAggregation;
SELECT AppNumber,
COUNT(*) AS AppCount
FROM #StageForAggregation
WHERE TimeDiff = 0 OR TimeDiff > 5
GROUP BY AppNumber;
September 27, 2019 at 1:36 pm
/* test data */
drop table if exists #test_apps;
go
create table #test_apps(
AppNumber int not null,
Employee nvarchar(64) not null,
Time_generatedat datetime2 not null);
go
insert #test_apps(AppNumber, Employee, Time_generatedat) values
(143270137,'shiva', '2019-09-23 12:24'),
(143270137,'shiva', '2019-09-23 12:25'),
(143270137,'shiva', '2019-09-23 12:27'),
(143270137,'shiva', '2019-09-23 13:16'),
(12339090 ,'kalyan', '2019-09-23 08:13'),
(12339090 ,'kalyan', '2019-09-23 09:14');
with dt_cte as(
select
*,
lag(Time_generatedat,1) over (partition by AppNumber order by Time_generatedat) Time_lag
from
#test_apps t)
select
*,
iif(datediff(mi, Time_lag, Time_generatedat)>=5,2,1) Variable
from
dt_cte
order by 1, 3 asc;
September 27, 2019 at 1:42 pm
If you are using SQL 2008 you can't use LEAD and LAG. I think you might be able to use the windowed SUM function though.
;WITH Data AS
(
SELECT *
FROM (VALUES (143270137, 'shiva', CONVERT(datetime, '20190923 12:24')),
(143270137, 'shiva', '20190923 12:25'),
(143270137, 'shiva', '20190923 12:27'),
(143270137, 'shiva', '20190923 13:16'),
(143270137, 'shiva', '20190923 14:16'),
(143270137, 'shiva', '20190923 14:17'),
(12339090 , 'kalyan', '20190923 08:13'),
(12339090 , 'kalyan', '20190923 09:14'))T(AppNumber, Employee, DateTime_generatedat)
),
CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY AppNumber ORDER BY DateTime_generatedat ASC) RowNum, *
FROM Data
),
CTE2 AS
(
SELECT a.*,CASE
WHEN ISNULL(DATEDIFF(mi,b.DateTime_generatedat,a.DateTime_generatedat),0) < 5 THEN 0
ELSE 1
END gt5
FROM CTE a
LEFT JOIN CTE b
ON b.AppNumber = a.AppNumber
AND b.RowNum+1 = a.RowNum
)
SELECT x.AppNumber,x.Employee,x.DateTime_generatedat,
SUM(x.gt5) OVER (PARTITION BY x.AppNumber ORDER BY x.DateTime_generatedat)+1 AppN
FROM CTE2 x
ORDER BY AppNumber DESC,DateTime_generatedat
September 27, 2019 at 1:44 pm
I hope below query will work for you.
-- TABLE SCHEMA WITH SAMPLE DATA
DECLARE @Employee TABLE
(
AppNumber VARCHAR(20)
, Employee VARCHAR(50)
, [Date] DATE
, Time_generatedat TIME
)
INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '12:24:00:000')
INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '12:25:00:000')
INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '12:27:00:000')
INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '13:16:00:000')
INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (12339090, 'kalyan', '2019-09-23', '08:13:00:000')
INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (12339090, 'kalyan', '2019-09-23', '09:14:00:000')
-- Microsoft SQL Server 2008R2 & Onwards
; WITH cte_stag_1
AS
(
SELECT AppNumber
, Employee
, [Date]
, Time_generatedat
, ROW_NUMBER() OVER (ORDER BY AppNumber, Employee, [Date], Time_generatedat ASC) AS RowID
FROM @Employee
)
, cte_stag_2
AS
(
SELECT A.AppNumber
, A.Employee
, A.[Date]
, A.Time_generatedat
,
CASE
WHEN DATEDIFF(MINUTE, A.Time_generatedat, ISNULL(B.Time_generatedat, A.Time_generatedat)) < 5
THEN 5
ELSE DATEDIFF(MINUTE, A.Time_generatedat, ISNULL(B.Time_generatedat, A.Time_generatedat))
END AS Diff_In_Minutes
FROM cte_stag_1 A
LEFT JOIN cte_stag_1 B
ON B.AppNumber = A.AppNumber
AND B.Employee = A.Employee
AND B.[Date] = A.[Date]
AND B.RowID = A.RowID + 1
)
SELECT AppNumber
, Employee
, [Date]
, Time_generatedat
, DENSE_RANK() OVER (PARTITION BY AppNumber, Employee, [Date] ORDER BY Diff_In_Minutes ASC) AS [Output]
FROM cte_stag_2
-- Microsoft SQL Server 2012 & Onwards
; WITH cte_stag_1
AS
(
SELECT AppNumber
, Employee
, [Date]
, Time_generatedat
,
CASE
WHEN DATEDIFF(MINUTE, Time_generatedat, ISNULL(LEAD(Time_generatedat) OVER(PARTITION BY AppNumber, Employee, [Date] ORDER BY Time_generatedat ASC), Time_generatedat)) < 5
THEN 5
ELSE DATEDIFF(MINUTE, Time_generatedat, ISNULL(LEAD(Time_generatedat) OVER(PARTITION BY AppNumber, Employee, [Date] ORDER BY Time_generatedat ASC), Time_generatedat))
END AS Diff_In_Minutes
FROM @Employee
)
SELECT AppNumber
, Employee
, [Date]
, Time_generatedat
, DENSE_RANK() OVER (PARTITION BY AppNumber, Employee, [Date] ORDER BY Diff_In_Minutes ASC) AS [Output]
FROM cte_stag_1
My blog : https://sqlservercarpenter.com/
LinkedIn : https://in.linkedin.com/in/brahmanand-shukla
September 27, 2019 at 2:01 pm
Window functions make queries like this easy. Assuming you can't upgrade your SQL Server past 2008, can you offload your query to a newer version of SQL Server which supports window functions? If this is a reporting query, you should be able to keep a current copy of your data on the newer server.
September 27, 2019 at 4:34 pm
Jonathan's is the most correct. Mine only mimics your demo output and it's 2012+.
September 27, 2019 at 7:11 pm
I don't think going back just 1 row will be accurate, at least as I understand the requirements (which, of course, could be incorrect). For example, assume times of:
12:24; 12:25; 12:27; 12:30
I would think 12:30 should start a new group, being 6 minutes past 12:24, but it is still within 3 minutes of 12:27. Is that correct or not? Not sure, but it's a legitimate possibility.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply