Business Days in query 2

[conrads57]

I have this query which pulls all the cases that are 14 calendar days old....I do not know how to change calendar to business days. So for example if I run the query as it is...I get all case with a date of 06/30/2008 but need the ones with a date of 06/25/2008. Posted is the query can someone help? I don't have access to write functions and I am till learning to write queries.

select
mem_id,
cme_hmp_code,
cme_date_begin

from
cme_case_management_episode,
hmp_health_mgmnt_program,
mem_member

where
cme_mem_uid = mem_uid
and hmp_code = cme_hmp_code
and hmp_system_meaning = 'CCM'
and cme_status = 'A'
and trunc(cme_date_begin) = trunc(sysdate) - 14
and not exists (select mno_uid from mno_mem_note where mno_cme_uid = cme_uid)

order by
cme_date_begin

 

[Dagon]

What is your definition of a business day ? Since 30-Jun is a monday, I assume you don't just mean week days. Why do you want 25-jun rather than 30-jun ? Is that something to do with public holidays ?

 

[conrads57]

Our business days are Monday thru Friday.

 

[Dagon]

Something like the following could be used to count a number of business days backwards from the current date. You could probably do it formulaically as well, but I haven't quite got my head around how to do that yet.

select the_date from
(select the_date, rownum as rn from
(select trunc(sysdate)-level as the_date
from dual
connect by level<=30)
where to_char(the_date, 'DY') not in ('SAT', 'SUN'))
where rn=13

 

[SantaMufasa]

The code that you posted, Dagon, is delightfully clever! Hava

!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[conrads57]

Thank you Dagon, that worked, I appreciate the help.

 

[conrads57]

Now they came back and also want to exlude our Holidays from the days as well. We have a table created that lists the holidays. I have tried this but it is not giving me the right dates again. Here is the query.

select
mem_id,
cme_hmp_code,
cme_date_begin

from
cme_case_management_episode,
hmp_health_mgmnt_program,
mem_member

where
cme_mem_uid = mem_uid
and hmp_code = cme_hmp_code
and hmp_system_meaning = 'CCM'
and cme_status = 'A'
and trunc(cme_date_begin) =
(select bdate
from (
select bdate,
rownum as rn
from (
select trunc(sysdate) - level as bdate
from dual
connect by level < = 30)
where to_char(bdate,'DY') not in ('SAT','SUN')
and not exists (select holiday from holidays where holiday = bdate ))
where rn = 13)
and not exists (select mno_uid from mno_mem_note where mno_cme_uid = cme_uid)

order by
cme_date_begin

 

[SantaMufasa]

Conrad,

I'm sure we can post helpful code if you will please post the format of your company-holidays table, along with a couple of rows of sample data from that table.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[conrads57]

There are only 2 columns and sample data.

holiday (datetime)
desc (varcharw2(35))

01/01/2008 00:00:00 New Year's Day
01/21/2008 00:00:00 Martin Luther King, Jr. Day
02/18/2008 00:00:00 President's Day
05/26/2008 00:00:00 Memorial Day
07/04/2008 00:00:00 4th of July
09/01/2008 00:00:00 Labor Day
11/27/2008 00:00:00 Thanksgiving Day
11/28/2008 00:00:00 Day After Thanksgiving
12/24/2008 00:00:00 Christmas Eve
12/25/2008 00:00:00 Christmas Day
12/26/2008 00:00:00 Day After Christmas

 

[SantaMufasa]

I'm sorry, Conrad, that I did not notice earlier that your previous code contained the details of your HOLIDAYS table. (BTW, are you sure that you have a column named "DESC"?...That is usually flagged as an illegal use of an Oracle keyword, unless you created the column and always refer to it within double quotes.)

What is the problem you are having with the code you posted earlier?...Syntax error...Run-time error...Logic error? Could you please post samples of the error you are seeing?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[conrads57]

Sorry the column name is description. When I run the query as I have it written I get cases with a date of 07/07/2008. I am expecting to get ones dated 06/25/2008 (minus the business and 4th of July holiday.

Thank you.

 

[SantaMufasa]

Since I do not have your "cme_case_management_episode", "hmp_health_mgmnt_program", and "mem_member" tables with which to troubleshoot your code, I recommend your stripping down your code to just the part that calculates the days, and isolate the logic error you are experiencing. Then you can re-introduce the working code into your full query.

Let us know what you find.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[rcurva]

Just an idea. How about using an inline query instead and placing your holiday date criteria outside your business days query, like;

select
mem_id,
cme_hmp_code,
cme_date_begin

from
cme_case_management_episode,
hmp_health_mgmnt_program,
mem_member,

(select bdate
from
(
select bdate, rownum as rn
from (
select trunc(sysdate) - level as bdate
from dual
connect by level < = 30)
where to_char(bdate,'DY') not in ('SAT','SUN')
)
where rn = 13
)
) x

where
cme_mem_uid = mem_uid
and hmp_code = cme_hmp_code
and hmp_system_meaning = 'CCM'
and cme_status = 'A'
and trunc(cme_date_begin) = x.bdate
and not exists (select holiday from holidays where holiday = x.bdate )
and not exists (select mno_uid from mno_mem_note where mno_cme_uid = cme_uid)
order by
cme_date_begin



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided&quot" -- Art of War