Time Calculation with two schedule

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

General SQL Server Forums

New to SQL Server Programming

Time Calculation with two schedule

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2014-02-12 : 06:58:34

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 07:36:46

how would you determine which is IN time and which is OUT time? is there a field to indicate that in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-12 : 07:38:09

DECLARE @Time TABLE(EMPID INT,Date VARCHAR(20),Time VARCHAR(20))
Insert into @Time VALUES(8184,'01/01/2014','10:04'),
(8184,'01/01/2014','11:48'),
(8184,'01/01/2014','16:17'),
(8184,'01/01/2014','22:20')

select distinct EmpId
,Date
,case when (select Time ) = Time then '10:04' end 'In1'
,case when (select Time ) = Time then '11:48' end 'Out1'
,case when (select Time ) = Time then '16:17' end 'In2'
,case when (select Time ) = Time then '22:20' end 'Out2'
from @Time

Veera

pnasz
Posting Yak Master

101 Posts

Posted - 2014-02-12 : 08:12:57

it should be based on second table which shows schedule IN and out .
There can be many employee and the in and out should be based on schedule table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 08:31:18

quote:
Originally posted by pnasz

it should be based on second table which shows schedule IN and out .
There can be many employee and the in and out should be based on schedule table.

sorry that doesnt help
How can you determine whether its IN or OUT from that. There can abe always a chance that a person may start little late.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pnasz
Posting Yak Master

101 Posts

Posted - 2014-02-12 : 08:49:57

I have two table one with employee time in out. Employee is having two shift

[EMPID] [Date] [Time]
8184 01/01/2014 10:04
8184 01/01/2014 11:48
8184 01/01/2014 16:17
8184 01/01/2014 22:20

Second table shows Schedule for employee

[EmpID] [SCHIN1] [SCHOUT1] [SCHIN2] [SCHOUT2]
8185 10:00:00 12:00:00 15:00:00 20:00:00

How do i get the detail in this format

EmpID Date In1 out1 In2 out2

8185 01/01/2014 10:04 11:48 16:17 22:20

for schedule 1 i.e IN1 and Out1 time will be between SCHIN1 and SCHOUT2

MIN(CASE WHEN [EntryTime] <= SCHOUT1 THEN [Time] END) IN1
, MAX(CASE WHEN [EntryTime] <= SCHOUT1 THEN [Time] END) OUT1

is it possible todo this way if yes then how we can do it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 07:51:13

nope..that wont be accurate always. you need to have a catgeory or flag field in your employee table to indicate IN OUT status

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources