Calculate Duration between Timestamps on Different Rows

[CodeWell]

I have to run a Crystal Report that pulls data from an SQL Server database. User selects a start and end date range for data inclusion at runtime. In this example assume the user chose 5/1/2020 through 5/31/2020. The pool of data returned may look like what's in the table below. A XtionID can occur multiple times within a bucket and multiple times within a month. In most cases there will always be an even number of rows - a start and a stop for each occurrence of a XtionID (see RowID pairs c/d, e/f, g/h, i/j, k/l, m/n). It is possible though to have a stop without a start and a start without a stop when the runtime selected date range catches these rows (see RowIDs a, b, o and p).

NOTE: Here is sample pool of data. The RefRowID column is just here for referencing rows in this StackOverflow post.

RefRowID|Bucket|XtionID|TheTimestamp|XtionType
a|SI|1|5/17/2020 18:00:00|Stop
b|SI|2|5/2/2020 02:00:00|Stop
c|SU|1|5/2/2020 09:00:00|Start
d|SU|1|5/2/2020 15:00:00|Stop
e|TE|1|5/3/2020 10:00:00|Start
f|TE|1|5/3/2020 14:00:00|Stop
g|TE|1|5/27/2020 14:00:00|Start
h|TE|1|5/28/2020 15:00:00|Stop
i|SI|2|5/1/2020 18:00:00|Start
j|SI|2|5/2/2020 09:00:00|Stop
k|SI|2|5/2/2020 15:00:00|Start
l|SI|2|5/3/2020 10:00:00|Stop
m|SU|2|5/3/2020 14:00:00|Start
n|SU|2|5/27/2020 14:00:00|Stop
o|TE|2|5/12/2020 04:00:00|Start
p|SI|1|5/30/2020 17:00:00|Start

I need output that looks like what is in the table below. I have been using ROW_NUMBER partitions, temp tables with just start data, temp tables with just stop data and joining them but I keep running into issues - enough that I'm confusing myself and I couldn't clearly list them here. Anyone have any recommendations?

NOTE: The RefComment column is just here for informational purposes in this StackOverflow post. DaysDuration values are just estimated for simplicity sake when creating this post data.

Bucket|XtionID|StartTimestamp|EndTimestamp|XtionType|DaysDuration|Comment
SI|1|5/1/2020 00:00:00|5/17/2020 18:00:00|StopOnly|17.75|RowID "a" stop time becomes EndTimestamp, no start time so StartTimestamp is set to beginning of month. XtionType becomes "StopOnly" to indicate the duration was based upon deriving a start date.
SI|2|5/1/2020 00:00:00|5/2/2020 02:00:00|StopOnly|1.20|RowID "b" same comment as prev line.
SU|1|5/2/2020 09:00:00|5/2/2020 15:00:00|StartStop|0.25|RowIDs "c/d" start time becomes StartTimestamp, end time becomes EndTimestamp. XtionType becomes "StartStop" to indicate a non-derived duration.
TE|1|5/3/2020 10:00:00|5/3/2020 14:00:00|StartStop|0.12|RowIDs "e/f" same comment as prev line.
TE|1|5/27/2020 14:00:00|5/28/2020 15:00:00|StartStop|1.04|RowIDs "g/h" same comment as prev line.
SI|2|5/1/2020 18:00:00|5/2/2020 09:00:00|StartStop|0.50|RowIDs "i/j" same comment as prev line.
SI|2|5/2/2020 15:00:00|5/3/2020 10:00:00|StartStop|0.80|RowIDs "k/l" same comment as prev line.
SU|2|5/3/2020 14:00:00|5/27/2020 14:00:00|StartStop|24.00|RowIDs "m/n" same comment as prev line.
TE|2|5/12/2020 04:00:00|5/31/2020 24:00:00|StartOnly|18.75|RowID "o" start time becomes StartTimestamp, no end time so EndTimestamp is set to end of month. XtionType becomes "StartOnly" to indicate the duration was based upon deriving an end date.
SI|1|5/30/2020 17:00:00|5/31/2020 24:00:00|StartOnly|1.25|RowID "p" same comment as prev line.

 

[Andrzejek]

If you format your data, it would be a lot easier to see your issue:

