Select records more recent than one of two date/times in SQL and ESRI definition query

Question

I am an ESRI SQL user- so my query has to start with: SELECT * FROM tablename WHERE:

I have GPS-based vehicle records (snowplows) with a date/time field (called timedate) and I need to either A.) select records that have occurred in the previous x number of hours based on an integer field in a separate table or B.) select records that are newer than a date/time based on a date/time field in a separate table. If it is case A, then the field in case B will be null. If it is case B, then the field in case A will be 0.

Here's what I've written for case A.

SELECT * FROM myDataTable
WHERE(DATEDIFF(hh, TimeDate, GETDATE()) <= (select NumberofHours from myHoursControlTable where District = 2)
AND Fleet = 'District 2'
AND NOT [Speed] = 0
AND ([AssetDesc] LIKE  'Sander/Plow%' AND Input2On = 'On' AND Input1On = 'On')) 

And here's what I've written for case B.

SELECT * FROM myDataTable
WHERE(TimeDate <= (select StartTime from myHoursControlTable where District = 2)
AND Fleet = 'District 2'
AND NOT [Speed] = 0
AND([AssetDesc] LIKE  'Sander/Plow%' AND Input2On = 'On' AND Input1On = 'On')) 

They both work, but can someone help me put it together? When I've tried with a case statement, I keep getting the error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I also have the limitation of having to start with "Select *" since I'm doing this in an ESRI definition query.

Any help is appreciated.

Answer 1

Very quick thought. Probably not the most optimized but Why not simply coming and two WHERE statements using an OR?

SELECT * FROM myDataTable
WHERE(
(
(DATEDIFF(hh, TimeDate, GETDATE()) <= (select NumberofHours from myHoursControlTable where District = 2)
AND Fleet = 'District 2'
AND NOT [Speed] = 0
AND ([AssetDesc] LIKE  'Sander/Plow%' AND Input2On = 'On' AND Input1On = 'On')) 
)
OR
(
(TimeDate <= (select StartTime from myHoursControlTable where District = 2)
AND Fleet = 'District 2'
AND NOT [Speed] = 0
AND([AssetDesc] LIKE  'Sander/Plow%' AND Input2On = 'On' AND Input1On = 'On')) 
)

Update

Since both A) and B) share a large number of conditions, you could combine like below. This is taking for granted you want to catch rows fulfilling conditions EITHER A) OR B).

SELECT * FROM myDataTable
WHERE
  (    
    DATEDIFF(hh, TimeDate, GETDATE()) <= 
    (select NumberofHours from myHoursControlTable where District = 2)
  OR 
    TimeDate <= (select StartTime from myHoursControlTable where District = 2)
  )
AND Fleet = 'District 2'
AND NOT [Speed] = 0
AND ([AssetDesc] LIKE  'Sander/Plow%' AND Input2On = 'On' AND Input1On = 'On');