Re: Employment per week

From: Christopher Latta <nobody_at_nowhere.not>
Date: Wed, 13 Jun 2001 12:35:14 +1000
Message-ID: <rpAV6.3806$qJ4.166898@ozemail.com.au>

There are probably two approaches here:

1. Build an insanely long union statement programatically:

select '27-MAY-2001' as Week_Start, '02-JUN-2001' as Week_End, Count(emp_num) as Emp_Count
from emp_table
where Start_Date <= '27-MAY-2001' and Term_date >= '02-JUN-2001' union
select '03-JUN-2001' as Week_Start, '09-JUN-2001' as Week_End, Count(emp_num) as Emp_Count
from emp_table
where Start_Date <= 03-JUN-2001' and Term_date >= '09-JUN-2001' union
etc, etc....
order by 1

2. Run a stored procedure to populate a table which holds your results, and report on that:

First create yourself a table to hold the results:

create table WeekReport
  (Week_Start Date,
  Week_End Date,
  Emp_Count Number(8,0));

Now have a procedure to populate your table:

create or replace procedure BuildWeekReportTable   (p_ReportStart IN Date, -- Date for report start = start of first week   p_ReportEnd IN Date) -- Date for report end   IS

  dWeekStart        Date;
  dWeekEnd          Date;

begin
  Truncate Table WeekReport;
  dWeekStart := p_ReportStart;
  while dWeekStart < p_ReportEnd loop
    insert into WeekReport

      (Select dWeekStart, dWeekStart+6, count(emp_num)
      from emp_table
      where dWeekStart >= Start_Date
        and dWeekStart+6 <= Term_date);

    dWeekStart := dWeekStart + 7;
  end loop;
end;

To do your report, run the procedure. You can do this in SQL Plus as follows:

begin
  BuildWeekReportTable('27-MAY-2001', '28-OCT-2001'); end;

Now all you need to do is:
select * from WeekReport order by Week_Start and you have your report.

This is off the top of my head so may need some tweaking, but you get the idea.

My preference is for method 2.

HTH,
Christopher Latta

Vladimir Ivanovich <vladimiri_at_bosmedtech.com> wrote in message news:NXwV6.5$566.5938_at_news.nyc.globix.net...
> I need to create a report that counts employees for each week.
> The output should look like this:
>
> Week_Start Week_End Emp_Count
> ********************************
> 05/27/2001 06/08/2001 42
> 06/03/2001 06/09/2001 44
> 06/10/2001 06/16/2001 44
>
> etc.
>
> The Emp table has emp_num, Emp_name, Start_date and Term_Date along with
 the
> rest.
> Only full employment weeks should be counted for each employee.
Received on Tue Jun 12 2001 - 21:35:14 CDT