SQL [CASE Query View]

Question

SELECT 
DT, FlowParam, Abs_P, T, Volume, Energy, FlowTime_T,
    (SELECT ' > 1 ' AS Expr1 WHERE (
        (SELECT COUNT(*) AS Expr2
          FROM dbo.BACS_Alarm_1 
          WHERE 
            (DT_T >= dbo.BACS_HourFlow_1.DT_T) AND 
            (DT_T <= dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T)
         ) > 1
     )) AS Something
FROM dbo.BACS_HourFlow_1

I need output field AS Something from BACS_Alarm_1 table if WHERE... = 1 and output Null if = 0 ...and output my message if > 1 (that's already made)

How can I realize this case ?

So if

(SELECT COUNT(*) AS Expr2
  FROM dbo.BACS_Alarm_1 
  WHERE 
    (DT_T >= dbo.BACS_HourFlow_1.DT_T) AND 
    (DT_T <= dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T)
 ) > 1

I output ' > 1 ' as something

if = 1 I output a field from dbo.BACS_Alarm_1 if = 0 I output 0 or null

Answer 1

Leaving most as is, you could write:

SELECT 
DT, FlowParam, Abs_P, T, Volume, Energy, FlowTime_T,
    CASE (
        SELECT COUNT(*) AS Expr2
        FROM dbo.BACS_Alarm_1 
        WHERE (DT_T >= dbo.BACS_HourFlow_1.DT_T) 
        AND   (DT_T <= dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T)
     )   WHEN 0 THEN NULL 
         WHEN 1 THEN ' > 1 ' 
         ELSE 'something else'
     END AS Something
FROM dbo.BACS_HourFlow_1

Still, I think it can be improved a bit...I dont think the subquery is necessary. Since you mentioned values 1 and 0 as possible outcomes, I think you can omit COUNT too:

SELECT DT
,      FlowParam
,      Abs_P
,      T
,      Volume
,      Energy
,      FlowTime_T
,      CASE 
           WHEN dbo.BACS_Alarm_1.DT_T IS NULL THEN NULL
           ELSE ' > 1'
       END   AS Something
FROM        dbo.BACS_HourFlow_1
LEFT JOIN   dbo.BACS_Alarm_1 
ON          dbo.BACS_HourFlow_1.DT_T <= dbo.BACS_Alarm_1.DT_T 
AND         dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T >=         dbo.BACS_Alarm_1DT_T

If there really are more results posible, and the COUNT in the original could yield more than 1, you could write it like this:

SELECT DT
,      FlowParam
,      Abs_P
,      T
,      Volume
,      Energy
,      FlowTime_T
,      CASE COUNT(*) 
           WHEN 0 THEN NULL
           WHEN 1 THEN ' > 1'
           ELSE 'something else'
       END   AS Something
FROM        dbo.BACS_HourFlow_1
LEFT JOIN   dbo.BACS_Alarm_1 
ON          dbo.BACS_HourFlow_1.DT_T <= dbo.BACS_Alarm_1.DT_T 
AND         dbo.BACS_HourFlow_1.DT_T + dbo.BACS_HourFlow_1.FlowTime_T >= dbo.BACS_Alarm_1DT_T
GROUP BY    BACS_HourFlow_1.DT
,           BACS_HourFlow_1.FlowParam
,           BACS_HourFlow_1.Abs_P
,           BACS_HourFlow_1.T
,           BACS_HourFlow_1.Volume
,           BACS_HourFlow_1.Energy
,           BACS_HourFlow_1.FlowTime_T