CODEWITHSUNDEEP
sqlsql-server-2012
Real Gem
Real Gem

Reputation: 49

How to remove the rows that returns 'Null' in the results in SQL Server

I have a query that gives me the results as required. But I don't want the rows that displays 'Null' in the final results.

SELECT DISTINCT In_Time, 
    Out_Time,
    "Duration" = CASE
                    WHEN DATEDIFF (hh,In_Time, Out_Time) >= 2 
                       THEN 'Duration was GREATER than 2 hours'
                 END  
FROM 
    CASES  C

The result displayed:

In_Time Out_Time Duration
2021-04-01 09:05:21 2021-04-01 09:28:46 NULL
2021-04-01 09:31:37 2021-04-01 10:33:27 NULL
2021-04-01 08:56:00 2021-04-01 11:46:11 duration was GREATER than 2 hours
2021-04-02 12:50:00 2021-04-02 14:52:26 duration was GREATER than 2 hours
2021-04-02 16:10:57 2021-04-02 17:05:21 NULL

How to remove the rows that returns null and get the results as below

Expected result :

In_Time Out_Time Duration
2021-04-01 08:56:00 2021-04-01 11:46:11 duration was GREATER than 2 hours
2021-04-02 12:50:00 2021-04-02 14:52:26 duration was GREATER than 2 hours

Upvotes: 1

Views: 62

Answers (3)

Real Gem
Real Gem

Reputation: 49

Thank you for the Answers.

I got the Answer when I used the below query

SELECT DISTINCT In_Time, 
    Out_Time,
    "Duration" = 
    CASE
WHEN DATEDIFF (hh,In_Time, Out_Time) >= 2 THEN 'Duration was GREATER than 2 hours'
    END  
    FROM CASES  C
WHERE DATEDIFF(hour, In_Time, Out_Time) >= 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I would suggest:

SELECT DISTINCT In_Time, Out_Time,
      'Duration was GREATER than 2 hours' as Duration
FROM CASES C 
WHERE DATEDIFF(hour, In_Time, Out_Time) >= 2;

Including the string the SELECT seems redundant, though, because all the values are the same.

Another approach is to put the definition of DURATION in the FROM clause so you can use the value in multiple places:

SELECT DISTINCT c.In_Time, c.Out_Time, v.Duration
FROM CASES C CROSS APPLY
     (VALUES (CASE WHEN DATEDIFF(hour, c.In_Time, c.Out_Time) >= 2
                   THEN 'Duration was GREATER than 2 hours'
              END)
     ) v(Duration)
WHERE v.Duration IS NOT NULL;

Upvotes: 0

Martin
Martin

Reputation: 16453

Perhaps it's simplest just to include a WHERE clause for that:

WHERE DATEDIFF (hh,In_Time, Out_Time) >= 2

And in the original SQL:

SELECT DISTINCT In_Time, 
        Out_Time,
        CASE
          WHEN DATEDIFF (hh,In_Time, Out_Time) >= 2 THEN 'Duration was GREATER than 2 hours'
        END AS [Duration]
  FROM  CASES C 
  WHERE DATEDIFF (hh,In_Time, Out_Time) >= 2

Upvotes: 1

Related Questions