SQL code to retrieve rows

Question

SQL code to retrieve rows

SIJCOOKE

SSC Veteran

Points: 270
More actions
January 20, 2017 at 2:31 pm

#151075

Hi all, thanks for any help given.
I have a table with two columns and Date2...
e.g.
DateTime1           DateTime2
1/1/2017 10:00      6/1/2017 13:00
12/1/2017 08:00       20/1/2017 14:00
5/2/2017 11:30   16/2/2017 20:15
12/3/2017 09:15   19/3/2017 22:00
...etc, etc...
Is it possible to write an sql statement to fill a table with the datetimes NOT within this two ranges.. e.g, the table would look like this:
DateTime1               DateTime2
6/1/2017 13:00        12/1/2017 08:00
20/1/2017 14:00       5/2/2017 11:30
16/2/2017 20:15       12/3/2017 09:15
...etc, etc...
Any help with this would be appreciated,
thanks,
sijcooke

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

SIJCOOKE - Friday, January 20, 2017 2:31 PM
Hi all, thanks for any help given.
I have a table with two columns and Date2...
e.g.
DateTime1           DateTime2
1/1/2017 10:00      6/1/2017 13:00
12/1/2017 08:00       20/1/2017 14:00
5/2/2017 11:30   16/2/2017 20:15
12/3/2017 09:15   19/3/2017 22:00
...etc, etc...
Is it possible to write an sql statement to fill a table with the datetimes NOT within this two ranges.. e.g, the table would look like this:
DateTime1               DateTime2
6/1/2017 13:00        12/1/2017 08:00
20/1/2017 14:00       5/2/2017 11:30
16/2/2017 20:15       12/3/2017 09:15
...etc, etc...
Any help with this would be appreciated,
thanks,
sijcooke

What do you mean? Find the timespan between the end of the previous record and the current record? Also, please post create table and insert scripts for your table(s)... like this:
CREATE TABLE TimeSpans( SpanID INT IDENTITY, StartTime DATETIME, EndTime DATETIME);

INSERT INTO TimeSpans(StartTime, EndTime) VALUES ('12-Jan-2017 08:00', '20-Jan-2017 14:00') ,('5-Feb-2017 11:30', '16-Feb-2017 20:15') ,('12-Mar-2017 09:15', '19-Mar-2017 22:00') ,('1-Jan-2017 10:00', '6-Jan-2017 13:00');

SIJCOOKE SSC Veteran Points: 270 More actions · Answer 2

pietlinden - Friday, January 20, 2017 3:36 PM
SIJCOOKE - Friday, January 20, 2017 2:31 PM
Hi all, thanks for any help given.
I have a table with two columns and Date2...
e.g.
DateTime1           DateTime2
1/1/2017 10:00      6/1/2017 13:00
12/1/2017 08:00       20/1/2017 14:00
5/2/2017 11:30   16/2/2017 20:15
12/3/2017 09:15   19/3/2017 22:00
...etc, etc...
Is it possible to write an sql statement to fill a table with the datetimes NOT within this two ranges.. e.g, the table would look like this:
DateTime1               DateTime2
6/1/2017 13:00        12/1/2017 08:00
20/1/2017 14:00       5/2/2017 11:30
16/2/2017 20:15       12/3/2017 09:15
...etc, etc...
Any help with this would be appreciated,
thanks,
sijcooke
What do you mean? Find the timespan between the end of the previous record and the current record? Also, please post create table and insert scripts for your table(s)... like this:
CREATE TABLE TimeSpans( SpanID INT IDENTITY, StartTime DATETIME, EndTime DATETIME);
INSERT INTO TimeSpans(StartTime, EndTime) VALUES ('12-Jan-2017 08:00', '20-Jan-2017 14:00') ,('5-Feb-2017 11:30', '16-Feb-2017 20:15') ,('12-Mar-2017 09:15', '19-Mar-2017 22:00') ,('1-Jan-2017 10:00', '6-Jan-2017 13:00');

Hi. Yes that's what I want to do. The row should contain the end time of previous row and the start time of the current row... But I guess there needs to be some sort of IF statement because the first row won't have a previous row and the last row won't have the end date time. Thanks sijcooke

drew.allen SSC Guru Points: 76944 More actions · Answer 3

And what do you want to do if you have date ranges that overlap? Are you really still on SQL 2005? SQL 2012 has a feature that makes this much easier.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA