03-25-2016 02:56 PM
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.
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.
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,
FROM WORK.DATA_RAW1 t2
NATURAL JOIN surveydata t3
WHERE t2.datetimestamp BETWEEN t3.beddatetime AND t3.wakedatetime
ORDER by id, datetimestamp ;
03-25-2016 03:46 PM
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.
03-25-2016 07:00 PM
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.
03-25-2016 07:06 PM
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?
03-25-2016 08:36 PM
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!
03-25-2016 05:01 PM
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.
Need further help from the community? Please ask a new question.