January 16, 2019 at 11:16 am
I have the following rows
| UserShiftID | RowID | StartDateTime | EndDateTime |
| 1116 | 1 | 2017-08-04 11:45:44.0000000 -04:00 | 2017-08-04 16:45:44.0000000 -04:00 |
| 1131 | 2 | 2017-08-08 00:59:34.0000000 -04:00 | 2017-08-08 05:59:34.0000000 -04:00 |
| 1133 | 3 | 2017-08-09 11:41:03.0000000 -05:00 | 2017-08-09 11:50:13.0000000 -05:00 |
| 1134 | 4 | 2017-08-09 11:51:13.0000000 -05:00 | 2017-08-09 12:20:52.0000000 -05:00 |
But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!
January 16, 2019 at 11:22 am
SQL Espo - Wednesday, January 16, 2019 11:16 AMI have the following rows
UserShiftID RowID StartDateTime EndDateTime 1116 1 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00 1131 2 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00 1133 3 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00 1134 4 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00 But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!
Please show us how you want the final output to look. It's not clear to me from your explanation.
Are you expecting a single line of output, regardless of how many rows are in the table? If not, how is one 'set' of rows differentiated from another?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 16, 2019 at 12:38 pm
SQL Espo - Wednesday, January 16, 2019 11:16 AMI have the following rows
UserShiftID RowID StartDateTime EndDateTime 1116 1 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00 1131 2 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00 1133 3 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00 1134 4 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00 But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!
It would really help if you would follow form rules, and actually post DDL along with sample data. Here's my guess at what you might have meant. You didn't even bother to tell us the name of this thing!
CREATE TABLE Timecards
(user_shift_id CHAR(4) NOT NULL PRIMARY KEY,
start_shift_timestamp DATETIME(0) NOT NULL,
end_shift_timestamp DATETIME(0) NOT NULL,
CHECK (start_shift_timestamp < end_shift_timestamp));
I hope you understand that tables have to have keys, and I'm assuming that something starts before it ends. You did not bother to put any of these important constraints in your posting. However, you put in something you called a ROWID, which was a physical count of things. You're still thinking of a deck of punch cards or magnetic tape, aren't you?
If you just want to get the starting timestamp and an ending timestamp for the whole table:
SELECT MIN(start_shift_timestamp) AS strangely_aggregated_start_timestamp,
MAX( end_shift_timestamp) as strangely_aggregated_end_timestamp
FROM Timecards;
But this doesn't really make much sense. What are you trying to do?
Please post DDL and follow ANSI/ISO standards when asking for help.
January 16, 2019 at 12:45 pm
SQL Espo - Wednesday, January 16, 2019 11:16 AMI have the following rows
UserShiftID RowID StartDateTime EndDateTime 1116 1 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00 1131 2 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00 1133 3 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00 1134 4 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00 But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!
when you save row 1, leave the end time null. when you save row 2, update the start time for row 2 and the end time for row one with the same datetime value, and repeat that for each additional row.
January 16, 2019 at 1:17 pm
patrickmcginnis59 10839 - Wednesday, January 16, 2019 12:45 PMSQL Espo - Wednesday, January 16, 2019 11:16 AMI have the following rows
UserShiftID RowID StartDateTime EndDateTime 1116 1 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00 1131 2 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00 1133 3 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00 1134 4 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00 But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!when you save row 1, leave the end time null. when you save row 2, update the start time for row 2 and the end time for row one with the same datetime value, and repeat that for each additional row.
Don't store the end date at all. Use LEAD() to get the end date for one record from the start date from the next record.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 16, 2019 at 7:18 pm
drew.allen - Wednesday, January 16, 2019 1:17 PMpatrickmcginnis59 10839 - Wednesday, January 16, 2019 12:45 PMSQL Espo - Wednesday, January 16, 2019 11:16 AMI have the following rows
UserShiftID RowID StartDateTime EndDateTime 1116 1 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00 1131 2 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00 1133 3 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00 1134 4 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00 But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!when you save row 1, leave the end time null. when you save row 2, update the start time for row 2 and the end time for row one with the same datetime value, and repeat that for each additional row.
Don't store the end date at all. Use LEAD() to get the end date for one record from the start date from the next record.
Drew
Of course we don't know what the OP is using the table for and, of course, I know you already know this but saying it outloud for those that may not... IF the table is being used for temporal joins like you might find when using SCDs (Slowly Changing Dimensions), then the presence of an EndDateTime column will prove useful for performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2019 at 7:25 pm
SQL Espo - Wednesday, January 16, 2019 11:16 AMI have the following rows
UserShiftID RowID StartDateTime EndDateTime 1116 1 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00 1131 2 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00 1133 3 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00 1134 4 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00 But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!
I guess I don't understand why you're willing to destroy what are probably actual end times?
Also, if you want better help in the future, please see the article at the first link in my signature line under "Helpful Links" below.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2019 at 2:10 am
Here is a quick example
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_TIMECARD_05') IS NOT NULL DROP TABLE dbo.TBL_TIMECARD_05;
CREATE TABLE dbo.TBL_TIMECARD_05
(
UserShiftID INT NOT NULL
,RowID INT NOT NULL
,StartDateTime DATETIMEOFFSET NOT NULL
,EndDateTime DATETIMEOFFSET NOT NULL
);
INSERT INTO dbo.TBL_TIMECARD_05 WITH (TABLOCKX) (UserShiftID,RowID,StartDateTime,EndDateTime)
VALUES
(1116,1,'2017-08-04 11:45:44.0000000 -04:00','2017-08-04 16:45:44.0000000 -04:00')
,(1131,2,'2017-08-08 00:59:34.0000000 -04:00','2017-08-08 05:59:34.0000000 -04:00')
,(1133,3,'2017-08-09 11:41:03.0000000 -05:00','2017-08-09 11:50:13.0000000 -05:00')
,(1134,4,'2017-08-09 11:51:13.0000000 -05:00','2017-08-09 12:20:52.0000000 -05:00')
;
SELECT
TC.UserShiftID
,TC.RowID
,TC.EndDateTime AS StartDateTime
,LEAD(TC.StartDateTime,1,NULL) OVER
(
ORDER BY TC.RowID ASC
) AS EndDateTime
FROM dbo.TBL_TIMECARD_05 TC;
Output
UserShiftID RowID StartDateTime EndDateTime
----------- ----------- ---------------------------------- ----------------------------------
1116 1 2017-08-04 16:45:44.0000000 -04:00 2017-08-08 00:59:34.0000000 -04:00
1131 2 2017-08-08 05:59:34.0000000 -04:00 2017-08-09 11:41:03.0000000 -05:00
1133 3 2017-08-09 11:50:13.0000000 -05:00 2017-08-09 11:51:13.0000000 -05:00
1134 4 2017-08-09 12:20:52.0000000 -05:00 NULL
January 17, 2019 at 9:21 am
Jeff Moden - Wednesday, January 16, 2019 7:18 PMdrew.allen - Wednesday, January 16, 2019 1:17 PMpatrickmcginnis59 10839 - Wednesday, January 16, 2019 12:45 PMSQL Espo - Wednesday, January 16, 2019 11:16 AMI have the following rows
UserShiftID RowID StartDateTime EndDateTime 1116 1 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00 1131 2 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00 1133 3 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00 1134 4 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00 But I want in one row is StartDateTime to be EndDateTime from Row1 and then EndDateTime to be the StartDateTime from Row2….and so forth…
Any help is appreciated!when you save row 1, leave the end time null. when you save row 2, update the start time for row 2 and the end time for row one with the same datetime value, and repeat that for each additional row.
Don't store the end date at all. Use LEAD() to get the end date for one record from the start date from the next record.
Drew
Of course we don't know what the OP is using the table for and, of course, I know you already know this but saying it outloud for those that may not... IF the table is being used for temporal joins like you might find when using SCDs (Slowly Changing Dimensions), then the presence of an EndDateTime column will prove useful for performance.
I'm not so sure about that. When an index has multiple range filters, the second range filter does not really add that much to the performance. I haven't done a test comparing the two approaches, and I don't really have time right now, but it would be interesting to compare.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2019 at 11:39 am
Sorry. Newbie to the Forum. Not sure how to format my post.
with
Your_Shift_Tbl as
(
select 1116 as UserShiftID
,convert(decimal(3,1), 1) as RowID
,convert(datetimeoffset, '2017-08-04 16:45:44.0000000 -04:00') as EndDateTime
union
select 1131, 2, '2017-08-08 00:59:34.0000000 -04:00', '2017-08-08 05:59:34.0000000 -04:00'
union
select 1133, 3, '2017-08-09 11:41:03.0000000 -05:00', '2017-08-09 11:50:13.0000000 -05:00'
union
select 1134, 4, '2017-08-09 11:51:13.0000000 -05:00', '2017-08-09 12:20:52.0000000 -05:00'
)
select * from Your_Shift_Tbl
union
select y1.UserShiftID
,(y1.RowID + 0.5) as RowID
,y1.EndDateTime as StartDateTime
,(select min(StartDateTime) from Your_Shift_Tbl y2 where y2.StartDateTime > y1.EndDateTime
)
from Your_Shift_Tbl y1
UserShiftID RowID StartDateTime EndDateTime
----------- --------------------------------------- ---------------------------------- ----------------------------------
1116 1.0 2017-08-04 11:45:44.0000000 -04:00 2017-08-04 16:45:44.0000000 -04:00
1116 1.5 2017-08-04 16:45:44.0000000 -04:00 2017-08-08 00:59:34.0000000 -04:00
1131 2.0 2017-08-08 00:59:34.0000000 -04:00 2017-08-08 05:59:34.0000000 -04:00
1131 2.5 2017-08-08 05:59:34.0000000 -04:00 2017-08-09 11:41:03.0000000 -05:00
1133 3.0 2017-08-09 11:41:03.0000000 -05:00 2017-08-09 11:50:13.0000000 -05:00
1133 3.5 2017-08-09 11:50:13.0000000 -05:00 2017-08-09 11:51:13.0000000 -05:00
1134 4.0 2017-08-09 11:51:13.0000000 -05:00 2017-08-09 12:20:52.0000000 -05:00
1134 4.5 2017-08-09 12:20:52.0000000 -05:00 NULL
,convert(datetimeoffset, '2017-08-04 11:45:44.0000000 -04:00') as StartDateTime
January 18, 2019 at 12:06 pm
In future, I'd suggest a little more whitespace 😛😀
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply