shift overlapp!!!!!!!!!

Question

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

Mohsin Arif · Answer

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

Rajanikant Hawaldar · Answer

Hi Nandhakumar , can you post here your query?

Nandha Kumar Nagarajan · Answer

there is only one table

Rajanikant Hawaldar · Answer

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'