Add condition in SQL Query

Question

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.

[SHIFTA_START] = employee's clock in time
[SHIFTA_END] = employee's clock out time

Example:

SHIFTA_START: 3/10/2016 12:15:00 PM
SHIFTA_END: 3/10/2016 12:30:00 AM

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! ^_^

hi op, you've got some good answers below. Feel free to check that they work and upvote any that are relevant. Also please select what you consider the best answer as accepted. — Rich Benner
– Rich Benner, Commented Jun 9, 2016 at 7:46

sagi · Accepted Answer · 2016-06-09 07:35:45Z

2

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.

edited Jun 9, 2016 at 7:35

answered Jun 9, 2016 at 7:28

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rich Benner Over a year ago

That's never going to return a result, look at the WHEN statement

sagi Over a year ago

Its CASE EXPRESSION, not WHEN STATEMENT and of course it does!

Rich Benner Over a year ago

So those fields both need to equal each other AND one is greater than the other. I was talking specifically about the WHEN part of your CASE EXPRESSION

sagi Over a year ago

@RichBenner Yes, you are correct, didn't look at the names. Fixed.

Rich Benner · Accepted Answer · 2016-06-09 07:50:01Z

2

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'

edited Jun 9, 2016 at 7:50

answered Jun 9, 2016 at 7:29

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

3 Comments

Rich Benner Over a year ago

Read the OP's question, he's checking that shifta_start and shifta_end are on the same date, he's then looking for anything where the shift end date comes before the start date.

Rich Benner Over a year ago

@sagi I'm assuming the OP is probably calculating the hours worked for employees based upon their clock in and out times. but doesn't want negative values because of incorrect data and also wants to restrict the results to each day (e.g. if somebody doesn't clock out). We've all had to deal with bad data at one point in our careers.

sagi Over a year ago

I know, I already commented on this in my answer and fixed my answer

Ragesh · Accepted Answer · 2016-06-09 07:46:32Z

1

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

answered Jun 9, 2016 at 7:46

Ragesh

7383 silver badges9 bronze badges

1 Comment

Rich Benner Over a year ago

You need to be aware of the issues that you may have with your datepart calculation here. What if the two fields are on the 1st of June and the 1st of July? You'd be better to convert the datetime to date to avoid potential issues with this.

Aleksander Stankiewicz · Accepted Answer · 2016-06-09 07:33:21Z

1

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

answered Jun 9, 2016 at 7:33

Aleksander Stankiewicz

5503 silver badges20 bronze badges

Comments

Andrey Nadezhdin · Accepted Answer · 2016-06-09 07:28:59Z

0

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

answered Jun 9, 2016 at 7:28

Andrey Nadezhdin

1266 bronze badges

1 Comment

Rich Benner Over a year ago

DATEPART SHIFTA_START won't work in SQL I'm afraid, you need to do a calculation here.

Collectives™ on Stack Overflow

Add condition in SQL Query

5 Answers 5

4 Comments

3 Comments

Result

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

3 Comments

Result

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related