Not that easy time question when error occures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Not that easy time question when error occures

Author

Topic

rischfre
Starting Member

15 Posts

Posted - 2011-11-03 : 12:35:52

Hi

In one table i get stored all Starting and ending time of my employes.

IDEmploye as Long
Date as date
Time as Time
InOut as Bool (true = IN)

what i m looking for is to make a query / table which stores movments in a more efficient way before displaying it in a report : to separate the IN and the OUT. This should let me as well facilitate the duracion calculation.

IDEmploye
Date
Time
In <-- 2 fields
Out <-- 2 fields

The dificulty is sometimes error occures, and somebody tells to the sistem that he starts his day going out. Or going our 2 times without any entrance...

Does exist a way within SQL in order to stored this correcly (Ex. letting the field In empty in the case that there are 2 followin "OUT")? Or should i write a program going through each record and storing the correct result in a table?

idEmpl Date Time InOut
6 10/01/2011 07:00:01 0 <-- Starts his day with OUT
6 10/01/2011 07:27:13 -1
6 10/01/2011 11:17:42 0
6 10/01/2011 11:52:26 -1
6 10/01/2011 16:46:50 0

would become:
6 10/01/2011 00:00:00 07:00:01
6 10/01/2011 07:27:13 11:17:42
6 10/01/2011 11:52:26 16:46:50

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-03 : 13:03:27

something like

;with cte as
(
select idEmpl, Date, Time InOut, seq = row_number() over (partition by idEmpl order by Date, Time)
from tbl
)
select endday.idEmpl, endday.date, coalesce(strtday.Time, '00:00:00'), endday.Time
(select * from cte where InOut = 0) endday
left join (select * from cte where InOut = -1) strtday
on endday.idEmpl = strtday.idEmpl
and endday.seq = startday.seq + 1
order by idEmpl, endday.date, endday.Time

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 13:15:57

[code]
select t.idEmpl,t.Date,coalesce(t1.Time,'00:00') as TimeIn,t.Time as TimeOut
from table t
outer apply (select max(Time) as Time
from table
where IDEmploye= t.IDEmploye
and Date = t.Date
and Time < t.Time
and InOut = -1
)t1
WHERE t.InOut=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rischfre
Starting Member

15 Posts

Posted - 2011-11-04 : 07:41:51

Hi,

Thanks for answers. I checked your solution visakh16, and found that in case

you have got those data :
idEmp Date time InOut
32 . . 2011.10.28 . 05:36.56 -1
32 . . 2011.10.28 . 14:20:15 0
32 . . 2011.10.28 . 14:20:56 0 <-- 2 Out

your query shows :
idEmp Date TimeIn TimeOut
32 . . 2011-10-28 . 05:36:56 . 14:20:15
32 . . 2011-10-28 . 05:36:56 . 14:20:19 <-- Duplicate of the entrance, in this case should be "00:00:00"

nigelrivett, thank you as well, i did not check yet your code. You ll do so a bit later on

regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 07:50:35

rischfre
Starting Member

15 Posts

Posted - 2011-11-04 : 08:14:43

Hi visakh16,

thanks again for your so quick answer, but your query is not showing anything when there is no OUT after a IN

for Example :
idEmp Date time InOut
58 . . 2011.10.29 . 05:36.56 -1 <-- Not shown as missing the OUT after an IN

or
10 . . 2011.10.28 . 11:17:02 -1
10 . . 2011.10.28 . 14:35:24 0
10 . . 2011.10.28 . 15:26:19 -1 <-- not shown as there is not OUT after this record
10 . . 2011.10.28 . 18:30:44 -1 <-- not shown as there is not OUT after this record (user made a mistake tiping IN instaed of OUT)

Sorry, and thank you very much!

rischfre
Starting Member

15 Posts

Posted - 2011-11-04 : 11:07:54

I did a last try, using the query from visakh16 and addind a "UNION ALL" in order to complete it with missing records. When there are any OUT after each IN.
Obviously this is howfull, but i did not find any other way...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 12:47:54

quote:
Originally posted by rischfre

Hi visakh16,

thanks again for your so quick answer, but your query is not showing anything when there is no OUT after a IN

for Example :
idEmp Date time InOut
58 . . 2011.10.29 . 05:36.56 -1 <-- Not shown as missing the OUT after an IN

