T-SQL Case When Exists Query Not Producing Expected Results

sql case when multiple values
nested case statement in sql example
sql case statement multiple conditions
how to return multiple values for then clause in an sql case expression
case statement with multiple conditions in sql server
sql case statement in where clause multiple values
optimize case statement sql
sql case multiple then

I've put together a query which should show every record which has events before and after a selected date. With an extra column added that will be set to True/False if the record has events before date and the same for if the record has events after the second date.

Problem is I know there are records in the events table with have events before the first date and not after the second date but they don't appear in my query. Only results that are True for both come out of the query. Not sure what I'm doing wrong.

SELECT DISTINCT DeviceName,dPhone,DSIMID,  Vehicles.reg, DeviceID,
CASE
WHEN EXISTS (SELECT eventID FROM [events] WHERE [events].sysdatetime < 
'2018-09-05 11:46:00.000')
THEN 'True' ELSE 'False' 
END AS [WasReportingBeforeDate],
CASE
WHEN EXISTS (SELECT eventID FROM [events] WHERE [events].sysdatetime > 
'2018-09-05 11:46:00.000')
THEN 'True' ELSE 'False' END AS [WasReportingAfterDate]
FROM [Devices]
INNER JOIN Vehicles ON Vehicles.DevID = Devices.DevID
INNER JOIN [events] ON Devices.DevID = [events].DeviceID;

Current Output Example is:

Z3243   1  1 AA05 AAA 1 True    True

Expected Output:

Z3243   1  1 AA05 AAA 1 True    False

There is no data for this record more recent than the second date so it should return false


You aren't correlating your EXISTS, so if any record exists in the table with [events].sysdatetime > '2018-09-05 11:46:00.000' then your EXISTS will always return true.

You want something like this:

...
CASE WHEN EXISTS (SELECT *
                  FROM [events] e2
                  WHERE e2.sysdatetime > '2018-09-05 11:46:00.000'
                  AND e2.eventID = e.eventID) --here
...
FROM [Devices] d
INNER JOIN Vehicles v ON b.DevID = d.DevID
INNER JOIN [events] e ON d.DevID = e.DeviceID;

How to Execute a SQL Query Only If Another SQL Query Has No , How do you execute a SQL query only if another SQL query has no results? NOT Exists Example: Find the cities that NOT have the column state correspondent in the states table. SELECT * FROM cities c WHERE NOT EXISTS (SELECT * FROM states s WHERE c.state=s.name ); NOT Exists Result:


I would break the query into 2 separate queries and use union all to return the result set back together again. If you need a flag to determine the Events before you could hard code this into the below queries.

   ;WITH EventsBefore AS 
    (
    SELECT DISTINCT
           DeviceName,
           dPhone,
           DSIMID,
           Vehicles.reg,
           DeviceID,
    FROM [Devices]
         INNER JOIN Vehicles ON Vehicles.DevID = Devices.DevID
         INNER JOIN [events] ON Devices.DevID = [events].DeviceID
         INNER JOIN lastEvent ON Devices.DevID = lastEvent.devID;
    WHERE [events].sysdatetime < '2018-09-05 11:46:00.000'

    )

     ,EventsAfter AS 
    (
    SELECT DISTINCT
           DeviceName,
           dPhone,
           DSIMID,
           Vehicles.reg,
           DeviceID,
    FROM [Devices]
         INNER JOIN Vehicles ON Vehicles.DevID = Devices.DevID
         INNER JOIN [events] ON Devices.DevID = [events].DeviceID
         INNER JOIN lastEvent ON Devices.DevID = lastEvent.devID;
    WHERE WHERE [events].sysdatetime > '2018-09-05 11:46:00.000'

    )

    SELECT * FROM EventsBefore
    UNION ALL 
    SELECT * FROM EventsAfter

Best way to test if a row exists in a MySQL table, How do you check if record not exists in SQL? SQL Server Contains Full Text Function Not Returning Expected Results. I can't quite figure out why I am getting unexpected results from the following query/statement. I have included code to replicate the issue (which may not be an issue at all but more a misunderstanding on my part about how contains works).


  1. Get rid of join to events and feel free to remove redundant DISTINCT
  2. Put join predicate into subqueries which are missing it now

like that:

SELECT DeviceName,dPhone,DSIMID,  Vehicles.reg, DeviceID,
CASE
  WHEN EXISTS (SELECT 1 FROM [events] e 
    WHERE e.sysdatetime < '2018-09-05 11:46:00.000'
    AND e.DeviceID = Devices.DevID)
  THEN 'True' ELSE 'False' 
END AS [WasReportingBeforeDate],
CASE
  WHEN EXISTS (SELECT 1 FROM [events] e 
    WHERE e.sysdatetime > '2018-09-05 11:46:00.000'
    AND e.DeviceID = Devices.DevID)
THEN 'True' ELSE 'False' END AS [WasReportingAfterDate]
FROM [Devices]
INNER JOIN Vehicles ON Vehicles.DevID = Devices.DevID

Nest a query inside another query or in an expression by using a , How do you use the results of a query in another query? You’ll probably need to use EXISTS and NOT EXISTS in different parts of your application depending on your business logic. When you run a query such as the ones above, it should be noted that the subquery runs first. The subquery in the EXISTS and NOT EXISTS statements is the query that returns order records. This query runs first.


Dirty Secrets of the CASE Expression, as a field alias cannot return more than one field. I'm writing a query with some CASE expressions and it outputs helper-data columns which help me determine whether or not a specific action is required. I would like to know if I can somehow use the result of a subquery as the output without having to perform the same query twice (between WHEN (subquery) THEN and as the result after THEN )


How to Execute a SQL Query Only if Another SQL Query has no , The CASE expression is one of my favorite constructs in T-SQL. SELECT [​result] = CASE CONVERT(SMALLINT, 1+RAND()*3) WHEN 1 Query A: expression referenced directly in CASE; no ELSE: SELECT x, COUNT(*) IIF() is a function that I expected to fall into this same trap, but this function is  Distribution of values with different query techniques. In this case I am relying on the fact that SQL Server chose to evaluate the expression in the subquery and not introduce it to the searched CASE expression, but this is merely to demonstrate that distribution can be coerced to be more even. In reality this may not always be the choice the optimizer makes, so please don't learn from this little trick.


NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server at , If that predicate returns no rows, they wanted to run another query using a different predicate. only if the first query didn't yield any results (through NOT EXISTS ). What about the inverse case, where the first query doesn't match any over nested loop joins because of a high number of estimated rows). While there is no longer any significant performance advantage, using NOT EXISTS will avoid unexpected results when the subquery’s source data contains NULL values. It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery.