I need help Please :(

  • 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.....

  • 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
  • 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

  • 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)

    eienlogdatetintout

    1Sample2013-09-20 00:00:00.0006:00pm9:00am

  • 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

  • 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.

  • 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)

    1Sample2013-09-20 00:00:00.0006:00pm9:00am

    3Sample32013-09-20 00:00:00.0008:00am5:00pm

    4Sample42013-09-21 00:00:00.000NULL4:00pm

    2Sample22013-09-20 00:00:00.0007:00amNULL

    pls note that 'sample3' spans a complete night!

  • 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

  • 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

  • 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
  • Hi SSCertifiable thanks for the comment here is the query;

    CREATE TABLE EMPTMS

    (Uidfirstname varchar (50),

    Uid1lastname varchar(50),

    Reader int,

    dtdate datetime

    );

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('001','Alvin','1','2013-09-20 6:30:000 ')

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('001','Alvin','2','2013-09-20 17:50:000 ')

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('002','Rebecca','1','2013-09-20 22:10:000 ')

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('003','Aliyah','1','2013-09-20 9:08:000 ')

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('002','Rebecca','2','2013-09-21 7:30:000 ')

    *VIEWS

    CREATE VIEW AS TMS

    (Select uidfirstname as empid, uidlastname

    as empname, convert ( varchar,dtdate,101 ) as logdate,

    , case when reader = 1 then

    Convert ( varchar,dtdate,108 )

    Else null end as timein,

    case when reader =2 then

    Convert (varchar,dtdate,108)

    Else nullend as timeout

    From emptms

    Group by uidfirstname,uidlastname,dtdate,

    reader

    )

    for final result query,

    SELECT EMPID,EMPNAME,

    LOGDATE, MIN(TIMEIN),

    MAX(TIMEOUT)

    FROM TMS

    basically that is the table, i create view and then create query

    for the output.

    thanks.

  • Hi Sharon27,

    Thanks for the DDL and consumable sample data. I added a couple more punches for Alvin and Rebecca to the sample data to test:

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('001','Alvin','1','2013-09-21 6:30:000 ')

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('001','Alvin','2','2013-09-21 17:50:000 ')

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('002','Rebecca','1','2013-09-21 22:10:000 ')

    INSERT INTO EMPTMS

    (uidfirstname,uidlastname,reader,dtdate)

    VALUES ('002','Rebecca','2','2013-09-22 7:30:000 ')

    It appears from the sample data that "IN" punches occur on reader 1 and "OUT" punches occur on reader 2. If this holds true in all cases, this gives you the result you want:

    SELECT c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

    FROM (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS ) c1

    LEFT OUTER JOIN (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS ) c2

    ON c1.uidfirstname = c2.uidfirstname

    AND c1.punch_number + 1 = c2.punch_number

    WHERE c1.reader = 1

    If you're actually working in SQL Server 2005 or above (you posted in a forum for SQL Server 2000 and SQL Server 7), you can use a CTE to make your code a little neater:

    WITH cte_punches AS (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS )

    SELECT c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

    FROM cte_punches c1

    LEFT OUTER JOIN cte_punches c2

    ON c1.uidfirstname = c2.uidfirstname

    AND c1.punch_number + 1 = c2.punch_number

    WHERE c1.reader = 1

    This works in SQL 2005 and above, too:

    WITH cte_punches AS (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS )

    SELECT c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

    FROM cte_punches c1

    CROSS APPLY (SELECT c.dtdate FROM cte_punches c WHERE c1.uidfirstname = c.uidfirstname

    AND c1.punch_number + 1 = c.punch_number) c2

    WHERE c1.reader = 1

    Performance may vary among the different versions, so test them all if necessary.

    Jason Wolfkill

  • Hi wolfkillj thank you for helping me find solution to my problem.

    i tried your script and it was ok, but i found something wrong. I tried to add 1 log for Rebecca which is same day of 2013-9-21 23:10:00.

    this what i got

    001Alvin 2013-09-21 06:30:00.0002013-09-21 17:50:00.000

    002Rebecca2013-09-21 22:10:00.0002013-09-21 23:10:00.000

    002Rebecca2013-09-21 23:10:00.0002013-09-22 07:30:00.000

    which should be like this.

    001Alvin 2013-09-21 06:30:00.0002013-09-21 17:50:00.000

    002Rebecca2013-09-21 22:10:00.0002013-09-22 07:30:00.000

    meaning no matter how many times they punch the reader still need to get the first in and the last out of the employee.

    thanks.

  • Hi Sharon

    Can you confirm which SQL Server version you are using?

    “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

  • Sir 2005.

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply