12 Replies Latest reply: Apr 10, 2013 4:19 AM by 1002148 RSS

    Query help - Fetch employees working on two consecutive weekends(SAT/SUN)

    1002148
      Hello Gurus,

      I have the following requirement and i need your help on this.

      We have a table with all employees effort logged in for the days they have worked. a sample data is given below

      TD_date     Emp_id     Effort     Archive_month
      2-Mar-13     123     8     Mar-13
      9-Mar-13     123     8     Mar-13
      2-Mar-13     213     4     Mar-13
      3-Mar-13     213     4     Mar-13
      24-Mar-13     213     8     Mar-13
      9-Mar-13     312     4     Mar-13
      10-Mar-13     312     4     Mar-13
      16-Mar-13     312     8     Mar-13

      In the above sample data, we have employee 123 who worked on consecutive SATURDAY and 312 who worked on consecutive weekends (9th, 10th and 16th March) but the employee 213 worked on 1st weekend and 4th weekend of the month(Archive_month). So the output should return the employees who worked on two consecutive weekends as below.

      Emp_id
      123
      312

      I have written a query to fetch all employees who worked only on a SAT or SUN which is given below, but i am not able to return the employees who worked on consecutive weekends and i need your help.

      select emp_id, count(*)
      from timesheet_archive
      where archive_month = '03/2013'
      and trim(to_char(td_date,'DAY')) in ('SATURDAY','SUNDAY')
      group by emp_id having count(*) > 1
      order by td_date desc

      Please help me with an approach in SQL or PL/SQL to generate a report of employees who worked on two consecutive weekends.

      Thanks,

      Edited by: 999145 on Apr 9, 2013 11:08 PM

      Edited by: 999145 on Apr 9, 2013 11:10 PM
        • 1. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
          jeneesh
          Are you passing a month and trying to find out the employee count in that month?

          Can you provide sample data with CREATE TABLE and INSERT statements?
          • 2. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
            Manik
            Check this ... (Also post few more test cases so that we can test....)
            with t as (select '2-Mar-13' toDate,123 empid,8 effort, 'Mar-13' archive_month FROM DUAL union all
            select '9-Mar-13' ,123 ,8, 'Mar-13'FROM DUAL union all
            select '2-Mar-13' ,213 ,4, 'Mar-13'FROM DUAL union all
            select '3-Mar-13' ,213 ,4, 'Mar-13'FROM DUAL union all
            select '24-Mar-13' ,213, 8,'Mar-13'FROM DUAL union all
            select '9-Mar-13' ,312 ,4, 'Mar-13'FROM DUAL union all
            select '10-Mar-13' ,312, 4, 'Mar-13'FROM DUAL union all
            select '16-Mar-13' ,312, 8, 'Mar-13' FROM DUAL)
              SELECT empid
                FROM (SELECT TO_DATE (todate, 'DD-Mon-YY'),
                             empid,
                             effort,
                             archive_month,
                             TO_CHAR (TO_DATE (todate, 'DD-Mon-YY'), 'ww')
                             - NVL (
                                  LAG (
                                     TO_CHAR (TO_DATE (todate, 'DD-Mon-YY'), 'ww'))
                                  OVER (PARTITION BY empid, archive_month
                                        ORDER BY TO_DATE (todate, 'DD-Mon-YY')),
                                  0)
                                cal
                        FROM t
                       WHERE TRIM (TO_CHAR (TO_DATE (todate, 'DD-Mon-YY'), 'Day')) in
                                ('Saturday','Sunday'))
               WHERE cal = 1
            GROUP BY empid;
            Output:
            EMPID
            ---
            123
            312
            Cheers,
            Manik.

            Edited by: asked for few more test cases...
            • 3. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
              Karthick_Arp
              How about this
              SQL>with t
                2  as
                3  (
                4  select to_date('02-Mar-13', 'dd-mon-rr') td_date, 123 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
                5    from dual union all
                6  select to_date('09-Mar-13', 'dd-mon-rr') td_date, 123 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
                7    from dual union all
                8  select to_date('02-Mar-13', 'dd-mon-rr') td_date, 213 emp_id, 4 effort, to_date('Mar-13', 'mon-rr') archive_month
                9    from dual union all
               10  select to_date('03-Mar-13', 'dd-mon-rr') td_date, 213 emp_id, 4 effort, to_date('Mar-13', 'mon-rr') archive_month
               11    from dual union all
               12  select to_date('24-Mar-13', 'dd-mon-rr') td_date, 213 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
               13    from dual union all
               14  select to_date('09-Mar-13', 'dd-mon-rr') td_date, 312 emp_id, 4 effort, to_date('Mar-13', 'mon-rr') archive_month
               15    from dual union all
               16  select to_date('10-Mar-13', 'dd-mon-rr') td_date, 312 emp_id, 4 effort, to_date('Mar-13', 'mon-rr') archive_month
               17    from dual union all
               18  select to_date('16-Mar-13', 'dd-mon-rr') td_date, 312 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
               19    from dual
               20  )
               21  select *
               22    from (
               23            select t.*
               24                 , to_char(td_date, 'fmDAY') work_day
               25                 , lead(td_date) over(partition by emp_id, to_char(td_date, 'fmDAY') order by td_date) next_week_day
               26              from t
               27         )
               28   where next_week_day = td_date + 7
               29  /
               
              TD_DATE       EMP_ID     EFFORT ARCHIVE_M WORK_DAY  NEXT_WEEK
              --------- ---------- ---------- --------- --------- ---------
              02-MAR-13        123          8 01-MAR-13 SATURDAY  09-MAR-13
              09-MAR-13        312          4 01-MAR-13 SATURDAY  16-MAR-13
              • 4. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                Manik
                Karthick,

                your query as I understand checks for 7 days span and accounts for all week days -- May be you need to put sat / sun condition else this test case would fail.
                select to_date('21-Mar-13', 'dd-mon-rr') td_date, 1000 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
                     from dual
                     union all
                     select to_date('28-Mar-13', 'dd-mon-rr') td_date, 1000 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
                     from dual;
                Please do correct me if I got you wrong.. Thanks!

                Cheers,
                Manik.
                • 5. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                  1002148
                  hi Jeneesh,

                  I shall pass the month to the query as '03/2013' which will be compared against Archive_month in the table to fetch the data of the month.

                  Please use the below create and insert statements

                  create table timesheet_archive as
                  (TD_date date,
                  Emp_id number(19,0),
                  Effort Float,
                  Archive_month varchar2);

                  insert into timesheet_archive values (to_date('2-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
                  insert into timesheet_archive values (to_date('9-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
                  insert into timesheet_archive values (to_date('2-MAR-2013','dd-MON-yyyy'), 213,4,'03/2013');
                  insert into timesheet_archive values (to_date('3-MAR-2013','dd-MON-yyyy'), 213,4,'03/2013');
                  insert into timesheet_archive values (to_date('24-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
                  insert into timesheet_archive values (to_date('9-MAR-2013','dd-MON-yyyy'), 321,4,'03/2013');
                  insert into timesheet_archive values (to_date('10-MAR-2013','dd-MON-yyyy'), 321,4,'03/2013');
                  insert into timesheet_archive values (to_date('16-MAR-2013','dd-MON-yyyy'), 321,8,'03/2013');
                  • 6. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                    1002148
                    hi Manik,

                    thanks for your response!!

                    Please find the below create and insert scripts for your testing.

                    I am also validating the output with my requirement. Thanks for your help!

                    create table timesheet_archive as
                    (TD_date date,
                    Emp_id number(19,0),
                    Effort Float,
                    Archive_month varchar2);

                    insert into timesheet_archive values (to_date('2-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
                    insert into timesheet_archive values (to_date('9-MAR-2013','dd-MON-yyyy'), 123,8,'03/2013');
                    insert into timesheet_archive values (to_date('2-MAR-2013','dd-MON-yyyy'), 213,4,'03/2013');
                    insert into timesheet_archive values (to_date('3-MAR-2013','dd-MON-yyyy'), 213,4,'03/2013');
                    insert into timesheet_archive values (to_date('24-MAR-2013','dd-MON-yyyy'), 213,8,'03/2013');
                    insert into timesheet_archive values (to_date('9-MAR-2013','dd-MON-yyyy'), 321,4,'03/2013');
                    insert into timesheet_archive values (to_date('10-MAR-2013','dd-MON-yyyy'), 321,4,'03/2013');
                    insert into timesheet_archive values (to_date('16-MAR-2013','dd-MON-yyyy'), 321,8,'03/2013');

                    insert into timesheet_archive values (to_date('2-MAR-2013','dd-MON-yyyy'), 124,8,'03/2013');
                    insert into timesheet_archive values (to_date('9-MAR-2013','dd-MON-yyyy'), 124,8,'03/2013');
                    insert into timesheet_archive values (to_date('16-MAR-2013','dd-MON-yyyy'), 124,4,'03/2013');
                    insert into timesheet_archive values (to_date('23-MAR-2013','dd-MON-yyyy'), 124,4,'03/2013');
                    insert into timesheet_archive values (to_date('16-MAR-2013','dd-MON-yyyy'), 241,8,'03/2013');
                    insert into timesheet_archive values (to_date('23-MAR-2013','dd-MON-yyyy'), 241,4,'03/2013');
                    insert into timesheet_archive values (to_date('24-MAR-2013','dd-MON-yyyy'), 241,4,'03/2013');
                    insert into timesheet_archive values (to_date('2-MAR-2013','dd-MON-yyyy'), 412,8,'03/2013');
                    insert into timesheet_archive values (to_date('3-MAR-2013','dd-MON-yyyy'), 412,8,'03/2013');
                    insert into timesheet_archive values (to_date('30-MAR-2013','dd-MON-yyyy'), 412,8,'03/2013');
                    insert into timesheet_archive values (to_date('31-MAR-2013','dd-MON-yyyy'), 412,8,'03/2013');
                    • 7. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                      jeneesh
                      Are the already given queries not matching with your requirement?
                      SQL> with ts_with_week as
                        2  (
                        3    select emp_id,to_number(to_char(TD_DATE,'iw')) wk
                        4    from timesheet_archive  t
                        5    where Archive_month = '03/2013'
                        6    and to_char(td_date,'fmday') in ('saturday','sunday')
                        7  ),
                        8  ts_with_wk_diff as
                        9  (
                       10    select emp_id,
                       11           wk-lag(wk) over(partition by emp_id order by wk) df
                       12    from ts_with_week
                       13  )
                       14  select distinct emp_id
                       15  from ts_with_wk_diff
                       16  where df = 1;
                      
                          EMP_ID
                      ----------
                             123
                             124
                             241
                             321
                      
                      Elapsed: 00:00:00.01
                      Is this you want? If not provide proper sample cases and expected output..
                      • 8. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                        Karthick_Arp
                        Manik wrote:
                        Karthick,

                        your query as I understand checks for 7 days span and accounts for all week days -- May be you need to put sat / sun condition else this test case would fail.
                        select to_date('21-Mar-13', 'dd-mon-rr') td_date, 1000 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
                        from dual
                        union all
                        select to_date('28-Mar-13', 'dd-mon-rr') td_date, 1000 emp_id, 8 effort, to_date('Mar-13', 'mon-rr') archive_month
                        from dual;
                        Please do correct me if I got you wrong.. Thanks!

                        Cheers,
                        Manik.
                        Yes, but i assumed that OP has only SATURDAY and SUNDAY data. Atleast that was what his sample data showed.
                        • 9. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                          Manik
                          yes, I got that.. added it from my side because OP was using the sat and sun condition in his count query..
                          :)

                          No worries.. thx.

                          Cheers,
                          Manik.
                          • 10. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                            1002148
                            hi Karthick,

                            thanks for your response!!

                            I check you query and found that it returns all records even for a weekday (Monday to Friday) which should be omitted. I am looking for data of the employees who worked on consecutive weekends(Saturday and sunday).

                            If an employee worked on 2nd and 3rd of March 2013 alone he should not be returnned as he has worked only on one weekend. If an employee worked on 10th(sunday), 16th(saturday) and 24th(sunday) March 2013 the he should be fetched as he has worked on consecutive weekends.

                            Thanks for your help!!
                            • 11. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                              1002148
                              Hi Jeneesh,

                              thanks for your response!

                              I am validating the data returnned by your query and as far as i can randomly test the output looks perfect.

                              Thanks for your help!!!
                              • 12. Re: Query help - Fetch employees working on two consecutive weekends(SAT/SUN)
                                1002148
                                hi Manik, Karthick and Jeneesh,

                                thanks for your very quick help!!!

                                From the ramdom validation of data both the queries given by Manick and Jeneesh works perfect for my requirement.

                                I shall check further and get back to you if any of my reqirement is missed.

                                Thanks a lot for all your help!

                                Thanks,
                                sathiya