[pre]
RefRowID|Bucket|XtionID|TheTimestamp |XtionType
a |SI |1 |5/17/2020 18:00:00|Stop
b |SI |2 | 5/2/2020 02:00:00|Stop
c |SU |1 | 5/2/2020 09:00:00|Start
d |SU |1 | 5/2/2020 15:00:00|Stop
e |TE |1 | 5/3/2020 10:00:00|Start
f |TE |1 | 5/3/2020 14:00:00|Stop
g |TE |1 |5/27/2020 14:00:00|Start
h |TE |1 |5/28/2020 15:00:00|Stop
i |SI |2 | 5/1/2020 18:00:00|Start
j |SI |2 | 5/2/2020 09:00:00|Stop
k |SI |2 | 5/2/2020 15:00:00|Start
l |SI |2 | 5/3/2020 10:00:00|Stop
m |SU |2 | 5/3/2020 14:00:00|Start
n |SU |2 |5/27/2020 14:00:00|Stop
o |TE |2 |5/12/2020 04:00:00|Start
p |SI |1 |5/30/2020 17:00:00|Start
[/pre]
Output:
[pre]
Bucket|XtionID|StartTimestamp |EndTimestamp |XtionType|DaysDuration|Comment
SI |1 | 5/1/2020 00:00:00|5/17/2020 18:00:00|StopOnly | 17.75|RowID "a" stop time becomes EndTimestamp, no start time so StartTimestamp is set to beginning of month. XtionType becomes "StopOnly" to indicate the duration was based upon deriving a start date.
SI |2 | 5/1/2020 00:00:00| 5/2/2020 02:00:00|StopOnly | 1.20|RowID "b" same comment as prev line.
SU |1 | 5/2/2020 09:00:00| 5/2/2020 15:00:00|StartStop| 0.25|RowIDs "c/d" start time becomes StartTimestamp, end time becomes EndTimestamp. XtionType becomes "StartStop" to indicate a non-derived duration.
TE |1 | 5/3/2020 10:00:00| 5/3/2020 14:00:00|StartStop| 0.12|RowIDs "e/f" same comment as prev line.
TE |1 |5/27/2020 14:00:00|5/28/2020 15:00:00|StartStop| 1.04|RowIDs "g/h" same comment as prev line.
SI |2 | 5/1/2020 18:00:00| 5/2/2020 09:00:00|StartStop| 0.50|RowIDs "i/j" same comment as prev line.
SI |2 | 5/2/2020 15:00:00| 5/3/2020 10:00:00|StartStop| 0.80|RowIDs "k/l" same comment as prev line.
SU |2 | 5/3/2020 14:00:00|5/27/2020 14:00:00|StartStop| 24.00|RowIDs "m/n" same comment as prev line.
TE |2 |5/12/2020 04:00:00|5/31/2020 24:00:00|StartOnly| 18.75|RowID "o" start time becomes StartTimestamp, no end time so EndTimestamp is set to end of month. XtionType becomes "StartOnly" to indicate the duration was based upon deriving an end date.
SI |1 |5/30/2020 17:00:00|5/31/2020 24:00:00|StartOnly| 1.25|RowID "p" same comment as prev line.
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

It helps more if the OP posts the creation and INSERT statements which should be fairly simple and saves time for anyone who wants to assist.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ttCodeWell](
	[RefRowID] [nchar](10) NULL,
	[Bucket] [nchar](10) NULL,
	[XtionID] [int] NULL,
	[TheTimestamp] [datetime] NULL,
	[XtionType] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'a         ', N'SI        ', 1, CAST(N'2020-05-17T18:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'b         ', N'SI        ', 2, CAST(N'2020-05-02T02:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'c         ', N'SU        ', 1, CAST(N'2020-05-02T09:00:00.000' AS DateTime), N'Start     ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'd         ', N'SU        ', 1, CAST(N'2020-05-02T15:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'e         ', N'TE        ', 1, CAST(N'2020-05-03T10:00:00.000' AS DateTime), N'Start     ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'f         ', N'TE        ', 1, CAST(N'2020-05-03T14:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'g         ', N'TE        ', 1, CAST(N'2020-05-27T14:00:00.000' AS DateTime), N'Start     ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'h         ', N'TE        ', 1, CAST(N'2020-05-28T15:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'i         ', N'SI        ', 2, CAST(N'2020-05-01T18:00:00.000' AS DateTime), N'Start     ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'j         ', N'SI        ', 2, CAST(N'2020-05-02T09:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'k         ', N'SI        ', 2, CAST(N'2020-05-02T15:00:00.000' AS DateTime), N'Start     ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'l         ', N'SI        ', 2, CAST(N'2020-05-03T10:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'm         ', N'SU        ', 2, CAST(N'2020-05-03T14:00:00.000' AS DateTime), N'Start     ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'n         ', N'SU        ', 2, CAST(N'2020-05-27T14:00:00.000' AS DateTime), N'Stop      ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'o         ', N'TE        ', 2, CAST(N'2020-05-12T04:00:00.000' AS DateTime), N'Start     ')
GO
INSERT [dbo].[ttCodeWell] ([RefRowID], [Bucket], [XtionID], [TheTimestamp], [XtionType]) VALUES (N'p         ', N'SI        ', 1, CAST(N'2020-05-30T17:00:00.000' AS DateTime), N'Start     ')
GO

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016