oracle query

select mail_last_name,first_name,ds.position,reason_code,schedule_date
from dept_staff ds, emp_unexcld eu
where ds.payroll_id = eu.payroll_id
and ds.schedule_date between '25-mar-2012' and '31-mar-2012'
and ds.payroll_id = 'ZZW0024543'
--------------------------------------
MAIL_LAST_NAME|FIRST_NAME|POSITION|REASON_CODE|SCHEDULE_DATE
Aguirre                     Debra           PHMB                OD                 3/25/2012
Aguirre                     Debra           PHMB                 SH                  3/26/2012
Aguirre                     Debra           PHMB                 SH                  3/28/2012
Aguirre                     Debra           PHMB                 SH                  3/29/2012
Aguirre                     Debra           PHMB                 OD                 3/30/2012
Aguirre                     Debra           PHMB                  SH                  3/31/2012
----------------------------
But I need the report in this format
Name                 Position              SUN   MON   TUE   WED     THU     FRI     SAT
Aguirre,Debra      PHMB                 OD     SH               SH        SH      OD      SH

3/25/2012 is sunday
LVL 6
anumosesAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Try this:

select new_name, position, max(SUN) sun, max(MON) mon, max(TUE) tue, max(WED) ewd, max(THU) thu, max(FRI) fri, max(SAT) sat from(
select mail_last_name || ',' || first_name new_name,ds.position,reason_code
            case when to_char(schedule_date,'DY')='SUN' then reason_code end SUN,
            case when to_char(schedule_date,'DY')='MON' then reason_code end MON,
            case when to_char(schedule_date,'DY')='TUE' then reason_code end TUE,
            case when to_char(schedule_date,'DY')='WED' then reason_code end WED,
            case when to_char(schedule_date,'DY')='THU' then reason_code end THU,
            case when to_char(schedule_date,'DY')='FRI' then reason_code end FRI,
            case when to_char(schedule_date,'DY')='SAT' then reason_code end SAT
from dept_staff ds, emp_unexcld eu
where ds.payroll_id = eu.payroll_id
and ds.schedule_date between '25-mar-2012' and '31-mar-2012'
and ds.payroll_id = 'ZZW0024543'
)
group by new_name, position
/
0
 
sammySeltzerCommented:
try this too:

select (mail_last_name ||","||first_name) as Name,ds.position,
decode(reason_code reason_code,OD,dte,null) SUN,
decode(reason_code reason_code,SH,dte,null) MON,
decode(reason_code reason_code,null,dte,null) TUE,
decode(reason_code reason_code,SH,dte,null) WED,
decode(reason_code reason_code,SH,dte,null) THU,
decode(reason_code reason_code,OD,dte,null) FRI,
decode(reason_code reason_code,SH,dte,null) SAT
from( select schedule_date as dte from 
from dept_staff ds, emp_unexcld eu
where ds.payroll_id = eu.payroll_id
and ds.schedule_date between '25-mar-2012' and '31-mar-2012'
and ds.payroll_id = 'ZZW0024543')
group by name, position

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>decode(reason_code reason_code,OD,dte,null) SUN,

Syntax is wrong.  Also the logic.  They want to return the reason_code when the date is 'SUN', etc...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sammySeltzerCommented:
Unfortunately, I have no way of testing oracle right now but I think you are right; DECODE probably takes 3 arguments instead of 4.

As for what the user needs, I am sure he can decide that for himself.

If I am provided the wrong solution, i will apologize for it.
0
 
anumosesAuthor Commented:
slightwv,

Can we add order by
department_id fron dept_staff,
position,
new name,payroll_id from emp
mail last name and first name
0
 
slightwv (䄆 Netminder) Commented:
>>Can we add order by

You can add whatever order by you want.  Just add the order to the original select you posted.
0
 
anumosesAuthor Commented:
select payroll_id,
       department_id,
       new_name,
       position,
         max(SUN) sun,
         max(MON) mon,
         max(TUE) tue,
         max(WED) wed,
         max(THU) thu,
         max(FRI) fri,
         max(SAT) sat
 from(
select   eu.payroll_id,ds.department_id,first_name || ',' || mail_last_name new_name,ds.position,reason_code,
            case when to_char(schedule_date,'DY')='SUN' then reason_code end SUN,
            case when to_char(schedule_date,'DY')='MON' then reason_code end MON,
            case when to_char(schedule_date,'DY')='TUE' then reason_code end TUE,
            case when to_char(schedule_date,'DY')='WED' then reason_code end WED,
            case when to_char(schedule_date,'DY')='THU' then reason_code end THU,
            case when to_char(schedule_date,'DY')='FRI' then reason_code end FRI,
            case when to_char(schedule_date,'DY')='SAT' then reason_code end SAT
from dept_staff ds, emp_unexcld eu , department d
where ds.payroll_id = eu.payroll_id
  and d.department_id = ds.department_id
and ds.schedule_date between '25-mar-2012' and '31-mar-2012'
and eu.termination_date is null
and d.area_cd in ('BC','IN')
--and ds.payroll_id = 'ZZW0024543'
)
group by  payroll_id,department_id,new_name, position
order by department_id,position,new_name,payroll_id
0
 
slightwv (䄆 Netminder) Commented:
>>order by department_id,position,new_name,payroll_id

You need to add the order by on the INNER select.

something like:


select payroll_id,
       department_id,
       new_name,
       position,
         max(SUN) sun,
         max(MON) mon,
         max(TUE) tue,
         max(WED) wed,
         max(THU) thu,
         max(FRI) fri,
         max(SAT) sat
 from(
select   eu.payroll_id,ds.department_id,first_name || ',' || mail_last_name new_name,ds.position,reason_code,
            case when to_char(schedule_date,'DY')='SUN' then reason_code end SUN,
            case when to_char(schedule_date,'DY')='MON' then reason_code end MON,
            case when to_char(schedule_date,'DY')='TUE' then reason_code end TUE,
            case when to_char(schedule_date,'DY')='WED' then reason_code end WED,
            case when to_char(schedule_date,'DY')='THU' then reason_code end THU,
            case when to_char(schedule_date,'DY')='FRI' then reason_code end FRI,
            case when to_char(schedule_date,'DY')='SAT' then reason_code end SAT
from dept_staff ds, emp_unexcld eu , department d
where ds.payroll_id = eu.payroll_id
  and d.department_id = ds.department_id
and ds.schedule_date between '25-mar-2012' and '31-mar-2012'
and eu.termination_date is null
and d.area_cd in ('BC','IN')
--and ds.payroll_id = 'ZZW0024543'
order by department_id,position,first_name || ',' || mail_last_name ,payroll_id
)
group by  payroll_id,department_id,new_name, position
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.