none
Trying to create a 36 hour break schedule RRS feed

  • Question

  • So I am attempting to create a system that can look back over the past days of people signing in and out and seeing if there is a 36 hour break between the last out time and the next scan in time and was wondering how to do this.

    I've been trying to use cursors and fetch statements but i feel like it will take to much processing power. I have to do it by id and then get all their times and then somehow parse them. I need to get the last out time and date while comparing this to the next in time. Then have it scroll through the past how ever many days until it finds a time that is greater than 36 hours.

    Any ideas.

    Friday, May 29, 2020 4:04 PM

All replies

  • So I am attempting to create a system that can look back over the past days of people signing in and out and seeing if there is a 36 hour break between the last out time and the next scan in time and was wondering how to do this.

    I've been trying to use cursors and fetch statements but i feel like it will take to much processing power. I have to do it by id and then get all their times and then somehow parse them. I need to get the last out time and date while comparing this to the next in time. Then have it scroll through the past how ever many days until it finds a time that is greater than 36 hours.

    Any ideas.

    Good day Raymond,

    The solution depends on your database structure, and I am not even sure what exactly is your question

    We have no idea about your database structure and we cannot read minds

    As I see it, there are two options:

    (1) Your database already designed and you ask for a query - in this case we need you to provide queries to create the relevant table(s) and to insert some sample data in order to reproduce the database structure

    (2) You ask how to design the database - in this case you need to provide your best attempt and full description fo the system and this is not something we can do in the forum well.

    Please provide the missing information


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, May 29, 2020 11:57 PM
    Moderator
  • There is some vagueness in your post.  Are you performing this check in SQL stored procedures when a user logs into the system?  Is there a method to retrieve the last signout time or last recorded activity?  Do you have an existing check now and want to post it so we can take a look?

    Regarding a potential solution here, you may be able to obtain both values and perform a DATEDIFF() using the Hour parameter:

    DECLARE @lastActivityDateTime DATETIME = '2020-05-28 12:00:00.000',
            @currentDateTime DATETIME = '2020-05-30 12:00:00.000';

    SELECT DATEDIFF(HOUR, @lastActivityDateTime , @currentDateTime ) AS ElapsedTime;

    --RESULT= 48
    • Proposed as answer by Lily Lii Tuesday, June 2, 2020 6:07 AM
    Saturday, May 30, 2020 12:55 PM
  • Hi RaymondJohnson,

    SQL Server doesn't log the logins, there is only failed logins in the SQL Server Error Log, it is a way to use trigger to log all successful logins to a user defined table. If you have completed building one like:

    id    name    sign_in                                         sign_out
    1      sa         2020-02-20 10:13:33.480         2020-02-20 17:11:03.000

    then:

    select id from(
    select id,max(sign_in) sign_in,max(sign_out) sign_out from table group by id)a
    where datediff(hh,sign_in,sign_out) >=36

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Tuesday, June 2, 2020 6:07 AM
    Monday, June 1, 2020 6:01 AM