Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

I need help Please :( Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 2:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
I have a table name Employee Logs

table consist of EmpID, Empname,Logdatetime.

I can generate the first in and last out by using max and min. Unfortunately there is an employee who came in at 6 pm and comes out the next day. if i use min and max this is what i got:

EmpID Empname Logdate Timein Timeout
0001 Sample 09/20/2013 6:00pm NULL
0001 Sample 09/21/2013 NULL 9:00am


what i want to bring out is like this

EmpID Empname Logdate Timein Logdate Timeout
0001 Sample 09/20/2013 6:00am 09/21/2013 9:00am


is this possible? i hope someone could help me...thanks in advance.....
Post #1499889
Posted Monday, September 30, 2013 10:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:20 PM
Points: 3,943, Visits: 8,951
Do you have some column to identify the night shift?
Otherwise, you would have to code exactly for that employee and it will fail when another employee takes the night shift.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1500100
Posted Monday, September 30, 2013 10:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
Hi Luis thanks for the reply, yes i dont have a column for the shifting schedule. would you mind to give me some script sample to get the correct output?

thank you
Post #1500258
Posted Tuesday, October 1, 2013 12:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:43 AM
Points: 153, Visits: 2,193
try something like this

create table a(ei int, en varchar(255), logdate datetime, tin varchar(7), tout varchar(7))
insert into a values(0001, 'Sample', '20.09.2013', '6:00pm', NULL)
insert into a values(0001, 'Sample', '21.09.2013', NULL, '9:00am')

select
si.ei, si.en, si.logdate, si.tin, so.tout
from
(select * from a si where tout is null) si
join
(select * from a si where tin is null) so
on
si.ei = so.ei
and so.logdate = dateadd(dd, 1, si.logdate)

ei en logdate tin tout
1 Sample 2013-09-20 00:00:00.000 6:00pm 9:00am
Post #1500269
Posted Tuesday, October 1, 2013 12:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
Hi Just check this one:

create table dbo.Emp
(EmpID varchar(5), Empname varchar(20), Logdate date, Timein time, Timeout time)

insert into dbo.Emp select '1001', 'Steve', '20130928', '11:30 am', '02:30 pm'
insert into dbo.Emp select '1001', 'Steve', '20130928', '03:30 pm', '05:30 pm'
insert into dbo.Emp select '1001', 'Steve', '20130928', '05:45 pm', '08:00 pm'
insert into dbo.Emp select '1002', 'Anthony', '20130928', '08:00 pm', NULL
insert into dbo.Emp select '1002', 'Anthony', '20130929', NULL, '06:00 am'

select A.EmpID, A.Logdate, 
MIN(A.Timein) AS Timein,
CASE WHEN MAX(A.Timeout) IS NULL
THEN (SELECT MIN(B.Timeout) FROM dbo.Emp B
WHERE B.EmpID = A.EmpID)
ELSE MAX(A.Timeout) END AS Timeout
from dbo.Emp A
group by A.EmpID, A.Logdate
having MIN(A.Timein) IS NOT NULL

Post #1500270
Posted Tuesday, October 1, 2013 1:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
Hi SSC-Enthusiastic, thanks for your help, i tried your suggested script it was ok, unfortunately i encounter a problem. if an employee forgot to logout his/her wont come out to the out put. Another problem is if an employee time in at 8am and logout at 5pm it wont come out also to the output.


thanks.
Post #1500281
Posted Tuesday, October 1, 2013 1:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:43 AM
Points: 153, Visits: 2,193
try a full join and 'isnull' to find the missing items

create table a(ei int, en varchar(255), logdate datetime, tin varchar(7), tout varchar(7))
insert into a values(0001, 'Sample', '20.09.2013', '6:00pm', NULL)
insert into a values(0001, 'Sample', '21.09.2013', NULL, '9:00am')
insert into a values(0002, 'Sample2', '20.09.2013', '7:00am', NULL)
insert into a values(0003, 'Sample3', '20.09.2013', '8:00am', NULL)
insert into a values(0003, 'Sample3', '21.09.2013', NULL, '5:00pm')
insert into a values(0004, 'Sample4', '21.09.2013', NULL, '4:00pm')

select
isnull(si.ei, so.ei), isnull(si.en, so.en), isnull(si.logdate, so.logdate), si.tin, so.tout
from
(select * from a si where tout is null) si
full join
(select * from a si where tin is null) so
on
si.ei = so.ei
and so.logdate = dateadd(dd, 1, si.logdate)

1 Sample 2013-09-20 00:00:00.000 6:00pm 9:00am
3 Sample3 2013-09-20 00:00:00.000 8:00am 5:00pm
4 Sample4 2013-09-21 00:00:00.000 NULL 4:00pm
2 Sample2 2013-09-20 00:00:00.000 7:00am NULL

pls note that 'sample3' spans a complete night!
Post #1500287
Posted Tuesday, October 1, 2013 6:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:52 AM
Points: 6,872, Visits: 14,185
sharon.ferrer (9/30/2013)
I have a table name Employee Logs

table consist of EmpID, Empname,Logdatetime.

I can generate the first in and last out by using max and min. Unfortunately there is an employee who came in at 6 pm and comes out the next day. if i use min and max this is what i got:

EmpID Empname Logdate Timein Timeout
0001 Sample 09/20/2013 6:00pm NULL
0001 Sample 09/21/2013 NULL 9:00am


what i want to bring out is like this

EmpID Empname Logdate Timein Logdate Timeout
0001 Sample 09/20/2013 6:00am 09/21/2013 9:00am


is this possible? i hope someone could help me...thanks in advance.....


If you are sure that the results you are currently getting from your query are correct, then a second processing stage will provide the final result set you are looking for. Can you provide some sample data to work with? Either in your source data format or in the output format of your existing query? This should be a CREATE TABLE statement and statement(s) to populate the table.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1500361
Posted Tuesday, October 1, 2013 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
Hi sqlnaive, thank you for your reply, but unfortunately thr script found some error. The NULL was filled with the wrong timeout or timein.

thanks
Post #1500432
Posted Tuesday, October 1, 2013 8:13 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:20 PM
Points: 3,943, Visits: 8,951
Please post DDL, sample data and expected results so we can give you better responses by working on something that will actually look like your real data.
For guidance, please read the article linked on my signature.
We're willing to help but we need you to help us.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1500441
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse