Calculate timespan between datetime field in multiple records
-
I have a C# program that creates a record when a specific event happens.
To keep this a simple as possible, you can think of it as a timer with a start and stop event.You can see the create statement below.
When the start event happens a record will be created in the table. The field “TimeStamp” holds the time the event started.
When the stop event happens a record will be created in the table. The field “TimeStamp” holds the time the event stopped.
To differentiate between start and stop records, I can look in the UUID field. If the UUID field holds a value, then it’s a start event, and if the UUID field is empty it’s a stop event.My task is to run through all the records and calculate the time between a start and stop event. The calculated time of ALL records must be added together to create a sum grouped per ProdNo field.
To complicate matters it cannot be guarantied that the first record is a start event. And also it cannot be guarantied that the last event is a stop event.If this can be done – then the next task will be to also group the result by date. E.g. calculate time of all records grouped per ProdNo per day !!
I’m a complete newbie to SQL programming. I can do simple select statements on single tables so I’m in over my head with this one!
I’m sure I can work out a solution in C# by selecting all records into a recordset and then run through that. But it’s inefficient and not the right way to do it. It makes no sense taking out the data from the SQL server to process them in a external program simply because I’m missing the skills to do this I SQL.I was hoping to make a view that can produce the result or a stored procedure I can call to get the result. But I'm open to ideas.
Thanks
This is the create statement of the table:
CREATE TABLE [dbo].[tTransactions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GUID] [char](50) NOT NULL,
[ProdNo] [int] NOT NULL,
[UUID] [char](50) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
CONSTRAINT [PK_tTransactions] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO -
Could you post some sample data - insert statements so we have something to work with? Basically, you can use LAG with a window to get the "previous" record, and then just subtract the previous value from the current one.
- pietlinden - Tuesday, July 10, 2018 3:11 AM
Hope this formatting is ok.
185 A01DBA65-54EF-45E3-90B9-EAC50FE04E50 2 8E : 04 : 61 : 7E 2018-07-09 10:55:43.107
186 A01DBA65-54EF-45E3-90B9-EAC50FE04E50 0 2E : 29 : 61 : 7E 2018-07-09 10:55:43.610
187 A01DBA65-54EF-45E3-90B9-EAC50FE04E50 1 6E : 16 : 61 : 7E 2018-07-09 10:55:44.090
188 A01DBA65-54EF-45E3-90B9-EAC50FE04E50 0 2018-07-09 10:55:54.070
189 A01DBA65-54EF-45E3-90B9-EAC50FE04E50 0 3E : 75 : 5B : 7E 2018-07-09 10:55:57.780
190 A01DBA65-54EF-45E3-90B9-EAC50FE04E50 0 2018-07-09 10:56:11.993
191 A01DBA65-54EF-45E3-90B9-EAC50FE04E50 0 2E : 29 : 61 : 7E 2018-07-09 10:56:34.793The INSERT statement is done i C# using this code:
sUpdate = "INSERT dbo.tTransactions (GUID, ProdNo, UUID, TimeStamp) VALUES (@GUID, @ProdNo, @UUID, @TimeStamp)";
com.Parameters.AddWithValue("@GUID", guid);
com.Parameters.AddWithValue("@ProdNo", Tags[UUIDIndex].ProductNo);
UUIDStr = Helper.ByteArrayToHexString(Tags[UUIDIndex].UUID);
com.Parameters.AddWithValue("@UUID", UUIDStr);
com.Parameters.AddWithValue("@TimeStamp", DateTime.Now);
com.CommandText = sUpdate;
com.ExecuteNonQuery(); -
Can you create INSERT statements for your sample data, please? Then people will be able to run your CREATE TABLE, then the INSERT and they'll have something which they can use to build a solution to your problem.
The benefit to you is that you will get some working code.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Let's try something and see how it goes:
CREATE TABLE #tTransactions (
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[GUID] char(50) NOT NULL,
ProdNo int NOT NULL,
UUID char(50) NOT NULL,
[TimeStamp] datetime NOT NULL,
);
SET IDENTITY_INSERT #tTransactions ON;INSERT INTO #tTransactions (ID, [GUID], ProdNo, UUID, [TimeStamp])
VALUES (185, 'A01DBA65-54EF-45E3-90B9-EAC50FE04E50', 2, '8E : 04 : 61 : 7E', '2018-07-09 10:55:43.107'),
(186, 'A01DBA65-54EF-45E3-90B9-EAC50FE04E50', 0, '2E : 29 : 61 : 7E', '2018-07-09 10:55:43.610'),
(187, 'A01DBA65-54EF-45E3-90B9-EAC50FE04E50', 1, '6E : 16 : 61 : 7E', '2018-07-09 10:55:44.090'),
(188, 'A01DBA65-54EF-45E3-90B9-EAC50FE04E50', 0, '', '2018-07-09 10:55:54.070'),
(189, 'A01DBA65-54EF-45E3-90B9-EAC50FE04E50', 0, '3E : 75 : 5B : 7E', '2018-07-09 10:55:57.780'),
(190, 'A01DBA65-54EF-45E3-90B9-EAC50FE04E50', 0, '', '2018-07-09 10:56:11.993'),
(191, 'A01DBA65-54EF-45E3-90B9-EAC50FE04E50', 0, '2E : 29 : 61 : 7E', '2018-07-09 10:56:34.793');
SET IDENTITY_INSERT #tTransactions OFF;WITH ORDERED_ROWS AS (
SELECT T.ProdNo, T.[GUID], T.UUID, T.[TimeStamp],
ROW_NUMBER() OVER(PARTITION BY T.ProdNo, CASE WHEN T.UUID = '' THEN 'END' ELSE 'START' END ORDER BY T.[TimeStamp]) AS RowNum,
CONVERT(date, T.[TimeStamp]) AS TheDate
FROM #tTransactions AS T
),
DATA_POINTS AS (SELECT O1.ProdNo, O1.TheDate, DATEDIFF(ms, O1.[TimeStamp], O2.[TimeStamp]) AS TimeDiffMSecs
FROM ORDERED_ROWS AS O1
INNER JOIN ORDERED_ROWS AS O2
ON O1.ProdNo = O2.ProdNo
AND O1.RowNum = O2.RowNum
AND O1.[TimeStamp] < O2.[TimeStamp]
)
SELECT DP.ProdNo, DP.TheDate, SUM(DP.TimeDiffMSecs) AS TotalTimeMSecs
FROM DATA_POINTS AS DP
GROUP BY DP.ProdNo, DP.TheDate
ORDER BY DP.ProdNo, DP.TheDate;DROP TABLE #tTransactions;
Please post back with whether this works or not, and then we can fix it. And please do follow Phil's advice in the future. When we have to type in your data, that's a disincentive to helping you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, July 10, 2018 6:45 AM
Wow !!! That was fast ! And as far as I can see it works as expected. Looks like you nailed it in first attempt.
But I - on the other hand - messed up. I got it wrong when I looked at the data. Actually when the UUID is empty it's a START event and when the UUID holds data it's a stop event. Sorry for that !!!
I also produced some more data to test with. And I managed to script it as an INSERT statement.
USE [Version3]
GO
SET IDENTITY_INSERT [dbo].[tTransactions] ON
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (185, N'A01DBA65-54EF-45E3-90B9-EAC50FE04E50 ', 2, N'8E : 04 : 61 : 7E ', CAST(N'2018-07-09T10:55:43.107' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (186, N'A01DBA65-54EF-45E3-90B9-EAC50FE04E50 ', 0, N'2E : 29 : 61 : 7E ', CAST(N'2018-07-09T10:55:43.610' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (187, N'A01DBA65-54EF-45E3-90B9-EAC50FE04E50 ', 1, N'6E : 16 : 61 : 7E ', CAST(N'2018-07-09T10:55:44.090' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (188, N'A01DBA65-54EF-45E3-90B9-EAC50FE04E50 ', 0, N' ', CAST(N'2018-07-09T10:55:54.070' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (189, N'A01DBA65-54EF-45E3-90B9-EAC50FE04E50 ', 0, N'3E : 75 : 5B : 7E ', CAST(N'2018-07-09T10:55:57.780' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (190, N'A01DBA65-54EF-45E3-90B9-EAC50FE04E50 ', 0, N' ', CAST(N'2018-07-09T10:56:11.993' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (191, N'A01DBA65-54EF-45E3-90B9-EAC50FE04E50 ', 0, N'2E : 29 : 61 : 7E ', CAST(N'2018-07-09T10:56:34.793' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (192, N'20ACC86B-2E69-47FC-87D0-EB0C6B770F24 ', 2, N'8E : 04 : 61 : 7E ', CAST(N'2018-07-10T16:16:59.170' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (193, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 2, N'8E : 04 : 61 : 7E ', CAST(N'2018-07-10T16:17:52.120' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (194, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 0, N'2E : 29 : 61 : 7E ', CAST(N'2018-07-10T16:17:52.620' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (195, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 1, N'6E : 16 : 61 : 7E ', CAST(N'2018-07-10T16:17:53.097' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (196, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 0, N' ', CAST(N'2018-07-10T16:17:59.973' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (197, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 0, N'2E : 29 : 61 : 7E ', CAST(N'2018-07-10T16:18:01.270' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (198, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 1, N' ', CAST(N'2018-07-10T16:18:03.073' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (199, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 1, N'6E : 16 : 61 : 7E ', CAST(N'2018-07-10T16:18:05.337' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (200, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 2, N' ', CAST(N'2018-07-10T16:18:07.170' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (201, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 2, N'8E : 04 : 61 : 7E ', CAST(N'2018-07-10T16:18:09.703' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (202, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 0, N' ', CAST(N'2018-07-10T16:18:12.030' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (203, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 1, N' ', CAST(N'2018-07-10T16:18:14.070' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (204, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 1, N'6E : 16 : 61 : 7E ', CAST(N'2018-07-10T16:18:14.913' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (205, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 0, N'2E : 29 : 61 : 7E ', CAST(N'2018-07-10T16:18:16.980' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (206, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 2, N' ', CAST(N'2018-07-10T16:18:19.973' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (207, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 0, N' ', CAST(N'2018-07-10T16:18:22.380' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (208, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 2, N'8E : 04 : 61 : 7E ', CAST(N'2018-07-10T16:18:24.943' AS DateTime))
GO
INSERT [dbo].[tTransactions] ([ID], [GUID], [ProdNo], [UUID], [TimeStamp]) VALUES (209, N'06087315-3544-42F3-8F58-9E911D6E0CD7 ', 0, N'2E : 29 : 61 : 7E ', CAST(N'2018-07-10T16:18:27.407' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[tTransactions] OFF
GO - fnillerfnaller - Tuesday, July 10, 2018 9:05 AM
Do you get this result with the existing code? I'm pretty sure you will, as I did, and I even reversed the PARTITION BY values of 'START' and 'END' and got the same results, because those values weren't essential as to what value they held - merely that they be different. Swap them around so your query at least appears to be logical, and I still got the exact same results. I did use your new set of data instead of the old.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, July 10, 2018 10:08 AMfnillerfnaller - Tuesday, July 10, 2018 9:05 AM
Do you get this result with the existing code? I'm pretty sure you will, as I did, and I even reversed the PARTITION BY values of 'START' and 'END' and got the same results, because those values weren't essential as to what value they held - merely that they be different. Swap them around so your query at least appears to be logical, and I still got the exact same results. I did use your new set of data instead of the old.
Yes - that's the same result I got when running your code.
I'm not sure I understand your last comment about "swapping them around" and getting the same value.
Looks like you more or less managed to do what I was searching a solution for.
I - on the other hand - managed to mess it up. In my original post I said that if the UUID holds a value it's a start signal and if it's empty it's a stop signal. But it's the other way around. If the UUID is empty it's a start signal and if it holds a value it's a stop signal.
Hope this makes sense- and sorry for not getting it right the first time ! - fnillerfnaller - Tuesday, July 10, 2018 1:01 PMsgmunson - Tuesday, July 10, 2018 10:08 AMfnillerfnaller - Tuesday, July 10, 2018 9:05 AM
Do you get this result with the existing code? I'm pretty sure you will, as I did, and I even reversed the PARTITION BY values of 'START' and 'END' and got the same results, because those values weren't essential as to what value they held - merely that they be different. Swap them around so your query at least appears to be logical, and I still got the exact same results. I did use your new set of data instead of the old.
Yes - that's the same result I got when running your code.
I'm not sure I understand your last comment about "swapping them around" and getting the same value.
Looks like you more or less managed to do what I was searching a solution for.
I - on the other hand - managed to mess it up. In my original post I said that if the UUID holds a value it's a start signal and if it's empty it's a stop signal. But it's the other way around. If the UUID is empty it's a start signal and if it holds a value it's a stop signal.
Hope this makes sense- and sorry for not getting it right the first time !I did understand that you had messed up your specification. From a logic perspective, however, my solution happens to use a technique that forces the order by the times of the events, so my PARTITION BY clause happening to choose values in the CASE statement portion of 'START' and 'END' is more for show than functionality, as I could have just as easily used 'END' and 'START' (effectively reversing them), or I could also have used 0 and 1, or 'X' and 'Y'. Functionally, as long as they were different, it didn't really matter what the two values were. However, given that someone trying to read the query and also understand what it is doing would benefit from those values actually being "logical" (meaning that they at least give the appearance of being applicable to the situation), it's a better choice to specify 'START' for the condition when the UUID is empty and 'END' when it's not. Does that make it clear?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, July 11, 2018 6:49 AMfnillerfnaller - Tuesday, July 10, 2018 1:01 PMsgmunson - Tuesday, July 10, 2018 10:08 AMfnillerfnaller - Tuesday, July 10, 2018 9:05 AM
Do you get this result with the existing code? I'm pretty sure you will, as I did, and I even reversed the PARTITION BY values of 'START' and 'END' and got the same results, because those values weren't essential as to what value they held - merely that they be different. Swap them around so your query at least appears to be logical, and I still got the exact same results. I did use your new set of data instead of the old.
Yes - that's the same result I got when running your code.
I'm not sure I understand your last comment about "swapping them around" and getting the same value.
Looks like you more or less managed to do what I was searching a solution for.
I - on the other hand - managed to mess it up. In my original post I said that if the UUID holds a value it's a start signal and if it's empty it's a stop signal. But it's the other way around. If the UUID is empty it's a start signal and if it holds a value it's a stop signal.
Hope this makes sense- and sorry for not getting it right the first time !I did understand that you had messed up your specification. From a logic perspective, however, my solution happens to use a technique that forces the order by the times of the events, so my PARTITION BY clause happening to choose values in the CASE statement portion of 'START' and 'END' is more for show than functionality, as I could have just as easily used 'END' and 'START' (effectively reversing them), or I could also have used 0 and 1, or 'X' and 'Y'. Functionally, as long as they were different, it didn't really matter what the two values were. However, given that someone trying to read the query and also understand what it is doing would benefit from those values actually being "logical" (meaning that they at least give the appearance of being applicable to the situation), it's a better choice to specify 'START' for the condition when the UUID is empty and 'END' when it's not. Does that make it clear?
Hi Steve
Sorry for the late reply.
Had a few days off with the family.Anyway - Yes I get your point.
I've spend a lot of time trying to make it work, but even with the great start you gave me, I'm simply not good enough to get a solution that works.
So - I've rewritten my C# code so it orders the records in a more structured way. This means that the code will make sure there are pairs of start and stop actions and they are written together in one record.
To do that I need to store the start value in memory which ofcause has a risk. But this is not bank transactions, so I can live with the risk rather than trying to make the above work.I'll mark the questions an answered.
Thanks a million for your time. I've learned a lot from your input!All the best
Henrik - fnillerfnaller - Saturday, July 14, 2018 5:13 PM
Thanks for the feedback. Glad you were able to find a solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply