Time and attendance












-3















I have a table with below data



EMPID   |  DEVICE     |    EVENTTIME
-----------------------------------------
112 | READ_IN | 2018-11-02 07:00:00.000
112 | READ_IN | 2018-11-02 08:00:00.000
112 | READ_OUT | 2018-11-02 12:00:00.000
112 | READ_IN | 2018-11-02 13:00:00.000
112 | READ_OUT | 2018-11-02 16:00:00.000


I need a select query to achieve below data:



ID_Emp  |Date          |TimeIn |TimeOut|Hours




112     |02/11/2018    |8:00   |16:00  |7:00


In my table, the employee came at 7:00 but he didn't do his work then after one hour he came back and work. He took his lunch break at 12:00-13:00 and left his work at 16:00. So his total working hours will be 7 hours.










share|improve this question




















  • 4





    Are you using MySQL or Oracle ?

    – Madhur Bhaiya
    Nov 24 '18 at 11:06






  • 2





    And can there be consecutive 'read_out's. If so, what happens then?

    – Strawberry
    Nov 24 '18 at 11:25











  • I removed the incompatible database tags. Please tag only with the database you are really using.

    – Gordon Linoff
    Nov 24 '18 at 12:05
















-3















I have a table with below data



EMPID   |  DEVICE     |    EVENTTIME
-----------------------------------------
112 | READ_IN | 2018-11-02 07:00:00.000
112 | READ_IN | 2018-11-02 08:00:00.000
112 | READ_OUT | 2018-11-02 12:00:00.000
112 | READ_IN | 2018-11-02 13:00:00.000
112 | READ_OUT | 2018-11-02 16:00:00.000


I need a select query to achieve below data:



ID_Emp  |Date          |TimeIn |TimeOut|Hours




112     |02/11/2018    |8:00   |16:00  |7:00


In my table, the employee came at 7:00 but he didn't do his work then after one hour he came back and work. He took his lunch break at 12:00-13:00 and left his work at 16:00. So his total working hours will be 7 hours.










share|improve this question




















  • 4





    Are you using MySQL or Oracle ?

    – Madhur Bhaiya
    Nov 24 '18 at 11:06






  • 2





    And can there be consecutive 'read_out's. If so, what happens then?

    – Strawberry
    Nov 24 '18 at 11:25











  • I removed the incompatible database tags. Please tag only with the database you are really using.

    – Gordon Linoff
    Nov 24 '18 at 12:05














-3












-3








-3








I have a table with below data



EMPID   |  DEVICE     |    EVENTTIME
-----------------------------------------
112 | READ_IN | 2018-11-02 07:00:00.000
112 | READ_IN | 2018-11-02 08:00:00.000
112 | READ_OUT | 2018-11-02 12:00:00.000
112 | READ_IN | 2018-11-02 13:00:00.000
112 | READ_OUT | 2018-11-02 16:00:00.000


I need a select query to achieve below data:



ID_Emp  |Date          |TimeIn |TimeOut|Hours




112     |02/11/2018    |8:00   |16:00  |7:00


In my table, the employee came at 7:00 but he didn't do his work then after one hour he came back and work. He took his lunch break at 12:00-13:00 and left his work at 16:00. So his total working hours will be 7 hours.










share|improve this question
















I have a table with below data



EMPID   |  DEVICE     |    EVENTTIME
-----------------------------------------
112 | READ_IN | 2018-11-02 07:00:00.000
112 | READ_IN | 2018-11-02 08:00:00.000
112 | READ_OUT | 2018-11-02 12:00:00.000
112 | READ_IN | 2018-11-02 13:00:00.000
112 | READ_OUT | 2018-11-02 16:00:00.000


I need a select query to achieve below data:



ID_Emp  |Date          |TimeIn |TimeOut|Hours




112     |02/11/2018    |8:00   |16:00  |7:00


In my table, the employee came at 7:00 but he didn't do his work then after one hour he came back and work. He took his lunch break at 12:00-13:00 and left his work at 16:00. So his total working hours will be 7 hours.







sql crystal-reports report






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 24 '18 at 12:05









Gordon Linoff

763k35296400




763k35296400










asked Nov 24 '18 at 10:59









Rifas BabuRifas Babu

1




1








  • 4





    Are you using MySQL or Oracle ?

    – Madhur Bhaiya
    Nov 24 '18 at 11:06






  • 2





    And can there be consecutive 'read_out's. If so, what happens then?

    – Strawberry
    Nov 24 '18 at 11:25











  • I removed the incompatible database tags. Please tag only with the database you are really using.

    – Gordon Linoff
    Nov 24 '18 at 12:05














  • 4





    Are you using MySQL or Oracle ?

    – Madhur Bhaiya
    Nov 24 '18 at 11:06






  • 2





    And can there be consecutive 'read_out's. If so, what happens then?

    – Strawberry
    Nov 24 '18 at 11:25











  • I removed the incompatible database tags. Please tag only with the database you are really using.

    – Gordon Linoff
    Nov 24 '18 at 12:05








4




4





Are you using MySQL or Oracle ?

– Madhur Bhaiya
Nov 24 '18 at 11:06





Are you using MySQL or Oracle ?

– Madhur Bhaiya
Nov 24 '18 at 11:06




2




2





And can there be consecutive 'read_out's. If so, what happens then?

– Strawberry
Nov 24 '18 at 11:25





And can there be consecutive 'read_out's. If so, what happens then?

– Strawberry
Nov 24 '18 at 11:25













I removed the incompatible database tags. Please tag only with the database you are really using.

– Gordon Linoff
Nov 24 '18 at 12:05





I removed the incompatible database tags. Please tag only with the database you are really using.

– Gordon Linoff
Nov 24 '18 at 12:05












1 Answer
1






active

oldest

votes


















0














At first you need to eliminate time between 12 and 1, I wrote simple where clause for this. After that
I used PIVOT for transposing rows to columns by max EVENTTIME.
And finally, I wrote outermost SELECT query for converting columns to your intended format.



here is the fiddler link: http://sqlfiddle.com/#!4/f1189/10



here is the code:



SELECT 
EMPID,
TO_CHAR(READ_IN, 'HH24:MI') READ_IN,
TO_CHAR(READ_OUT, 'HH24:MI') READ_OUT,
EXTRACT(HOUR FROM READ_OUT - READ_IN) HOUR
FROM (
select * from (
select * from Table1
WHERE
extract(hour from eventtime) not between '12' and '13'
)
PIVOT (
MAX(EVENTTIME)
for DEVICE in ( 'READ_IN' READ_IN, 'READ_OUT' READ_OUT )
)
)


please note that this example only works for oracle.






share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53457430%2ftime-and-attendance%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    At first you need to eliminate time between 12 and 1, I wrote simple where clause for this. After that
    I used PIVOT for transposing rows to columns by max EVENTTIME.
    And finally, I wrote outermost SELECT query for converting columns to your intended format.



    here is the fiddler link: http://sqlfiddle.com/#!4/f1189/10



    here is the code:



    SELECT 
    EMPID,
    TO_CHAR(READ_IN, 'HH24:MI') READ_IN,
    TO_CHAR(READ_OUT, 'HH24:MI') READ_OUT,
    EXTRACT(HOUR FROM READ_OUT - READ_IN) HOUR
    FROM (
    select * from (
    select * from Table1
    WHERE
    extract(hour from eventtime) not between '12' and '13'
    )
    PIVOT (
    MAX(EVENTTIME)
    for DEVICE in ( 'READ_IN' READ_IN, 'READ_OUT' READ_OUT )
    )
    )


    please note that this example only works for oracle.






    share|improve this answer




























      0














      At first you need to eliminate time between 12 and 1, I wrote simple where clause for this. After that
      I used PIVOT for transposing rows to columns by max EVENTTIME.
      And finally, I wrote outermost SELECT query for converting columns to your intended format.



      here is the fiddler link: http://sqlfiddle.com/#!4/f1189/10



      here is the code:



      SELECT 
      EMPID,
      TO_CHAR(READ_IN, 'HH24:MI') READ_IN,
      TO_CHAR(READ_OUT, 'HH24:MI') READ_OUT,
      EXTRACT(HOUR FROM READ_OUT - READ_IN) HOUR
      FROM (
      select * from (
      select * from Table1
      WHERE
      extract(hour from eventtime) not between '12' and '13'
      )
      PIVOT (
      MAX(EVENTTIME)
      for DEVICE in ( 'READ_IN' READ_IN, 'READ_OUT' READ_OUT )
      )
      )


      please note that this example only works for oracle.






      share|improve this answer


























        0












        0








        0







        At first you need to eliminate time between 12 and 1, I wrote simple where clause for this. After that
        I used PIVOT for transposing rows to columns by max EVENTTIME.
        And finally, I wrote outermost SELECT query for converting columns to your intended format.



        here is the fiddler link: http://sqlfiddle.com/#!4/f1189/10



        here is the code:



        SELECT 
        EMPID,
        TO_CHAR(READ_IN, 'HH24:MI') READ_IN,
        TO_CHAR(READ_OUT, 'HH24:MI') READ_OUT,
        EXTRACT(HOUR FROM READ_OUT - READ_IN) HOUR
        FROM (
        select * from (
        select * from Table1
        WHERE
        extract(hour from eventtime) not between '12' and '13'
        )
        PIVOT (
        MAX(EVENTTIME)
        for DEVICE in ( 'READ_IN' READ_IN, 'READ_OUT' READ_OUT )
        )
        )


        please note that this example only works for oracle.






        share|improve this answer













        At first you need to eliminate time between 12 and 1, I wrote simple where clause for this. After that
        I used PIVOT for transposing rows to columns by max EVENTTIME.
        And finally, I wrote outermost SELECT query for converting columns to your intended format.



        here is the fiddler link: http://sqlfiddle.com/#!4/f1189/10



        here is the code:



        SELECT 
        EMPID,
        TO_CHAR(READ_IN, 'HH24:MI') READ_IN,
        TO_CHAR(READ_OUT, 'HH24:MI') READ_OUT,
        EXTRACT(HOUR FROM READ_OUT - READ_IN) HOUR
        FROM (
        select * from (
        select * from Table1
        WHERE
        extract(hour from eventtime) not between '12' and '13'
        )
        PIVOT (
        MAX(EVENTTIME)
        for DEVICE in ( 'READ_IN' READ_IN, 'READ_OUT' READ_OUT )
        )
        )


        please note that this example only works for oracle.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 24 '18 at 11:29









        SimonareSimonare

        7,71311535




        7,71311535






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53457430%2ftime-and-attendance%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

            Calculate evaluation metrics using cross_val_predict sklearn

            Insert data from modal to MySQL (multiple modal on website)