Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Unanswered: Get Time defaulters list

    Following are the table details
    1.
    name: time_tbl
    columns:
    time_id
    employee_id
    time_date (date)
    time_start(time)
    time_end(time)
    time_details(text)

    2.
    name: employee_tbl
    columns:
    employee_id
    employee_name(varchar)
    employee_join_date(datetime)

    Everyday employees enter their time entries and their task details. I want to have list of employees that did not fill their time entries between a given date range.

    If the date range is more than one day and the employee has not entered time details for more than one day in between the range, then the employee name should appear more than once with respect to date.

    Required output

    date | employee_id | employee_name

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Handling of Date/Time datatype quite different by products.

    So, it would be better to ask on product specific forum.

    What product are you using?

    Here are some links.
    Quote Originally Posted by tonkuma View Post
    Last edited by tonkuma; 02-15-12 at 02:43. Reason: Add some links to product specific forums

  3. #3
    Join Date
    Feb 2012
    Posts
    4
    Ms sql 2005

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... between a given date range.
    How would you give the "given date range"?

    (1) If you gave it by the list of dates, like
    CREATE TABLE date_range
    ( given_date DATE NOT NULL UNIQUE );

    Try this...
    Example 1:
    Code:
    SELECT r.given_date
         , e.employee_id
         , e.employee_name
     FROM  date_range   r
     CROSS JOIN
           employee_tbl e
     LEFT  OUTER JOIN
           time_tbl     t
      ON   t.time_date    = r.given_date
       AND t.employee_id  = e.employee_id
       AND t.time_start   IS NOT NULL
       AND t.time_end     IS NOT NULL
       AND t.time_details IS NOT NULL
     WHERE t.employee_id  IS NULL
     ORDER BY
           r.given_date
         , e.employee_id
    ;

    (2) If you gave it by a pair of(begin_date , end_date),
    make list of given_date like in (1), then use Example 1.

    It should be different by RDBMS products to make list of given_date from a pair of(begin_date , end_date).

    Sorry, I don't know so much about MS SQL Server.
    Here is an example on DB2.

    Example 2:
    Note: Although a column "k" seems to be redundant(and not necessary),
    it was used to prevent a warning message, like...
    SQL0347W The recursive common table expression "DB2ADMIN.DATE_RANGE" may
    contain an infinite loop. SQLSTATE=01605
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      duration(begin_date , end_date) AS (
    VALUES
      ( DATE('2012-01-29') , DATE('2012-02-04') )
    )
    , date_range(given_date , end_date , k) AS (
    SELECT begin_date
         , end_date
         , 1
     FROM  duration
    UNION ALL
    SELECT given_date + 1 DAY
         , end_date
         , k + 1
     FROM  date_range
     WHERE k < 10000
       AND given_date < end_date
    )
    SELECT given_date FROM date_range
    ;
    ------------------------------------------------------------------------------
    
    GIVEN_DATE
    ----------
    2012-01-29
    2012-01-30
    2012-01-31
    2012-02-01
    2012-02-02
    2012-02-03
    2012-02-04
    
      7 record(s) selected.
    Last edited by tonkuma; 02-15-12 at 17:43. Reason: Add predicates to ask NOT NULL in Ex 1. Add qualifier "r." for given_date in Ex 1. Add Note for Ex 2.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example for case (1).

    Example 3:
    Code:
    SELECT r.given_date
         , e.employee_id
         , e.employee_name
     FROM  date_range   r
     CROSS JOIN
           employee_tbl e
     WHERE NOT EXISTS (
           SELECT 0
            FROM  time_tbl t
            WHERE t.time_date    = r.given_date
              AND t.employee_id  = e.employee_id
              AND t.time_start   IS NOT NULL
              AND t.time_end     IS NOT NULL
              AND t.time_details IS NOT NULL
           )
     ORDER BY
           r.given_date
         , e.employee_id
    ;
    Last edited by tonkuma; 02-15-12 at 17:45. Reason: Add predicates to ask IS NOT NULL.

  6. #6
    Join Date
    Feb 2012
    Posts
    4
    I will try and let you know the result, thanks.

Tags for this Thread

Posting Permissions

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