To find last next value on the basis of Current and next row values in Sql server 2008

Transact-SQL
1

table:temp_events
columns (entry_date_n_time, id, status)
this table holds employees attendance entries
status='01' means incoming
status='02' means outgoing
sample data and schema will be posted in next post

I am finding last outgoing time which is greater than incoming time and less than 24
hours

this is my query
...
select e.trans_date
, timein = e.time_portion
, timeout = convert(varchar,( select max(e2.entry_date_n_time)
from temp_events e2
where e2.entry_date_n_time between e.entry_date_n_time+0.01 and e.entry_date_n_time + 0.99
and e2.id = e.id
and e2.status = '02'
),108)
, dateout = (select max(e2.trans_date)
from temp_events e2
where e2.entry_date_n_time between e.entry_date_n_time +0.01 and e.entry_date_n_time + 0.99
and e2.id = e.id
and e2.status = '02'
)
from temp_events e
where status='01'
...
and this is the result

QueryResult
QueryResult1440×900 282 KB

The result is almost ok , but now i want a slight ammendment in my above query
look at this

sno entry_date_n_time id status
1 2020-09-14 19:46:44 0000175564 01
2 2020-09-15 05:00:19 0000175564 02
3 2020-09-15 05:00:21 0000175564 02
-- I want to pick line3 05:00:21
4 2020-09-15 09:14:23 0000175564 01
5 2020-09-15 18:00:22 0000175564 02
6 2020-09-15 18:00:23 0000175564 02
-- my query picking line number 18:00:23 (24 hrs)

The point is that
find last outgoing date and time
which lies between current row incoming time and next row incoming time.

2

...
drop table temp_events

