CODEWITHSUNDEEP
sql-server
Vannessa
Vannessa

Reputation: 183

Add condition in SQL Query

I want to add a SQL condition in my existing query, to return NULL if the below condition is reached.

I have 2 columns SHIFTA_START and SHIFTA_END.

Example:

Hence I want to condition 

IF DATEPART SHIFTA_START = DATEPART SHIFTA_END   
   AND SHIFTA_START > SHIFTA_END  
THEN return NULL

How do I make code this in SQL? Help is appreciated, thank You so much! ^_^

Upvotes: 4

Views: 6772

Answers (5)

Rich Benner
Rich Benner

Reputation: 8113

You're after a case expression, you want to convert the dates to date rather than datetime too (I think that's what you're trying to do).

SELECT
CASE
    WHEN CONVERT(date, SHIFTA_START) = CONVERT(date, SHIFTA_END)
        AND SHIFTA_START > SHIFTA_END
        THEN NULL
    ELSE CASE 
            WHEN CONVERT(date, SHIFTA_START) = CONVERT(date, SHIFTA_END)                         
                THEN DATEDIFF(mi, SHIFTA_START, SHIFTA_END)
            ELSE 'Different Days' 
         END
   END AS TimeWorked
FROM TableName

The calculation above will give you the following values;

  • start and end on same day BUT start after end = NULL
  • start and end on same day AND start before end (correct) = minutes between start and end
  • start and end on different days = 'Different Days'

Upvotes: 2

Ragesh
Ragesh

Reputation: 738

Below query will give the required result :-

declare @test table
(SHIFTA_START datetime NOT NULL,
 SHIFTA_END datetime NOT NULL
)

Insert into @test
values('2016-03-10 12:15:00 PM','2016-03-10 12:30:00 AM')

select 
CASE WHEN ((datepart(dd,SHIFTA_START) = datepart(dd,SHIFTA_END)) AND (cast(SHIFTA_START as time) > cast(SHIFTA_END as time)))
THEN NULL 
ELSE '' END AS RESULT from @test

Result

NULL

Upvotes: 1

sagi
sagi

Reputation: 40481

Use CASE EXPRESSION :

SELECT t.*,
       CASE WHEN CAST(SHIFTA_START as DATE) = CAST(SHIFTA_END as DATE)
                 AND SHIFTA_START > SHIFTA_END 
            THEN NULL
            ELSE SomeOtherValue
       END as Your_Ind_Column
FROM YourTable

This will result with the entire data set, and an indication column. I didn't see how only this column would help you , but if you want only that, remove t.*, from the query.

Upvotes: 2

Aleksander Stankiewicz
Aleksander Stankiewicz

Reputation: 550

In simple cases (like simple condition leading to TRUE or FALSE) you can use inline version of conditional values supported by IIF() function...

Upvotes: 1

Andrey Nadezhdin
Andrey Nadezhdin

Reputation: 126

Use CASE WHEN condition. Here is an example:

SELECT 
    CASE WHEN 
        DATEPART SHIFTA_START = DATEPART SHIFTA_END
        AND SHIFTA_START > SHIFTA_END 
    THEN
        NULL 
    ELSE
        YOUR_COLUMN
    END
FROM YOUR_TABLE

Upvotes: 0

Related Questions