0

I have select which do not bring me (fully) expected results

from Job d, 
SENSORS_JOBHANDLE s
where s.TimeStamp BETWEEN d.JOB_StartAllocDateTime and d.JOB_EndAllocDateTime

in certain cases resulted output of this select has missed records. Job table has a job id with start and end time and also SENSORS_JOBHANDLE has a record for the selected time range.

how to adjust and what could be a reason for missed records?

Vladimir
  • 3
  • 1
  • `or s.SENSORS_JOBHANDLE BETWEEN d.JOB_StartAllocDateTime and d.JOB_EndAllocDateTime`? – jarlh Mar 03 '20 at 16:18
  • 1
    There's not enough detail here for anyone to do anything but guess. To help us help you, please read through [How To Ask](https://stackoverflow.com/help/how-to-ask). Take a look at this [well-structured question](https://stackoverflow.com/questions/60453346/use-or-conditions-in-where-clause-as-column-names-in-result), then [edit](https://stackoverflow.com/posts/60511449/edit) your question with the details needed to create [a Minimal, Complete, and Verifiable Example for database-related questions](https://dba.stackexchange.com/help/minimal-reproducible-example). – Eric Brandt Mar 03 '20 at 16:24
  • 3
    It's also long time you stopped using 31 year old syntax, and started using the syntax that superseded it 28 years ago: [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – Thom A Mar 03 '20 at 16:26
  • First step for me would be to turn the comma join into a standard join so that I know what I'm joining on. It's hard to tell from this partial snippet. If you include some sample data and expected results it will help readers assist you. – Jacob H Mar 03 '20 at 16:26
  • 1
    Any chance `JOB_EndAllocDateTime` has a value like `2020-03-03` and `s.TimeStamp` has a value like `2020-03-03 16:40:21` for the missing records? Meaning, the end date is not inclusive of the whole day, but rather stops at midnight first instant that morning. – Joel Coehoorn Mar 03 '20 at 16:40

0 Answers0