Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: 4 queries into 1 query

    I have 4 queries that run fine, giving me the number of different clocking errors for employees. I would like to run just 1 query if possible.

    Query 1:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.CLOCKSEQ) AS LateEarly
    from clockwise.Employee E, clockwise.clocking A,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d,ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.WorkDate >= to_date('05-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
    AND E.Clk_Roster = '!*'
    AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
    AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
    AND ((A.CLOCKSEQ = 1 AND A.CLOCKIN > 600) OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT < 960))
    GROUP BY E.EmpNo, E.FirstName, E.Surname
    ORDER BY E.Surname ASC

    Gives me: 11111, John, Smith, 3


    Query 2:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.CLOCKSEQ) AS Lunch
    from clockwise.Employee E, clockwise.clocking A,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d,ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.WorkDate >= to_date('05-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
    AND E.Clk_Roster = '!*'
    AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
    AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
    AND ((A.CLOCKSEQ = 1 AND A.CLOCKOUT < 750) OR (A.CLOCKSEQ = 2 AND A.CLOCKIN > 870))
    GROUP BY E.EmpNo, E.FirstName, E.Surname
    ORDER BY E.Surname ASC

    Gives me: 11111, John, Smith, 1


    Query 3:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.Mins) AS HoursDown
    FROM clockwise.Employee E, clockwise.Correcti A,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.WorkDate >= to_date('14-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
    AND E.Clk_Roster = '!*'
    AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
    AND (f.POST_EFF_END_DT >= to_date('14-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
    AND A.CorrCode = '+[' AND A.Mins < -600
    GROUP BY E.EmpNo, E.FirstName, E.Surname
    ORDER BY E.Surname ASC

    Gives me: 11111, John, Smith, 1


    Query 4:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, COUNT(A.CLOCKSEQ) AS ForgotToClock
    FROM clockwise.Employee E, clockwise.clocking A,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.WorkDate >= to_date('05-01-2005','dd-mm-yyyy') AND A.WorkDate <= to_date('14-01-2005','dd-mm-yyyy')
    AND E.Clk_Roster = '!*'
    AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '5005'
    AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null)
    AND ((A.CLOCKSEQ = 1
    AND A.EmpID NOT IN (SELECT DISTINCT EmpID
    FROM clockwise.clocking H
    WHERE H.CLOCKSEQ = 2
    AND H.WorkDate = A.WorkDate))
    OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT = 0)
    OR (A.CLOCKSEQ = 1 AND A.CLOCKIN >= 720))
    GROUP BY E.EmpNo, E.FirstName, E.Surname
    ORDER BY E.Surname ASC

    Gives me: 11111, John, Smith, 5

    I would like to get 1 row with 11111, John, Smith, 3, 1, 1, 5.


    Query 3 select information from the Correcti table, the other 3 queries select information from the Clocking table otherwise the 4 other tables in the queries are the same. The condition that gives the result for each query comes after the AND (f.POST_EFF_END_DT >= to_date('05-01-2005','dd-mm-yyyy') or f.POST_EFF_END_DT is null) line.

    Query 1:
    AND ((A.CLOCKSEQ = 1 AND A.CLOCKIN > 600) OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT < 960))

    Query 2:
    AND ((A.CLOCKSEQ = 1 AND A.CLOCKOUT < 750) OR (A.CLOCKSEQ = 2 AND A.CLOCKIN > 870))

    Query 3:
    AND A.CorrCode = '+[' AND A.Mins < -600

    Query 4:
    AND ((A.CLOCKSEQ = 1
    AND A.EmpID NOT IN (SELECT DISTINCT EmpID
    FROM clockwise.clocking H
    WHERE H.CLOCKSEQ = 2
    AND H.WorkDate = A.WorkDate))
    OR (A.CLOCKSEQ = 2 AND A.CLOCKOUT = 0)
    OR (A.CLOCKSEQ = 1 AND A.CLOCKIN >= 720))


    Thanks for any help with this.
    Mark.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you might want to look into the UNION operator
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    Hi r937,

    I've tried union, but I can't seem to get the output I need.

    Using union all I can get is 4 records:
    11111, John, Smith, 3
    11111, John, Smith, 1
    11111, John, Smith, 1
    11111, John, Smith, 5

    instead of 11111, John, Smith, 3, 1, 1, 5.

    If only 2 infringements occurr, I'll only get 2 rows and I don't know what these infrigements are for, LateEarly, Lunch, HoursDown or ForgotToClock.

    I've also tried just using a union, but adding extra redundant fields to the select statements.
    Using the first 2 queries I added an extra field after COUNT(A.CLOCKSEQ) AS LateEarly in the first select and before COUNT(A.CLOCKSEQ) AS Lunch in the second query.
    But I get the following:
    11111, John, Smith, 3, 3
    11111, John, Smith, 1, 1
    22222, Joan, Smith, 1, 1

    What I need to get is:
    11111, John, Smith, 3, 1
    22222, Joan, Smith, , 1

    any ideas?

    Thanks,
    Mark.
    Last edited by markos; 01-28-05 at 10:47.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Try adding a literal column to the union to indicate LateEarly, Lunch, HoursDown or ForgotToClock and then put the union into a subquery and put a CASE statement on the literal column to put the rows into columns.

Posting Permissions

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