Re: Employment per week

Christopher,

Thank you very much for your help.

Vladimir
"Christopher Latta" <nobody_at_nowhere.not> wrote in message news:rpAV6.3806$qJ4.166898_at_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 Wed Jun 13 2001 - 10:53:54 CDT