Date overlap or outgoing time missing

Transact-SQL
1

-- SQL SERVER 2008 R2

the problem rows are (look attached jpg post#2)
emp_code,trans_date,timein,timeout,dateout2
'182176','2020-08-24','20:20:09','13:51:20','2020-08-25'
'182176','2020-08-25','08:57:14','13:51:20','2020-08-25'
because timeout + dateout2 is not unique.
this means that there is a missing timeout
that is the employee left out without marking his timeout in front of attendance machine.

What I want

update (I want update not select) column remarks in table @t
frist problem row with 'out missing'
2nd problem row with 'work hours reset'

The following script will create table and insert sample data

declare @t table (emp_code varchar(6), trans_date datetime, timein varchar(8), timeout varchar(8), dateout2 datetime, remarks varchar(15))
insert @t (emp_code , trans_date , timein , timeout , dateout2, remarks)
select '182176','2020-08-20','20:00:59','05:43:24','2020-08-21','' union all
select '182176','2020-08-21','20:14:07','05:30:31','2020-08-22','' union all
select '182176','2020-08-22','20:35:03','05:00:00','2020-08-23','' union all
select '182176','2020-08-24','20:20:09','13:51:20','2020-08-25','problem' union all
select '182176','2020-08-25','08:57:14','13:51:20','2020-08-25','problem' union all
select '182176','2020-08-26','09:29:53','19:00:52','2020-08-26','' union all
select '182176','2020-08-27','09:41:45','13:00:00','2020-08-27',''
select emp_code, trans_date , timein , timeout , dateout2, remarks from @t

after update the result would be

emp_code , trans_date , timein , timeout , dateout2, remarks
182176,2020-08-24,20:20:09,13:51:20,2020-08-25,out missing
182176,2020-08-25,08:57:14,13:51:20,2020-08-25, work hours reset

2

overlap date
overlap date1440×900 336 KB

3

SQL 2008 doesn't have Lead/Lag commands, so you have to use cte

Create table #t (emp_code varchar(6), trans_date datetime, timein varchar(8), timeout varchar(8), dateout2 datetime, remarks varchar(15))
insert #t (emp_code , trans_date , timein , timeout , dateout2, remarks)
select '182176','2020-08-20','20:00:59','05:43:24','2020-08-21','' union all
select '182176','2020-08-21','20:14:07','05:30:31','2020-08-22','' union all
select '182176','2020-08-22','20:35:03','05:00:00','2020-08-23','' union all
select '182176','2020-08-24','20:20:09','13:51:20','2020-08-25','problem' union all
select '182176','2020-08-25','08:57:14','13:51:20','2020-08-25','problem' union all
select '182176','2020-08-26','09:29:53','19:00:52','2020-08-26','' union all
select '182176','2020-08-27','09:41:45','13:00:00','2020-08-27',''
;with cte as (select emp_code , trans_date , timein , timeout , dateout2, Row_number() over (partition by Emp_code order by emp_code, trans_date, DateOut2) as RowNum
			    from #t)

select CurRow.*, Case 
					  when CurRow.dateout2= NextRow.DateOut2 and CurRow.Timeout = NextRow.TimeOut then 'Problem' 
				      when CurRow.dateout2= PrevRow.DateOut2 and CurRow.Timeout = PrevRow.TimeOut then 'Problem' 
				  else '' end as Remarks
  from cte CurRow
	left join cte NextRow
		on CurRow.Emp_code = NextRow.Emp_Code
		and CurRow.RowNum = NextRow.RowNum + 1
	left join cte PrevRow
		on CurRow.Emp_code = PrevRow.Emp_Code
		and CurRow.RowNum + 1 = PrevRow.RowNum
1 Like
6

Yes indeed , i want uniqueness, because i need to pass two different type update statement.
your remarks is
problem rows
problem rows
i want
problem rows1
problem rows2

7

Hi mike01
i will change
NextRow.TimeOut then 'Problem'
PrevRow.TimeOut then 'Problem'

with

NextRow.TimeOut then 'Problem2'
PrevRow.TimeOut then 'Problem1'
and then it is ok for me
i can join on dates and pass separate update statement on the basis of remarks

Thanks and regards.

8

The two remarks can be anything but it should be different.

9

I accidentally selected harishgg1 post as solution. I thought i was selecting yours. Now it seems that i cant change the solution.

10

Hi harishgg1

I want to select mike01's post as solution.

11

hi mateen

does not matter to me ANYTHING at all

i just do my time pass here in this FORUM .. its not like 1 crore rupees i am getting !!!

NJOY .. you are getting what you want NJOY ..
:slight_smile::+1:

if you want to change the solution
.. ask the moderator GRAZ how to .. post him a message !!
Or
someone who knows how can help !!

1 Like
12

Thanks

13

Hi harishgg1

I didn't want you withdraw your posts.

I just thought that mike01 posted first with correct solution so he should get solution flag
and others with likes.

Please forgive me if I irritated you,

I have never disrespected teachers in my entire life.

14

If later version , then using Lead/Lag , is it possible to write a query without using cte.

15

hi mateen

please please dont feel that way !!!

Mike's was the correct solution

The solution i gave has a problem !!
if data is there like this
1 a
2 a
3 d
4 e
5 a
my solution will be wrong .. it will give 1a 2a and 5a as the answer ..

16

ok..

17

Yes it's possible without cte

Cte as
(
Select a plus b
)

Will become

Select from
( Select a plus b )

18

Is it possible without derived table in any version of sql server.

19

I think so

Please check

20

Yes. Non-recursive CTEs can be replaced by derived tables instead, in all versions of SQL Server back to at least SQL 7.0! (that's the earliest SQL Server version I used).

21

Hi ScottPletcher
as Mike01 said in post#3
<<SQL 2008 doesn't have Lead/Lag commands, so you have to use cte>>
I am curious to see a query which is not using CTE or derived table in SQL server 2012 or 2014

22

You would need a derived table, as I stated. I can't imagine why you wouldn't want to use any derived tables ... what is the point of that??