Post reply

Timesum Problem

  • June 20, 2006 at 5:04 am

    #644761

    Hallo, so i have test it in all ways. Its look great. The only Problem is the time 01.01.05 til 21.09.05 its the Format i have written.loginzeit and logoutzeit looks like that

    '1900-01-01 22:00:00' and not '1899-12-30 and 22:00:00'..

    Please what can i do to change it.

    Greetings Thomas

  • June 20, 2006 at 6:30 am

    #644781

    See Last Post!

     

    N 56°04'39.16"
    E 12°55'05.25"

  • June 20, 2006 at 6:47 am

    #644786

    Hallo,

    i have done it and now there are no counts or Time in the Column 22-24 ? I dont Knowh why there are different standards. It every night update from an txt file with an bulkinsert task.

    My head it knocking. Its look like the best i have seen what do you programming.

    Thomas

  • June 20, 2006 at 7:50 am

    #644814

    Hallo,

    i have found the misterium .

    its the Timestamp '22:00:00.000', '23:59:59.999'.

    I write this '22:00:00.000', '23:59:59' and then they count and sum.Do you think its ok??Or change this writing anything ??

    Thanks

    Thomas

  • June 20, 2006 at 8:12 am

    #644819

    No, it will not change the fact that you sometimes have time with '1899-12-30' (ms access standard) and sometimes time with '1900-01-01' (ms sql server standard).

    The problem arises when adding together LoginDate and LoginZeit, or LogoutDate and LogoutZeit.

    Since all dates are calculated as days passed since 1900-01-01, adding '1899-12-30 11:11:11' to that date, and adding '1901-01-01 11:11:11' to the same date produces different dates!

    Date '1899-12-30' is interpreted as day -2, while '1900-01-01' is interpreted as day 0.

     

    See Last Post!

    N 56°04'39.16"
    E 12°55'05.25"

  • June 20, 2006 at 9:22 am

    #644842

    Hi,

     

    I not really sure about what you're asking but it seems you're 'pro-rating' the number of hours being worked according to some attribute of the date, thus the problem appears to similar to a problem of paying 'Overtime'.

    If this is so the case statement might be of help to you, in example,

    Select EmployeeNo, sum((case when DateType='HolidayWeekEnd'  then hours * rate1 when DateType of  'WeekEnd' then hours* rate2 else hours end)) 'HourstoPay' from tblTable

    where Dates between @DateOne and @DateTwo

     

     

    Hope this helps

     

    Dan

  • June 20, 2006 at 10:44 am

    #644880

    Ich hoffe Sie Entdeckung die abschließenden Versionen des Codes nützlich! Schreiben Sie bitte auf Forum, wenn Sie denken, daß der Code gut ist oder es mehr gibt, zum zu tun.

    N 56°04'39.16"
    E 12°55'05.25"

  • June 21, 2006 at 2:56 am

    #644958

    Here is the breakdown of the test data

      LoginDate LoginZeit  LogoutDate  LogoutZeit  Diff  00-06    06-22   22-24   00-06
    2006-05-2400:59:472006-05-2401:09:00553553
    2006-05-2407:58:572006-05-2415:30:0527 06827 068
    2006-05-2408:41:122006-05-2413:00:4115 56915 569
    2006-05-2409:29:082006-05-2411:33:037 4357 435
    2006-05-2410:01:022006-05-2416:30:0423 34223 342
    2006-05-2413:00:372006-05-2416:16:1811 74111 741
    2006-05-2413:32:242006-05-2420:30:0025 05625 056
    2006-05-2413:56:272006-05-2420:30:0823 62123 621
    2006-05-2413:57:322006-05-2418:15:3215 48015 480
    2006-05-2413:59:182006-05-2417:31:5912 76112 761
    2006-05-2413:59:322006-05-2417:14:5711 72511 725
    2006-05-2413:59:402006-05-2416:15:238 1438 143
    2006-05-2414:00:412006-05-2417:31:2012 63912 639
    2006-05-2414:01:012006-05-2420:30:0423 34323 343
    2006-05-2414:02:002006-05-2417:24:3912 15912 159
    2006-05-2416:59:192006-05-2421:10:1215 05315 053
    2006-05-2417:54:122006-05-2422:16:5415 76214 7481 014
    2006-05-2418:44:192006-05-2500:00:5018 99111 7417 20050
    2006-05-2418:59:302006-05-2422:14:3811 70810 830878
    2006-05-2418:59:452006-05-2422:14:5211 70710 815892
    2006-05-2419:59:202006-05-2501:13:2118 8417 2407 2004401
    2006-05-2419:59:412006-05-2500:15:5115 3707 2197 200951
    2006-05-2420:00:112006-05-2501:15:1118 9007 1897 2004511
    2006-05-2420:01:092006-05-2421:59:157 0867 086
    2006-05-2422:50:312006-05-2502:31:3413 2634 1699094
    25377 316553322 00335 75319 007377 316   Zeit
    1238537  Anzahl

    And here is the test data

    -- Populate test data

    DECLARE @LoginLogout TABLE

     (

      Site VARCHAR(55), LoginID INT, NST DECIMAL(10, 0), LoginZeit DATETIME, LogoutZeit DATETIME, LogoutDate DATETIME, Skill1 DECIMAL(5, 0) NULL, Skill2 DECIMAL(5, 0) NULL, Skill3 DECIMAL(5, 0) NULL, DiffInOut INT, Logindate DATETIME

      )

    INSERT @LoginLogout

     (

      Site, LoginID, NST, LoginZeit, LogoutZeit, LogoutDate, Skill1, Skill2, Skill3, DiffInOut, Logindate

      )

    SELECT 'Siteone', 50001, 74587, '1899-12-30 20:01:09.000', '1899-12-30 21:59:15.000', '2006-05-24 00:00:00.000', 100, NULL, NULL,  7086, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50001, 74628, '1899-12-30 09:29:08.000', '1899-12-30 11:33:03.000', '2006-05-24 00:00:00.000', 100, NULL, NULL,  7435, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50002, 74558, '1899-12-30 13:59:18.000', '1899-12-30 17:31:59.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 12761, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50006, 74534, '1899-12-30 22:50:31.000', '1899-12-30 02:31:34.000', '2006-05-25 00:00:00.000', 100, NULL, NULL, 13263, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50007, 74511, '1899-12-30 13:57:32.000', '1899-12-30 18:15:32.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 15480, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50010, 74596, '1899-12-30 13:59:32.000', '1899-12-30 17:14:57.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 11725, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50011, 74538, '1899-12-30 14:02:00.000', '1899-12-30 17:24:39.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 12159, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50011, 74538, '1899-12-30 18:59:45.000', '1899-12-30 22:14:52.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 11707, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50014, 74518, '1899-12-30 13:59:40.000', '1899-12-30 16:15:23.000', '2006-05-24 00:00:00.000', 100, NULL, NULL,  8143, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50014, 74518, '1899-12-30 16:59:19.000', '1899-12-30 21:10:12.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 15053, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50016, 74680, '1899-12-30 18:44:19.000', '1899-12-30 00:00:50.000', '2006-05-25 00:00:00.000', 100, NULL, NULL, 18991, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50018, 74539, '1899-12-30 18:59:30.000', '1899-12-30 22:14:38.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 11708, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50019, 74585, '1899-12-30 08:41:12.000', '1899-12-30 13:00:41.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 15569, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50020, 74519, '1899-12-30 07:58:57.000', '1899-12-30 15:30:05.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 27068, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50025, 74628, '1899-12-30 13:00:37.000', '1899-12-30 16:16:18.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 11741, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50030, 74632, '1899-12-30 13:32:24.000', '1899-12-30 20:30:00.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 25056, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50033, 74545, '1899-12-30 00:59:47.000', '1899-12-30 01:09:00.000', '2006-05-24 00:00:00.000', 100, NULL, NULL,   553, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50033, 74690, '1899-12-30 19:59:41.000', '1899-12-30 00:15:51.000', '2006-05-25 00:00:00.000', 100, NULL, NULL, 15370, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50034, 74599, '1899-12-30 20:00:11.000', '1899-12-30 01:15:11.000', '2006-05-25 00:00:00.000', 100, NULL, NULL, 18900, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50037, 74618, '1899-12-30 14:01:01.000', '1899-12-30 20:30:04.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 23343, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50041, 74681, '1899-12-30 10:01:02.000', '1899-12-30 16:30:04.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 23342, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50045, 74597, '1899-12-30 19:59:20.000', '1899-12-30 01:13:21.000', '2006-05-25 00:00:00.000', 100, NULL, NULL, 18841, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50046, 74561, '1899-12-30 13:56:27.000', '1899-12-30 20:30:08.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 23621, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50048, 74687, '1899-12-30 17:54:12.000', '1899-12-30 22:16:54.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 15762, '2006-05-24 00:00:00.000' UNION ALL

    SELECT 'Siteone', 50053, 74520, '1899-12-30 14:00:41.000', '1899-12-30 17:31:20.000', '2006-05-24 00:00:00.000', 100, NULL, NULL, 12639, '2006-05-24 00:00:00.000'

    DECLARE @fhessen TABLE

     (

      feiertage datetime

      )

    INSERT @fhessen

     (

      feiertage

      )

    SELECT '2006-05-25'

    And here is the function

    CREATE FUNCTION dbo.fnCalculateSeconds

    (

     @LoginDate DATETIME,

     @LoginZeit DATETIME,

     @LogoutDate DATETIME,

     @LogoutZeit DATETIME,

     @InTime DATETIME,

     @OutTime DATETIME,

     @FromCheck BIT

    )

    RETURNS INT

    AS

    BEGIN

     DECLARE @Login DATETIME,

      @Logout DATETIME,

      @Low DATETIME,

      @High DATETIME,

      @Seconds INT

     SELECT @Login = DATEADD(dd, DATEDIFF(dd, 0, @LoginDate), 0) + DATEADD(dd, -DATEDIFF(dd, 0, @LoginZeit), @LoginZeit),

      @Logout = DATEADD(dd, DATEDIFF(dd, 0, @LogoutDate), 0) + DATEADD(dd, -DATEDIFF(dd, 0, @LogoutZeit), @LogoutZeit)

     IF @FromCheck = 1

      SELECT @Low = DATEADD(dd, DATEDIFF(dd, 0, @login), 0) + DATEADD(dd, -DATEDIFF(dd, 0, @InTime), @InTime),

       @High = DATEADD(dd, DATEDIFF(dd, 0, @login), 0) + DATEADD(dd, -DATEDIFF(dd, 0, @OutTime), @OutTime) + DATEADD(ss, 1, 0)

     ELSE

      SELECT @Low = DATEADD(dd, DATEDIFF(dd, 0, @Logout), 0) + DATEADD(dd, -DATEDIFF(dd, 0, @InTime), @InTime),

       @High = DATEADD(dd, DATEDIFF(dd, 0, @Logout), 0) + DATEADD(dd, -DATEDIFF(dd, 0, @OutTime), @OutTime) + DATEADD(ss, 1, 0)

     IF @login <= @Low AND @Low <= @High AND @High <= @Logout

      SELECT @Seconds = DATEDIFF(ss, @Low, @High)

     IF @Low <= @login AND @login <= @Logout AND @Logout <= @High

      SELECT @Seconds = DATEDIFF(ss, @login, @Logout)

     IF @login <= @Low AND @Low <= @Logout AND @Logout <= @High

      SELECT @Seconds = DATEDIFF(ss, @Low, @Logout)

     IF @Low <= @login AND @login <= @High AND @High <= @Logout

      SELECT @Seconds = DATEDIFF(ss, @login, @High)

     IF @FromCheck = 0 AND DATEDIFF(dd, @LoginDate, @LogoutDate) = 0

      SELECT @Seconds = 0

     RETURN ISNULL(@Seconds, 0)

    END

    And here is the code

    --Do the magic!

    SELECT  z.theDate,

      z.Vacation,

      z.Sonntag,

      SUM(case when z.[00-06] > 0 then 1 else 0 end) 'Anzahl 00-06',

      SUM(case when z.[06-22] > 0 then 1 else 0 end) 'Anzahl 06-22',

      SUM(case when z.[22-24] > 0 then 1 else 0 end) 'Anzahl 22-24',

      SUM(z.[00-06]) 'Zeit 00-06',

      SUM(z.[06-22]) 'Zeit 06-22',

      SUM(z.[22-24]) 'Zeit 22-24'

    FROM  (

       SELECT  ll.LoginDate theDate,

         ll.loginzeit,

         CASE WHEN fh.feiertage IS NULL THEN 0 ELSE 1 END Vacation,

         CASE WHEN DATEPART(dw, ll.LoginDate) = 1 THEN 1 ELSE 0 END Sonntag,

         dbo.fnCalculateSeconds(ll.LoginDate, ll.LoginZeit, ll.LogoutDate, ll.LogoutZeit, '00:00:00', '05:59:59', 1) '00-06',

         dbo.fnCalculateSeconds(ll.LoginDate, ll.LoginZeit, ll.LogoutDate, ll.LogoutZeit, '06:00:00', '21:59:59', 1) '06-22',

         dbo.fnCalculateSeconds(ll.LoginDate, ll.LoginZeit, ll.LogoutDate, ll.LogoutZeit, '22:00:00', '23:59:59', 1) '22-24'

       FROM  @LoginLogout ll

       LEFT JOIN @fhessen fh ON fh.feiertage = ll.LoginDate

       UNION ALL

       SELECT  ll.LogoutDate theDate,

         ll.logoutzeit,

         CASE WHEN fh.feiertage IS NULL THEN 0 ELSE 1 END,

         CASE WHEN DATEPART(dw, ll.LogoutDate) = 1 THEN 1 ELSE 0 END,

         dbo.fnCalculateSeconds(ll.LoginDate, ll.LoginZeit, ll.LogoutDate, ll.LogoutZeit, '00:00:00', '05:59:59', 0),

         dbo.fnCalculateSeconds(ll.LoginDate, ll.LoginZeit, ll.LogoutDate, ll.LogoutZeit, '06:00:00', '21:59:59', 0),

         dbo.fnCalculateSeconds(ll.LoginDate, ll.LoginZeit, ll.LogoutDate, ll.LogoutZeit, '22:00:00', '23:59:59', 0)

       FROM       @LoginLogout ll

       LEFT JOIN @fhessen fh ON fh.feiertage = ll.LogoutDate

      ) z

    GROUP BY z.theDate,

      z.Vacation,

      z.Sonntag

    And here is the output

    theDate     Vacation  Sonntag  Anzahl 00-06  Anzahl 06-22  Anzahl 22-24  Zeit 00-06   Zeit 06-22   Zeit 22-24 

    ----------  --------  -------  ------------  ------------  ------------  -----------  -----------  -----------

    2006-05-24         0        0             1            23             8          553       322003        35753

    2006-05-25         1        0             5             0             0        19007            0            0

    N 56°04'39.16"
    E 12°55'05.25"

  • June 21, 2006 at 5:49 am

    #644987

    Hallo sorry for answere so late but there are Problems with the Inernet from the IT Service. I take all from you and it RUNS very well.

    Thank you for spent your time for me.

    Ok i am Newbie but it looks SUPER.

    Thanks again

    Thomas

  • June 21, 2006 at 6:22 am

    #644995

    Thanks! I hope the code works out for you.

    If you have the time and courage, I would suggest another environment for you, since your environment has a number of flaws. For example logging in 2006-05-24 23:50:00 and logging out 2006-05-26 00:15:00 can't be done. The algorithm will not work correctly. But my proposed environment will take just about anything.

    First of all, I would have a lookup table with all information about dates like this

    DECLARE @times TABLE (FromTime DATETIME, ToTime DATETIME, Vacation BIT, Salary SMALLMONEY, Timeslot VARCHAR(8))

    INSERT @times

    SELECT '2006-05-24 00:00:00', '2006-05-24 05:59:59', 0, 1.25, '00-06' UNION ALL

    SELECT '2006-05-24 06:00:00', '2006-05-24 21:59:59', 0, 1.00, '06-22' UNION ALL

    SELECT '2006-05-24 22:00:00', '2006-05-24 23:59:59', 0, 1.25, '22-24' UNION ALL

    SELECT '2006-05-25 00:00:00', '2006-05-25 23:59:59', 1, 2.00, '00-24' UNION ALL

    SELECT '2006-05-26 00:00:00', '2006-05-26 05:59:59', 0, 1.50, '00-06'

    Then I would have a table containing all the logins and logout for the workers

    DECLARE @Logs TABLE (WorkerID INT, Login DATETIME, Logout DATETIME)

    INSERT @Logs

    SELECT 1000, '2006-05-24 04:00:00', '2006-05-25 01:30:00' UNION ALL

    SELECT 1000, '2006-05-26 08:00:00', '2006-05-26 17:00:00' UNION ALL

    SELECT 1001, '2006-05-24 13:30:00', '2006-05-24 20:00:00' UNION ALL

    SELECT 1001, '2006-05-25 10:15:00', '2006-05-25 16:30:00'

    For your current environment, it took a function and a lot of programming to accomplish the task you wanted. But look at my proposed environment! There is no need for a function, just a simple select

    SELECT     l.WorkerID,

               t.Vacation IsVacation,

               DATEADD(dd, DATEDIFF(dd, 0, t.FromTime), 0) WorkDate,

               t.Timeslot,

               CASE

                   WHEN t.FromTime <= l.Login AND l.Login <= t.ToTime AND t.ToTime <= l.Logout THEN 1 + DATEDIFF(ss, l.Login, t.ToTime)

                   WHEN t.FromTime <= l.Login AND l.Login <= l.Logout AND l.Logout <= t.ToTime THEN DATEDIFF(ss, l.Login, l.Logout)

                   WHEN l.Login <= t.FromTime AND t.FromTime <= l.Logout AND l.Logout <= t.ToTime THEN DATEDIFF(ss, t.FromTime, l.Logout)

                   WHEN l.Login <= t.FromTime AND t.FromTime <= t.ToTime AND t.ToTime <= l.Logout THEN 1 + DATEDIFF(ss, t.FromTime, t.ToTime)

               END WorkSeconds,

               t.Salary WorkSalary

    FROM       @Logs l

    CROSS JOIN @Times t

    WHERE      (t.FromTime <= l.Login AND l.Login <= t.ToTime AND t.ToTime <= l.Logout)

               OR (t.FromTime <= l.Login AND l.Login <= l.Logout AND l.Logout <= t.ToTime)

               OR (l.Login <= t.FromTime AND t.FromTime <= l.Logout AND l.Logout <= t.ToTime)

               OR (l.Login <= t.FromTime AND t.FromTime <= t.ToTime AND t.ToTime <= l.Logout)

    ORDER BY   l.WorkerID,

               l.Login,

               t.FromTime

    And there is another benefit! You can also retreive the salary information related to the timeslot. Output from my proposed environment is

    WorkerID  IsVacation  WorkDate    Timeslot  WorkSeconds  WorkSalary  Payment

    --------  ----------  ----------  --------  -----------  ----------  -------

        1000           0  2006-05-24  00-06            7200        1.25    50.00

        1000           0  2006-05-24  06-22           57600        1.00   320.00

        1000           0  2006-05-24  22-24            7200        1.25    50.00

        1000           1  2006-05-25  00-24            5400        2.00    60.00

        1001           0  2006-05-24  06-22           23400        1.00   146.25

        1001           1  2006-05-25  00-24           22500        2.00   281.25

    Keep in mind that this only select can be put into a stored procedure where the call parameters can be fromdate and to date such as

        EXEC uspGetTimeReport '2006-05-01', '2006-05-31'

    or any other date. And you can really easy produce a report for salary payment! Join the table to the Workers table where the personal hourly payment is stored and multiply with WorkSalary value and multiply with WorkSeconds / 3600.0, and you get exactly what to pay each worker. Of course you then also have the opportunity to do a round off for the times. If wanted, you can round off to nearest 10 minutes up (600 seconds). If worker did 5 minutes and 30 seconds in one timeslot, round up to 10 minutes even, or 30 minutes, or not at all! Se table above. No round off and Worker 1000 has 20 euro an hour and Worker 1001 has 22.50 euro an hour.

    Good luck and have a nice day!

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 10 posts - 16 through 24 (of 24 total)

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