How to perform logical AND between two query results-CodePudding

Database is SQL Server 2017.

How to perform logical AND operation between two selects?

Here's the problem: I need to check if there are no executions for a particular process within a time range (count <=0) AND if there are messages in the queue (count >0). If the previous statement is true, then return 1. Any other combination returns 0

To check number of executions

select count(*) as 'workflow_count' 
from MY_TABLE
where 1=1
  and [PROCESS] = 'A'
  and [DATE] > DATEADD(MINUTE, -5, GETDATE())

To check number of messages

select SUM(total) as 'message_count'
from
    (select count(*) total 
     from [dbo].[QUEUE1]
     union all
     select count(*) total 
     from [dbo].[QUEUE2]) s

CodePudding user response：

You can use case and [not] exists (subquery)

select 
   case when (not exists (
      select 1 
      from  TABLE 
      where 1=1
          and [PROCESS] = 'A'
          and [DATE] > DATEADD(MINUTE, -5, GETDATE()))

     ) and exists (
       select 1
       from [dbo].[QUEUE1]
     )
   then 1 else 0 end x;

CodePudding user response：

You can check the first table with a select, and include the check for the second table in the where clause :

SELECT TOP 1 1 AS RESULT
FROM TBL
WHERE 1=1
    AND [PROCESS] = 'A'
    AND [DATE] > DATEADD(MINUTE, -5, GETDATE())
    AND EXISTS(SELECT 1 FROM dbo.QUEUE1)