Loading ...
Sorry, an error occurred while loading the content.

How to Count Consectively and regular Absent

Expand Messages
  • rrrr
    Count minimum 10 days or greater then Consecutively date wise Absent Employee not in Saturday and Sunday count and also set Parameter Start_date and End_date.
    Message 1 of 3 , Aug 4, 2013
    • 0 Attachment
      Count minimum 10 days or greater then Consecutively date wise Absent Employee not in Saturday and Sunday count and also set Parameter Start_date and End_date. The Column Name of Absent is 'XX' and Present is 'PP'.

      WH ='XX'

      What is Query Please help me..

      Note: Write two Queries.

      1) Write to Count Individually Employee Wise Absent Record
      2) Write to Count All Employee Wise Absent Record Query

      Example Data

      emp_id Attend_date WH

      X235 01/01/2013 PP
      X235 02/01/2013 XX
      X235 03/01/2013 XX
      X235 04/01/2013 XX
      X235 05/01/2013 PP
      X235 06/01/2013 PP
      X235 07/01/2013 XX
      X235 08/01/2013 PP
      X235 09/01/2013 XX
      X235 10/01/2013 XX
      X235 11/01/2013 XX
      X235 12/01/2013 XX
      X235 13/01/2013 XX
      X235 14/01/2013 XX
      X235 15/01/2013 XX
      X235 16/01/2013 XX
      X235 17/01/2013 XX
      X235 18/01/2013 XX
      X235 19/01/2013 PP

      Please Solve this task as soon

      Thanks..

      Fahad
    • Abdul Karim
      SELECT COUNT(*) ABS_EMP FROM ABC WHERE WH=XX AND ATTEND_DATE NOT IN ( SUN ,SAT ) AND ATTEND_DATE BETWEEN &FRM_dATE AND &TO_DATE ... -- Regards, Abdul Karim
      Message 2 of 3 , Aug 4, 2013
      • 0 Attachment
        SELECT COUNT(*) ABS_EMP FROM ABC
        WHERE WH=XX 
        AND ATTEND_DATE NOT IN ('SUN',SAT')
        AND ATTEND_DATE BETWEEN &FRM_dATE AND &TO_DATE


        On Mon, Aug 5, 2013 at 8:20 AM, rrrr <fahad_aijaz81@...> wrote:
         

        Count minimum 10 days or greater then Consecutively date wise Absent Employee not in Saturday and Sunday count and also set Parameter Start_date and End_date. The Column Name of Absent is 'XX' and Present is 'PP'.

        WH ='XX'

        What is Query Please help me..

        Note: Write two Queries.

        1) Write to Count Individually Employee Wise Absent Record
        2) Write to Count All Employee Wise Absent Record Query

        Example Data

        emp_id Attend_date WH

        X235 01/01/2013 PP
        X235 02/01/2013 XX
        X235 03/01/2013 XX
        X235 04/01/2013 XX
        X235 05/01/2013 PP
        X235 06/01/2013 PP
        X235 07/01/2013 XX
        X235 08/01/2013 PP
        X235 09/01/2013 XX
        X235 10/01/2013 XX
        X235 11/01/2013 XX
        X235 12/01/2013 XX
        X235 13/01/2013 XX
        X235 14/01/2013 XX
        X235 15/01/2013 XX
        X235 16/01/2013 XX
        X235 17/01/2013 XX
        X235 18/01/2013 XX
        X235 19/01/2013 PP

        Please Solve this task as soon

        Thanks..

        Fahad




        --
        Regards,

        Abdul Karim
        Oracle Developer / Implementor
        Cell #  0321-2977318
      • Hanif Babar
        ASSUMING TABLE WITH FOLLOWING STRUCTUR  SQL DESC ATTENDANCE Name                                 Type  
        Message 3 of 3 , Aug 15, 2013
        • 0 Attachment
          ASSUMING TABLE WITH FOLLOWING STRUCTUR
           
          SQL> DESC ATTENDANCE
          Name                                 Type
           
          EMPID                             VARCHAR2(10)
          ADATE                              DATE
          ATTEND_STATUS           VARCHAR2(2)
           
           
          SELECT
          empid, adate, attend_status,last_ab ,first_ab
          FROM (SELECT t.empid, t.adate, t.attend_status,
          (SELECT MAX(adate) + 1
          FROM attendance b
          WHERE b.empid = t.empid
          AND b.adate < t.adate
          AND b.attend_status != 'XX') first_ab,
          (
          SELECT MIN(adate) - 1
          FROM attendance b
          WHERE b.empid = t.empid
          AND b.adate > t.adate
          AND b.attend_status != 'XX') last_ab
          FROM attendance t
          WHERE t.attend_status = 'XX')
          WHERE ((last_ab - first_ab) + 1 >= 10 OR last_ab IS NULL)
          ORDER BY adate
           
          Regards Hanif Babar
          0321-3810021

          From: Abdul Karim <akarim05@...>
          To: PkOug@yahoogroups.com
          Sent: Monday, August 5, 2013 8:36 AM
          Subject: Re: [PkOug] How to Count Consectively and regular Absent
           
          SELECT COUNT(*) ABS_EMP FROM ABC
          WHERE WH=XX 
          AND ATTEND_DATE NOT IN ('SUN',SAT')
          AND ATTEND_DATE BETWEEN &FRM_dATE AND &TO_DATE
          On Mon, Aug 5, 2013 at 8:20 AM, rrrr <fahad_aijaz81@...> wrote:
           
          Count minimum 10 days or greater then Consecutively date wise Absent Employee not in Saturday and Sunday count and also set Parameter Start_date and End_date. The Column Name of Absent is 'XX' and Present is 'PP'.

          WH ='XX'

          What is Query Please help me..

          Note: Write two Queries.

          1) Write to Count Individually Employee Wise Absent Record
          2) Write to Count All Employee Wise Absent Record Query

          Example Data

          emp_id Attend_date WH

          X235 01/01/2013 PP
          X235 02/01/2013 XX
          X235 03/01/2013 XX
          X235 04/01/2013 XX
          X235 05/01/2013 PP
          X235 06/01/2013 PP
          X235 07/01/2013 XX
          X235 08/01/2013 PP
          X235 09/01/2013 XX
          X235 10/01/2013 XX
          X235 11/01/2013 XX
          X235 12/01/2013 XX
          X235 13/01/2013 XX
          X235 14/01/2013 XX
          X235 15/01/2013 XX
          X235 16/01/2013 XX
          X235 17/01/2013 XX
          X235 18/01/2013 XX
          X235 19/01/2013 PP

          Please Solve this task as soon

          Thanks..

          Fahad


          -- Regards, Abdul Karim Oracle Developer / Implementor Cell #  0321-2977318
        Your message has been successfully submitted and would be delivered to recipients shortly.