CREATE TABLE [dbo].[temp_events](
[entry_date_n_time] [datetime] NOT NULL,
[id] varchar NOT NULL,
[status] varchar NOT NULL,
[trans_date] AS (CONVERT([datetime],CONVERT([varchar],[entry_date_n_time],(111)),0)),
[time_portion] AS (CONVERT([varchar],[entry_date_n_time],(108))),
CONSTRAINT [PK_temp_events] PRIMARY KEY CLUSTERED
(
[entry_date_n_time] ASC,
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

delete from temp_events
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 1 2020 9:00:23:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 1 2020 6:00:21:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 1 2020 6:00:42:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 2 2020 9:05:53:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 2 2020 6:00:50:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 2 2020 6:00:51:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 3 2020 8:59:47:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 3 2020 6:20:10:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 3 2020 6:20:11:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 4 2020 9:05:08:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 4 2020 6:14:43:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 4 2020 6:14:44:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 5 2020 9:06:15:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 5 2020 8:03:21:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 5 2020 8:03:22:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 7 2020 7:57:55:000PM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 8 2020 5:05:02:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 8 2020 5:05:04:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 8 2020 7:57:02:000PM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 9 2020 5:01:12:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 9 2020 5:01:14:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 9 2020 7:57:55:000PM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 10 2020 5:10:35:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 10 2020 5:10:36:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 10 2020 7:55:19:000PM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 11 2020 5:02:26:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 11 2020 5:02:27:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 11 2020 7:58:55:000PM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 12 2020 7:17:50:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 12 2020 7:17:52:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 12 2020 7:57:30:000PM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 13 2020 5:25:52:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 13 2020 5:25:54:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 14 2020 7:46:44:000PM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 15 2020 5:00:19:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 15 2020 5:00:21:000AM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 15 2020 9:14:23:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 15 2020 6:00:22:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 15 2020 6:00:23:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 16 2020 9:12:13:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 16 2020 6:03:05:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 16 2020 6:03:07:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 17 2020 9:04:27:000AM','0000175564','01')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 17 2020 6:00:43:000PM','0000175564','02')
INSERT INTO [temp_events] ([entry_date_n_time],[id],[status])
VALUES('Sep 17 2020 6:00:44:000PM','0000175564','02')

select e.trans_date
, timein = e.time_portion
, timeout = convert(varchar,( select max(e2.entry_date_n_time)
from temp_events e2
where e2.entry_date_n_time
between e.entry_date_n_time+0.01 and e.entry_date_n_time + 0.99
and e2.id = e.id
and e2.status = '02'
),108)
, dateout = (select max(e2.trans_date)
from temp_events e2
where e2.entry_date_n_time between
e.entry_date_n_time +0.01 and e.entry_date_n_time + 0.99
and e2.id = e.id
and e2.status = '02'
)

from temp_events e
where status='01'

/*
select entry_date_n_time,id,status
from temp_events t
where trans_date between '2020/09/14' and '2020/09/15'

sno entry_date_n_time id status
1 2020-09-14 19:46:44.000 0000175564 01
2 2020-09-15 05:00:19.000 0000175564 02
3 2020-09-15 05:00:21.000 0000175564 02 -- I want to pick this line
4 2020-09-15 09:14:23.000 0000175564 01
5 2020-09-15 18:00:22.000 0000175564 02
6 2020-09-15 18:00:23.000 0000175564 02 -- my query picking this line
*/
...

3

hi Mateen

I tried to do this .. Please take a look

If the data changes then the SQL will have to be modified ..

For demo purposes I am using datepart day for the grouping ..
otherwise
i can use year month and day for grouping ..

; with cte as 
(
select datepart(dd, entry_date_n_time ) as grp , * from temp_events 
) 
, cte_rn as 
(
select ROW_NUMBER() over( partition by grp order by entry_date_n_time ) as rn , * from cte 
)
select 
   * 
from 
  cte_rn 
where 
   rn in (1,3) 
order by 
  entry_date_n_time

image
image1077×797 23.8 KB

4

@harishgg1
seems to be ok.
but
trans_date, timein(01), timeout(02) should be in one row.

5

how about this

; with cte as 
(
select min(entry_date_n_time)  as mind ,  max(entry_date_n_time) as maxd 
from temp_events group by cast(entry_date_n_time as date ) 
) 
select b.id ,b.trans_date ,  a.mind , a.maxd  from cte a join temp_events b on a.mind = b.entry_date_n_time 
go

image
image1009×477 14.2 KB

6 
 ; with cte as 
(
	select 
		min(entry_date_n_time)  as mind 
	  , max(entry_date_n_time)  as maxd 
	from 
	  temp_events 
	group by 
		cast(entry_date_n_time as date ) 
) 
select 
    b.id 
  , b.trans_date 
  , a.mind 
  , a.maxd  
from 
   cte a 
     join 
   temp_events b 
          on a.mind = b.entry_date_n_time 
go
7

line number 6 your result is

0000175564 2020-09-07 2020-09-07 19:57:55 2020-09-07 19:57:55
it should be
0000175564 2020-09-07 2020-09-07 19:57:55 2020-09-08 05:05:04
as I showed in my attached jpg.

Please note that my query is doing
find entryouttime which is last in the range
entryintime+1second and entryinime+23.99 hours

8

My query is bringing same dateouttime
for dates 14 and 15 because of a indatetime overlap
which i dont want

9

how do you want to resolve the " indatetime overlap"

you have to have some extra SQL logic to deal with this

10

If there is not overlap give me outdatetime last of 24 hours as i am doing in my query
if there is overlap then just give me next outdatetime.

11

hi

please note the data for " 2020-09-07" is not the same pattern as others ..

how do you want to deal with this scenario ?

image
image768×413 8.23 KB

12

for 14 also its the same as 7

13

image
image751×391 8.61 KB

14

my query is dealing this scenario and bringing
2020-09-07 00:00:00.000 19:57:55 05:05:04 2020-09-08 00:00:00.000
and this is ok

problem is

2020-09-14 00:00:00.000 19:46:44 18:00:23 2020-09-15 00:00:00.000
2020-09-15 00:00:00.000 09:14:23 18:00:23 2020-09-15 00:00:00.000
same dateouttime
here i want
2020-09-14 00:00:00.000 19:46:44 05:00:19 2020-09-15 00:00:00.000
--- --- --- ---
2020-09-15 00:00:00.000 09:14:23 18:00:23 2020-09-15 00:00:00.000
15

hi

please see trans date 13 also .. has only .. '02' status

image
image811×182 5.27 KB

16

hi how do you get this . i mean whats the understanding ..
2020-09-07 00:00:00.000 19:57:55 05:05:04 2020-09-08 00:00:00.000

05:05:04 2020-09-08 00:00:00.000 ?? what is the understanding on how to get this ??

17

status 02 may be many
we are to find last one within the 24 hours range and is ok if no intime overlap
if overlap give me the first one

18

tran date 13 has no status '01'

19

hi

please provide data and example .. for this explaining

Table_Output_Required_Result_V2
Table_Output_Required_Result_V21098×486 32.2 KB
Excel Explain SQL Team
Excel Explain SQL Team1920×1080 23.7 KB

20

ok,
please wait