shift overlapp!!!!!!!!!

Nandha Kumar Nagarajan

7y903

hi friends

this is my intime & outime&DATE here

i need to check the intime &out time depends upon date

ex:

should not mapped another intime &outtime in same date

if mapped its shows "shift time overlapped"

i dont know query in sql server

hwlp mee friends

DATE

2019-02-02

2019-03-03

2019-04-04

2019-05-05

2019-06-06

INTIME

09:30:00.0000000

10:00:00.0000000

09:30:00.0000000

10:00:00.0000000

OUTTIME

18:30:00.0000000

19:00:00.0000000

18:30:00.0000000

19:00:00.0000000

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mohsin Arif7y ago
hi try this just type take your tables name i generate my artifical table
select tbl2.DateShift,
case when  (select dateCount from (select count (tbl3.DateShift) dateCount,Intime, OutTime from (       -- just place here your tbl name
select '2019-02-02' DateShift,'09:30:00.0000000'    Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-03-03' DateShift,'10:00:00.0000000'    Intime, '19:00:00.0000000'    OutTime Union ALL
select '2019-04-04' DateShift, '09:30:00.0000000'   Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-05-05' DateShift, '09:30:00.0000000'   Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-05-05' DateShift, '09:30:00.0000000'   Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-06-06' DateShift, '10:00:00.0000000'   Intime, '19:00:00.0000000'    OutTime)tbl3 group by tbl3.DateShift ,Intime, OutTime) tblFin where tblFin.dateCount>1 ) > 1 then 'OverLaped '
else tbl2.DateShift end as Remarks, tbl2.Intime,tbl2.OutTime From (                                              -- just place here your tbl name
select * from (
select '2019-02-02' DateShift,'09:30:00.0000000'    Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-03-03' DateShift,'10:00:00.0000000'    Intime, '19:00:00.0000000'    OutTime Union ALL
select '2019-04-04' DateShift, '09:30:00.0000000'   Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-05-05' DateShift, '09:30:00.0000000'   Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-05-05' DateShift, '09:30:00.0000000'   Intime, '18:30:00.0000000'    OutTime Union ALL
select '2019-06-06' DateShift, '10:00:00.0000000'   Intime, '19:00:00.0000000'    OutTime ) tbl ) tbl2
+2
Rajanikant Hawaldar7y ago
Hi Nandhakumar ,

can you post here your query?
+1
Nandha Kumar Nagarajan7y ago
there is only one table
+1
Rajanikant Hawaldar7y ago
Hi Nandhakumar,

How many tables are there?
share details of each tables and columns so it will be helpful to provide solution.

As displayed details i assume you have one tables if that is true then this may help you

select intime, outtime from tablename where date='2019-02-02'
+1