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(
AppNumberint not null,
Employeenvarchar(64) not null,
Time_generatedatdatetime2 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;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
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
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+.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy