SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help with query to get working days ago using BOM calendar

t_norwilloDec 8 2010 — edited Dec 8 2010

RDBMS : 10.2.0.4.0
Oracle Applications : 11.5.10.2

I'm trying to use the existing BOM calendar in the EBS to derive working days ago for a report I'm working on. The BOM calendar lists working days and non-working days with weekends and holidays listed as non-working days. The following query gives the correct results but I'm looking for alternate ways to do the same thing without the 'union'. I've got nothing against 'unions' but I feel like I'm missing a more elegant way to accomplish this. The query is going into Discoverer Plus 10.1.2.3, so using a "With" statement is not supported.

sample data

calendar_code                calendar_date                 seq_num                     
SAC-WRKDAY                   12/3/2010                     1817
SAC-WRKDAY                   12/4/2010                                                              
SAC-WRKDAY                   12/5/2010                                                              
SAC-WRKDAY                   12/6/2010                     1818                                 
SAC-WRKDAY                   12/7/2010                     1819                                 
SAC-WRKDAY                   12/8/2010                     1820     
SAC-WRKDAY                   12/9/2010                     1821
SAC-WRKDAY                   12/10/2010                    1822
SAC-WRKDAY                   12/11/2010                   
SAC-WRKDAY                   12/12/2010
SAC-WRKDAY                   12/13/2010                    1823

select calendar_code
     , calendar_date
     , seq_num
     , sum(decode(bcd.seq_num,null,0,1))over(partition by bcd.calendar_code order by bcd.calendar_date desc) workdays_ago
from   bom.bom_calendar_dates bcd
where  calendar_code = 'SAC-WRKDAY'	
and    trunc(bcd.calendar_date) < trunc(sysdate)

union

select calendar_code
     , calendar_date
     , seq_num
     , -sum(decode(bcd.seq_num,null,0,1))over(partition by bcd.calendar_code order by bcd.calendar_date ) workdays_ago
from   bom.bom_calendar_dates bcd
where  calendar_code = 'SAC-WRKDAY'
and    trunc(bcd.calendar_date) > trunc(sysdate)

sample output

calendar_code                calendar_date                 seq_num                      workdays_ago
SAC-WRKDAY                   12/3/2010                     1817                                  3
SAC-WRKDAY                   12/4/2010                                                           2
SAC-WRKDAY                   12/5/2010                                                           2
SAC-WRKDAY                   12/6/2010                     1818                                  2
SAC-WRKDAY                   12/7/2010                     1819                                  1
SAC-WRKDAY                   12/9/2010                     1821                                 -1
SAC-WRKDAY                   12/10/2010                    1822                                 -2
SAC-WRKDAY                   12/11/2010                                                         -2
SAC-WRKDAY                   12/12/2010                                                         -2
SAC-WRKDAY                   12/13/2010                    1823                                 -3

This post has been answered by Frank Kulash on Dec 8 2010

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 5 2011

Added on Dec 8 2010

2 comments

4,028 views