Query to extract datetimes in one table between datetimes found in a second table

[jkurka]

Hello community! Hopefully someone can help, as I am stuck

I have data in table 1 with variables and values with datetimes with each row corresponding to 1/40th of a second, with over 30 days per ID. I have data in table 2 that has the start and stop datetimes for each date for each ID.

I want SQL to pull out data in table 1 between the datetimes for each day in table 2.

For example:

Table 1

datetimestamp                id

21MAY15:06:14:57.250  se10
21MAY15:06:14:57.275  se10
21MAY15:06:14:57.300  se10
21MAY15:06:14:57.325  se10
21MAY15:06:14:57.350  se10
21MAY15:06:14:57.375  se10

Table 2

beddatetime                       wakedatetime

20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000

Currently, the below code pulls out the correct datetimestamp only if beddatetime is after midnight. If beddatetime is prior to midnight then it just stops at midnight and the information until midnight is lost.

PROC SQL;
    CREATE TABLE want AS
    SELECT    t2.x, t2.y, t2.z, t2.lux, t2.event, t2.temp, t2.id, /*t2.date, t2.timestamp,*/ t2.datetimestamp,
            t3.id AS id1, /*t3.date AS date1, t3.waketime, t3.bedtime,*/ t3.day, t3.beddatetime, t3.wakedatetime,
            t3.sleep_h
    FROM WORK.DATA_RAW1 t2
    NATURAL JOIN surveydata t3
    WHERE t2.datetimestamp BETWEEN t3.beddatetime AND t3.wakedatetime
    ORDER by id, datetimestamp ;
QUIT;

Any suggestions?

[PGStats · Accepted solution]

Do not use NATURAL JOIN for this, as it will enforce t2.date=t3.date. 

[jkurka]

Even though they have been coded out? With only the ID, datetimestamp, wakedatetimestamp, and beddatetimestamp, the output is still incorrect.

Any time I have called for an inner join my computer freezes up after 10 or so minutes.

[ballardw]

If T3 is your constraint table you might try a RIGHT JOIN instead of NATURAL JOIN.

[jkurka]

The constraining table has other IDs with respective wakedatetimes and beddatetimes also, and is producing some odd results that still are cutting off the data at midnight.

[ballardw]

Sounds like time to provide example input data with all of the variables (dummy is fine as long as it replicates the problem) and what the actual output desired for that example data may be.

Oh, One other point: Those values are SAS Datetime numeric values aren't they and not character?

[jkurka]

It's solved! Using the NATURAL JOIN was the only join that worked, but I removed date and instead used a generic number to keep the data set in order.

Sometimes just hashing these things out with others gets the brain flowing!

[PGStats]

Natural join joins on all common named variables whether you mention them in your select list or not. 

Build smaller datasets for testing. Get the correct result on those before scaling up to your full datasets.