Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    1

    Unanswered: how to get employees attendance as desired output from the input

    Hi I am uploading input sample data and desired output data can any one help to get the desire output
    Attached Thumbnails Attached Thumbnails input.png   output2.png  

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Care to share how you go from the Input to the Output? Or were you planning to leave that as an exercise to us?

    Can you also give us the CREATE TABLE script and INSERT scripts to create and populate that table? Perhaps even other related tables (just the columns that are needed to solve this problem).

    We don't mind helping, but you got to show you want to invest some work in it too.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    This was just covered the other day, in "Off day " thread

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This was just covered the other day, in "Off day " thread
    I thought this was essentially a different issue than in "Off day" thread.

    Quote Originally Posted by Wim View Post
    Care to share how you go from the Input to the Output? Or were you planning to leave that as an exercise to us?

    Can you also give us the CREATE TABLE script and INSERT scripts to create and populate that table? Perhaps even other related tables (just the columns that are needed to solve this problem).

    We don't mind helping, but you got to show you want to invest some work in it too.
    I agree with the opinion.
    Please give us the CREATE TABLE script and INSERT scripts to create and populate that table.


    By the way,

    I guessed a way to solve the issue might be ...
    (1) number rows in the table(e.g. call it r_num) by using ROW_NUMBER() funcion in a common-table-expression.
    (2) (Self) FULL OUTER JOIN the common-table-expression like ...
    Code:
     FROM  cte t1
     FULL  OUTER JOIN
           cte t2
      ON   t2.r_num = t1.r_num + 1
       AND t2.AltCategoryID = 3
       AND t1.AltCategoryID = 1

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a more concrete example.

    Not tested.
    Code:
    WITH
     numbered_table AS (
    SELECT s.*
         , ROW_NUMBER()
              OVER( ORDER BY AttDateTime ) AS r_num
     FROM  sample_data
    )
    SELECT ROW_NUMBER()
              OVER( ORDER BY COALESCE(i.AttDateTime , o.AttDateTime) ) AS rowNo
         , COALESCE(i.MachineID   , o.MachineID  ) AS MachineID
         , CAST(COALESCE(i.AttDateTime , o.AttDateTime) AS DATE) AS tDate
         , COALESCE(i.EmployeeID  , o.EmployeeID ) AS EmployeeID
         , i.AttCategoryID AS InType
         , i.AttRecordID   AS InRecordID
         , i.AttDateTime   AS InDateTime
         , o.AttCategoryID AS OutType
         , o.AttRecordID   AS OutRecordID
         , o.AttDateTime   AS OutDateTime
     FROM  (SELECT *
             FROM  numbered_table
             WHERE AttCategoryID = 1
           ) AS i
     FULL  OUTER JOIN
           (SELECT *
             FROM  numbered_table
             WHERE AttCategoryID = 3
           ) AS o
      ON   o.MachineID     = i.MachineID
       AND o.EmployeeID    = i.EmployeeID
       AND o.r_num         = i.r_num + 1
    ;
    Last edited by tonkuma; 07-09-13 at 11:40. Reason: Replace FULL OUTER JOINed tables with subqueries.

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
  •