Hi , I have a doubt in SQL . Supp

[uparna]

Hi ,
I have a doubt in SQL . Suppose i have 2 tables

table 1 : employee_id
time
effort_date
week_ending

table 2 : employee_id
authorization
week_ending

and i have a query that runs like this :

select
emp_id , sum(time), effort_date , authorization
from
table1 a , table2 b
where
a.employee_id = b.employee_id and
a.week_ending = b.week_ending and
a.employee_id = '007' and
a.effort_date between
to_date('01/09/2001','dd/mm/yyyy') and
to_date('30/09/2001','dd/mm/yyyy')
group by
emp_id , effort_date , authorization ;


this returns the following data :

007 9 24/09/2001 30/09/2001
007 14 27/09/2001 30/09/2001


but i want the data in this fashion for the month of september

007 0 01/09/2001 02/09/2001
007 0 02/09/2001 09/09/2001
007 0 03/09/2001 09/09/2001
007 0 04/09/2001 09/09/2001
007 0 05/09/2001 09/09/2001
007 0 06/09/2001 09/09/2001
007 0 07/09/2001 09/09/2001
007 0 08/09/2001 09/09/2001
.
.
.
.
.
007 0 23/09/2001 30/09/2001
007 9 24/09/2001 30/09/2001
.
.
.
007 14 27/09/2001 30/09/2001
.
.
.
007 0 30/09/2001 30/09/2001



how do i achieve this ? There is no problem with the week endings , as my table 2 has all the weekendings in a year.



Thanks in advance !

Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[Kenrae]

A union comes to mind:

select 
emp_id , sum(time), effort_date , authorization 
from  
table1  a , table2 b 
where
    a.employee_id = b.employee_id and 
    a.week_ending = b.week_ending and 
    a.employee_id = '007' and
    a.effort_date between 
to_date('01/09/2001','dd/mm/yyyy') and 
to_date('30/09/2001','dd/mm/yyyy')
    group by 
    emp_id , effort_date , authorization

UNION

select 
emp_id, 0 as sum(time), d.my_date as effort_date, authorization
from
table1 a, table2 b, table_dates d
where
    a.employee_id = b.employee_id and
    a.week_ending = b.week_ending and
    a.employee_id = '007' and
    d.my_date between
      to_date('01/09/2001','dd/mm/yyyy') and 
      to_date('30/09/2001','dd/mm/yyyy') and
    d.my_date < b.authorization and
    not exists(
       select 
       emp_id , sum(time), effort_date , authorization 
       from  
       table1  a2 , table2 b2
       where
         a2.employee_id = b2.employee_id and 
         a2.week_ending = b2.week_ending and 
         a2.employee_id = '007' and
         a2.effort_date = d.my_date and
         a2.effort_date between 
           to_date('01/09/2001','dd/mm/yyyy') and 
           to_date('30/09/2001','dd/mm/yyyy')
         group by 
         emp_id , effort_date , authorization
       );

being table_dates a table with all dates in a year.

 

[Kenrae]

Oh, and another thing, if you want the data ordered, you should write:

select *
from (mega-query here)
order by (you order criteria);

since unions don't accept order by clauses.

 

[carp]

Unless you list the columns in the order you want them sorted. Oracle does a sort in the course of a UNION. If you want to avoid the sort, then do a UNION ALL (although this will result in Oracle removing duplicate rows).