or
10 . . 2011.10.28 . 11:17:02 -1
10 . . 2011.10.28 . 14:35:24 0
10 . . 2011.10.28 . 15:26:19 -1 <-- not shown as there is not OUT after this record
10 . . 2011.10.28 . 18:30:44 -1 <-- not shown as there is not OUT after this record (user made a mistake tiping IN instaed of OUT)

Sorry, and thank you very much!

if there's no OUT at all how you want output to come for them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rischfre
Starting Member

15 Posts

Posted - 2011-11-04 : 12:53:36

Yes that s the point, even if there are missing records i would like to show all of them.
Even if users made a mistake there were present on should be corrected manually.
That s why i was thinking, in case there is a OUT without an IN :
ID DATE . . . IN . . . OUT
9 2011-10-11 00:00:00 16:31:22

Thank you, i really apreciate time you are spending for me...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 13:02:24

quote:
Originally posted by rischfre

Yes that s the point, even if there are missing records i would like to show all of them.
Even if users made a mistake there were present on should be corrected manually.
That s why i was thinking, in case there is a OUT without an IN :
ID DATE . . . IN . . . OUT
9 2011-10-11 00:00:00 16:31:22

Thank you, i really apreciate time you are spending for me...

nope...thats not what i'm asking
what if there's -1 without 0 after (only IN)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rischfre
Starting Member

15 Posts

Posted - 2011-11-04 : 15:40:43

Let me check if i understand well your question :
What do i expect if an user does not have any OUT after a IN?

my answer is that i would expect as a result :
a record (or several records in case there are several IN without OUT), the only diference would be that the IN result would be "00:00:00"

This example :
69 01/01/2011 13:46:23 -1
69 01/01/2011 18:36:13 -1

IdEmp .. Date . . . . TimeIN . . TimeOUT
69 . . . 01/01/2011 . 00:00:00 . 13:46:23
69 . . . 01/01/2011 . 00:00:00 . 18:36:13

Lets explain another point:
1- Records should be presented by pair (one entrance with one exit) [TimeIN < TimeOUT]
2- All records of the original table should be presented (errors occures, but still should be presented)
3- As the point 1 sais records should be by pair, when occurs a mistake the missing part should be completed (with a 00:00:00 for example or 23:59:59 i don t mind)
4- Idealy all those records should be ordered (by TimeOUT if <>00:00:00 otherwhise TimeIN)

a result may be:

id Date . . . . TimeIN . . TimeOUT
69 11/01/2011 . 00:00:00 . 13:56:21 <-- Error at first record
69 11/01/2011 . 14:00:00 . 15:00:00 <-- Everything fine
69 11/01/2011 . 15:00:01 . 00:00:00 <-- Entrance without exit before next entrance
69 11/01/2011 . 15:01:00 . 16:00:00 <-- everything fine
69 11/01/2011 . 00:00:00 . 17:00:00 <-- Exit without entrance
69 11/01/2011 . 18:01:00 . 00:00:00 <-- Entrance without exit

Thank you so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:52:13

[code]
select t.idEmpl,t.Date,coalesce(t2.Time,'00:00') as TimeIn,t.Time as TimeOut
from table t
outer apply (select max(Time) as Time
from table
where IDEmploye= t.IDEmploye
and Date = t.Date
and Time < t.Time
and InOut = 0
)t1
outer apply (select max(Time) as Time
from table
where IDEmploye= t.IDEmploye
and Date = t.Date
and Time < t.Time
and (Time > t1.Time or t1.Time is null)
and InOut = -1
)t2
WHERE t.InOut=0
union all
select t.idEmpl,t.Date,t.Time as TimeIn,'00:00' as TimeOut
from table t
outer apply (select min(Time) as Time
from table
where IDEmploye= t.IDEmploye
and Date = t.Date
and Time > t.Time
and InOut = -1
)t1
outer apply (select min(Time) as Time
from table
where IDEmploye= t.IDEmploye
and Date = t.Date
and Time > t.Time
and (Time < t1.Time or t1.Time is null)
and InOut = 0
)t2
where t.InOut=-1
and t2.Time is null
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rischfre
Starting Member

15 Posts

Posted - 2011-11-07 : 12:13:59

Thanks, that solves my issue. Thank you visakh16 for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 12:18:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources