Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2004
    Posts
    182

    Unanswered: Help with SP Code

    I am running this SP in Access 2003 .adp

    ALTER FUNCTION SQLACCESS.fnEmpHrs2
    ()
    RETURNS TABLE
    AS
    RETURN (SELECT Emp_id, (DATEDIFF(n, MinStart, MaxEnd)) AS Running ,employee, minstart, maxend
    FROM (SELECT distinct A.Emp_id, employee, CASE WHEN
    (SELECT MIN(start)
    FROM SQLACCESS.tblctjor AS B
    WHERE B.Emp_id = A.Emp_id AND A.start BETWEEN B.start AND B.stop) < [start] THEN
    (SELECT MIN(start)
    FROM SQLACCESS.tblctjor AS B
    WHERE B.Emp_id = A.Emp_id AND A.start BETWEEN B.start AND B.stop) ELSE [start] END AS MinStart, CASE WHEN
    (SELECT MAX(stop)
    FROM SQLACCESS.tblctjor AS B
    WHERE B.Emp_id = A.Emp_id AND A.stop BETWEEN B.start AND B.stop) > [stop] THEN
    (SELECT MAX(stop)
    FROM SQLACCESS.tblctjor AS B
    WHERE B.Emp_id = A.Emp_id AND A.stop BETWEEN B.start AND B.stop) ELSE [stop] END AS MaxEnd
    FROM SQLACCESS.tblctjor AS A) DERIVEDTBL
    where emp_id = 1142
    GROUP BY Emp_id, employee, minstart, maxend)

    The aim is to get the earliest start time and latest finish time of each emp each day and exclude skipped time (ie show actual working time)
    Here is a sample of the result that I am getting which I do not want:

    Running Emp_id employee minstart maxend
    530 1142 Alan 10/11/2005 07:56:45 10/11/2005 16:46:38
    529 1142 Alan 10/11/2005 07:56:45 10/11/2005 16:45:51

    I just want this to show the MinStart and MaxEnd for each emp for each day - 1 record.
    530 1142 Alan 10/11/2005 07:56:45 10/11/2005 16:46:38

    Any ideas why this is happening?

    Urgent!!

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Doesn't

    SELECT Emp_id, (DATEDIFF(n, min(start), max(stop)) AS Running ,employee, min(start), max(stop)
    FROM SQLACCESS.tblctjor
    where emp_id = 1142
    GROUP BY Emp_id, employee

    do what you want?

  3. #3
    Join Date
    Apr 2004
    Posts
    182
    No
    Firstly the syntax is not allowed in Access 2003.adp (the Datediff bit 'cos of the brackets in Max(stop) etc)
    Secondly when you do it withour datdiff it just returns the earliest start time and latest finish time for that employee in the entire table.

    The other way returns each day individually (or should)
    Last edited by livvie; 11-16-05 at 10:50.

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    There was an extra bracket in my previous try.
    This works for me:

    SELECT id, int(start) as int_start, min(start) as minstart, max(stop) as maxstop, datediff('n', min(start), max(stop))
    FROM MyTable
    group by id, int(start);

    It's just a query in Access 2000, but if a query works there, my guess is it's possible to make it works in Acc2003.adp.

  5. #5
    Join Date
    Apr 2004
    Posts
    182
    Ivon
    That works fine when each emp only has one record per day but that is not the case. Each emp has a number of records and some of them over lap so i just want the earliest start time and the latest finish time each day.
    Sample data:

    emp_id start stop
    142 10/11/2005 16:01:37 10/11/2005 16:49:38
    142 10/11/2005 08:35:21 10/11/2005 12:59:35
    142 10/11/2005 07:56:45 10/11/2005 16:45:51

    So my result needs to be
    emp_id Minstart MaxStop
    142 10/11/2005 07:56:45 10/11/2005 16:49:38

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So add a grouping level for date. You CAN pull only the date for grouping purposes...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Apr 2004
    Posts
    182
    Can you give me an example Teddy.


    I have created a new sp that uses the first one and takes the max and min for each day from it. Looks ok except where i have guys starting a job at 23:00 on one day and finishing after midnight which is the next day. I'm still trying to figure that one out.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Only group by date, not date AND time. That's where you're running into issues. I don't have an instance of sql server in front of me and I don't remember the exact syntax, but you can use the FORMAT() function to produce a "short date" that you could then use for grouping. this would restrict you to one aggregate value per day.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Apr 2004
    Posts
    182
    I'm using this:

    CONVERT(varchar(10), minstart, 102) which gives me just the date

    Using Access 2003 .adp so syntax is different.

  10. #10
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Teddy
    Only group by date, not date AND time.
    Yeah, that's what I did, group by id, int(start). It gave me 1 record per id, per day even though I had more records in my table.

    I don't know why that didn't work for Lizzie.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Clever... i wouldn't have thought of casting a date to an int. Neat idea.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Nov 2002
    Posts
    272
    Neat idea, or just plain luck.
    I couldn't remember how to round down a date-time to a date. In Oracle I'd use Trunc(), but I didn't think Access would understand. So I tried Int(), not really expecting it to work. But it did :-)

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    More date time conversion functions you could ever need from BoL FYI

    CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) style comes from the table below.

    Without century (yy)With century (yyyy)
    Standard
    Input/Output**-0 or 100 (*) Defaultmon dd yyyy hh:miAM (or PM)1101USAmm/dd/yy2102ANSIyy.mm.dd3103British/Frenchdd/mm/yy4104Germandd.mm.yy5105Italiandd-mm-yy6106-dd mon yy7107-Mon dd, yy8108-hh:mm:ss-9 or 109 (*) Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)10110USAmm-dd-yy11111JAPANyy/mm/dd12112ISOyymmdd-13 or 113 (*) Europe default + millisecondsdd mon yyyy hh:mm:ss:mmm(24h)14114-hh:mi:ss:mmm(24h)-20 or 120 (*) ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)-21 or 121 (*) ODBC canonical (with milliseconds)yyyy-mm-dd hh:mi:ss.mmm(24h)-126(***)ISO8601yyyy-mm-dd Thh:mm:ss:mmm(no spaces)-130*Kuwaitidd mon yyyy hh:mi:ss:mmmAM-131*Kuwaitidd/mm/yy hh:mi:ss:mmmAM
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aw - it mucked up the table formatting
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2002
    Posts
    272
    Awwww...

    I could have found that if I'd looked for it, but I had a working solution

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •