How to make sure that there is no any exist shift have common times ?

Question

0.00/5 (No votes)

See more:

what is the best best practice EF query to check if there are conflicts in the start and end of shifts ?

What I have tried:

i created MySQL table "emp_shifts" with this columns "

------------------------
-- id    ------- INT(11)      --
-- name  ------- VARCHAR(45)  --
-- start ------- TIMESTAMP(5) --
-- end   ------- TIMESTAMP(5) --
-- note  ------- VARCHAR(245) --

how i can validate the new record before inserting it to check if there any conflicts in the times , so i want to make sure that every shift has unique period

i write this query but i don't sure if it will work fine ..

C#

dbEntities DB01 = ConnectionTools.OpenConn();
TimeSpan start = StartInput.TimeSpan;
TimeSpan end = EndInput.TimeSpan;
var CheckUniquePeriod_start = DB01.emp_shifts.Any(u => start >= u.start.Value.TimeOfDay && start <= u.end.Value.TimeOfDay);
if (CheckUniquePeriod_start)
{
    StartInput.ErrorText = strings.MAKE_SURE_THAT_START_TIME_DONT_CONFLICT_WITH_ANOTHER_SHIFT;
    valid = false;
}
var CheckUniquePeriod_end = DB01.emp_shifts.Any(u => end >= u.start.Value.TimeOfDay && end <= u.end.Value.TimeOfDay);
if (CheckUniquePeriod_end)
{
    StartInput.ErrorText = strings.MAKE_SURE_THAT_END_TIME_DONT_CONFLICT_WITH_ANOTHER_SHIFT;
    valid = false;
}

Posted 31-Oct-19 12:35pm

Golden Basim

Updated 1-Nov-19 7:32am

Patrice T

v5

Add a Solution

Comments

Mohibur Rashid 31-Oct-19 23:08pm

The question is very ambiguous.

Golden Basim 1-Nov-19 6:10am

sorry , i updated the question

Richard MacCutchan 1-Nov-19 4:12am

Use a SELECT command to look for records that have conflicting times; whatever you are comparing against.

Golden Basim 1-Nov-19 6:10am

sorry , i updated the question

Richard MacCutchan 1-Nov-19 8:11am

What is the question?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2019-11-01T03:59:00

Quote:
i write this query but i don't sure if it will work fine ..

As programmer, your job is also to make sure your code is working fine.
More or less, you have to test all cases you can think about. The answer is " the code works fine" when it pass all tests.
For each test:
- Feed the database with sample data.
- Test if a new data pass or fail.
- Check that result of test is what is expected.

You speak of shifts, if working shifts like 3x8, the night shift is a special case.

Richard Deeming · Answer 2 · 2019-11-01T07:32:00

If you're dealing with a full DateTime, it's easy: look for shifts which start before the new shift ends, and end after the new shift starts.

A.StartDateTime <= B.EndDateTime && A.EndDateTime >= B.StartDateTime

If you're only looking at the time of day, then it depends on whether or not you have overnight shifts.

If you don't, and the shift start time is always guaranteed to be before the shift end time, then you can use the same pattern:

A.StartTimeOfDay <= B.EndTimeOfDay && A.EndTimeOfDay >= B.StartTimeOfDay

Once you add in overnight shifts (eg: 10PM – 6AM) then things start to get tricky:

If both shifts are overnight, then they overlap. (Both include midnight.)

If neither shift is overnight, then use the same pattern as above:

A.StartTimeOfDay <= B.EndTimeOfDay && A.EndTimeOfDay >= B.StartTimeOfDay

If one shift is overnight and the other isn't, then check whether one starts before the other ends:
```
A.StartTimeOfDay <= B.EndTimeOfDay || A.EndTimeOfDay >= B.StartTimeOfDay
```

Putting that all together will give you something like:

(
    A.StartTimeOfDay > A.EndTimeOfDay
&&
    B.StartTimeOfDay > B.EndTimeOfDay
)
||
(
    A.StartTimeOfDay <= B.EndTimeOfDay
&&
    A.EndTimeOfDay >= B.StartTimeOfDay
)
||
(
    (
        A.StartTimeOfDay > A.EndTimeOfDay
    ||
        B.StartTimeOfDay > B.EndTimeOfDay
    )
&&
    (
        A.StartTimeOfDay <= B.EndTimeOfDay
    ||
        A.EndTimeOfDay >= B.StartTimeOfDay
    )
)

How to make sure that there is no any exist shift have common times ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0