SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I need help Please :(


I need help Please :(

Author
Message
sharon27
sharon27
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 89
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.....
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16842 Visits: 19122
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
sharon27
sharon27
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 89
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
anastke
anastke
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 2354
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
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4311 Visits: 2774
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


sharon27
sharon27
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 89
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.
anastke
anastke
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 2354
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!
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 19557
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
sharon27
sharon27
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 89
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16842 Visits: 19122
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search