Clicky

I can identify the last 3 days of the previous month as shown in the code below, but am at a loss to get a simple means of identifying the last 3 business days (those being Monday through Friday). This needs to work dynamically regardless of what month or what day in that month it is run..

Here is what I've got for just the last 3 days:

Select
case_detail_id
, trunc(creation_dt) as Creation_DT
FROM reports.cers_rpt_case_detail_v
Where trunc(creation_dt) >= last_day(add_months(trunc(Sysdate),-1))-2
and trunc(creation_dt) <= last_day(add_months(trunc(Sysdate),-1))
and status_cd = 'CLOSED'
Order by trunc(creation_dt)

Anyone have a simple method for doing this?

Thank you,
Will

asked 12/15/2011 11:19

eSeattle's gravatar image

eSeattle ♦♦


24 Answers:
>>> simple method

hopefully this isn't too bad...

you can put this query in an inline or stand-alone view and join to it

SELECT d
  FROM (SELECT     TRUNC(sysdate, 'mm') - 6 + LEVEL d
              FROM DUAL
        CONNECT BY LEVEL <= 5
          ORDER BY d DESC)
 WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun') AND ROWNUM <= 3
link

answered

sdstuber's gravatar image

sdstuber

or, if you don't want to use the connect by, you can do it directly...


SELECT d
  FROM (SELECT TRUNC(SYSDATE, 'mm') - 1 d FROM DUAL
        UNION ALL
        SELECT TRUNC(SYSDATE, 'mm') - 2 d FROM DUAL
        UNION ALL
        SELECT TRUNC(SYSDATE, 'mm') - 3 d FROM DUAL
        UNION ALL
        SELECT TRUNC(SYSDATE, 'mm') - 4 d FROM DUAL
        UNION ALL
        SELECT TRUNC(SYSDATE, 'mm') - 5 d FROM DUAL
        ORDER BY 1 DESC)
 WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun') AND ROWNUM <= 3;
link

answered 2011-12-16 at 07:37:18

sdstuber's gravatar image

sdstuber

I'm not sure how that would be incorporated into my query above. I tried placing it into the where statement but it throws an error. Can you provide me with where this would go in my query?

Thank you!
link

answered 2011-12-16 at 07:40:42

eSeattle's gravatar image

eSeattle

ugly but :)


select
 last_day(add_months(trunc(Sysdate),-1)) - decode(to_number(to_char(last_day(add_months(trunc(Sysdate),-1)),'d')),7,3,3,4,2,4,1,4,2) start_day,
 last_day(add_months(trunc(Sysdate),-1)) - decode(to_number(to_char(last_day(add_months(trunc(Sysdate),-1)),'d')),7,1,1,2,0) end_day
from dual

this gives me

11/28/2011 11/30/2011
link

answered 2011-12-16 at 08:05:00

HainKurt's gravatar image

HainKurt

and if I use 2 months before

select
 last_day(add_months(trunc(Sysdate),-2)) - decode(to_number(to_char(last_day(add_months(trunc(Sysdate),-2)),'d')),7,3,3,4,2,4,1,4,2) start_day,
 last_day(add_months(trunc(Sysdate),-2)) - decode(to_number(to_char(last_day(add_months(trunc(Sysdate),-2)),'d')),7,1,1,2,0) end_day
from dual

10/27/2011 10/31/2011
link

answered 2011-12-16 at 08:13:02

HainKurt's gravatar image

HainKurt

try this...


SELECT   case_detail_id, TRUNC(creation_dt) AS creation_dt
    FROM reports.cers_rpt_case_detail_v,
         (SELECT d
            FROM (SELECT     TRUNC(SYSDATE, 'mm') - 6 + LEVEL d
                        FROM DUAL
                  CONNECT BY LEVEL <= 5
                    ORDER BY d DESC)
           WHERE TO_CHAR(d, 'Dy') NOT IN ('Sat', 'Sun') AND ROWNUM <= 3)
   WHERE creation_dt >= d AND creation_dt < d + 1 AND status_cd = 'CLOSED'
ORDER BY TRUNC(creation_dt)
link

answered 2011-12-16 at 08:15:26

sdstuber's gravatar image

sdstuber

sdstuber,

This appears to be working great. What part determines the previous month? I would like to test this on different months dealling with the weekends.

Thank you!

Also thank you HainKurt. Your's looked like it was good but I think the days need to be specifically identified rather than a begin and end range.
link

answered 2011-12-16 at 08:25:54

eSeattle's gravatar image

eSeattle

>>>What part determines the previous month

TRUNC(SYSDATE, 'mm')

this returns the 1st of the CURRENT month,  subtracting 6 + 1..5  days puts us into the previous month.
then simply check if each of those 5 days is Saturday or Sunday, if they are remove them,
sort the values so last days show up first and pick the first 3.
link

answered 2011-12-16 at 08:44:29

sdstuber's gravatar image

sdstuber

the union all method does the exact same thing,  except the list of previous month's last 5 days is explicitly listed rather than generated by the connect by method
link

answered 2011-12-16 at 08:48:55

sdstuber's gravatar image

sdstuber

"I think the days need to be specifically identified rather than a begin and end range"

maybe this

trunc(some_column) in (
 last_day(add_months(trunc(Sysdate),-1)) - decode(to_number(to_char(last_day(add_months(trunc(Sysdate),-1)),'d')),7,3,3,4,2,4,1,4,2), -- day1
 last_day(add_months(trunc(Sysdate),-1)) - decode(to_number(to_char(last_day(add_months(trunc(Sysdate),-1)),'d')),7,2,2,3,1,3,1) day2, --day2
 last_day(add_months(trunc(Sysdate),-1)) - decode(to_number(to_char(last_day(add_months(trunc(Sysdate),-1)),'d')),7,1,1,2,0) --day3
)
link

answered 2011-12-16 at 08:50:28

HainKurt's gravatar image

HainKurt

logic is simple

trunc(some_column) in (
last_day(add_months(trunc(Sysdate),-1)) - decode(7,?,6,?,5,?,4,?,3,?,2,?,1,?), --day 1
last_day(add_months(trunc(Sysdate),-1)) - decode(7,?,6,?,5,?,4,?,3,?,2,?,1,?), --day 2
last_day(add_months(trunc(Sysdate),-1)) - decode(7,?,6,?,5,?,4,?,3,?,2,?,1,?), --day 3
)

just fill up the ? :) I did above, but if there is an issue use 7 days, fill in all ?... open a calendar and see what those ? should be
link

answered 2011-12-16 at 09:00:17

HainKurt's gravatar image

HainKurt

I don't recommend the TRUNC(some_column)  method.  I took the TRUNC out of the original query on purpose
when I wrote the suggestion above.  The TRUNC  prevents usage of indexes on some_column


The same three lines could be unioned and put into an inline view as shown above though
link

answered 2011-12-16 at 09:02:58

sdstuber's gravatar image

sdstuber

if the table is big, yes we should not use any transformation on original column, then we can do this

trunc(some_column) in (
last_day(add_months(trunc(Sysdate),-1)) - decode(7,?,6,?,5,?,4,?,3,?,2,?,1,?), --day 1
last_day(add_months(trunc(Sysdate),-1)) - decode(7,?,6,?,5,?,4,?,3,?,2,?,1,?), --day 2
last_day(add_months(trunc(Sysdate),-1)) - decode(7,?,6,?,5,?,4,?,3,?,2,?,1,?), --day 3
)

-->

    some_column between day1 and day1+1
or some_column between day2 and day2+1
or some_column between day3 and day3+1

or using >= & <

    (some_column >= day1 and some_column < day1+1)
or (some_column >= day2 and some_column < day2+1)
or (some_column >= day3 and some_column < day3+1)

where dayx is above
link

answered 2011-12-16 at 09:03:10

HainKurt's gravatar image

HainKurt

here's an example of using the decode method.  I took out the double conversion of to_char and to_number, mostly just for my own convenience. I also removed the add_months/last_date.  I didn't really need to find the last day of the previous month.
First of this month minus 1 is sufficient for that, but since we're already subtracting, it was simpler to just put the 1 into the decode
and thus first of current month was the only date needed for the derivation.


SELECT   case_detail_id, TRUNC(creation_dt) AS creation_dt
    FROM reports.cers_rpt_case_detail_v,
         (SELECT trunc(sysdate,'mm')-DECODE(TO_CHAR(TRUNC(sysdate,'mm'),'Dy'),'Mon',5,'Tue',5,'Wed',5,'Sun',4,3) d        
  FROM x
UNION ALL
SELECT trunc(sysdate,'mm')-DECODE(TO_CHAR(TRUNC(sysdate,'mm'),'Dy'),'Mon',4,'Tue',4,'Sun', 3,  2)        
  FROM x
UNION ALL
SELECT trunc(sysdate,'mm')-DECODE(TO_CHAR(TRUNC(sysdate,'mm'),'Dy'),'Mon',3,'Sun',2,1)          
  FROM x)
   WHERE creation_dt >= d AND creation_dt < d + 1 AND status_cd = 'CLOSED'
ORDER BY TRUNC(creation_dt)
link

answered 2011-12-16 at 09:09:57

sdstuber's gravatar image

sdstuber


using between as in ...

some_column between day1 and day1+1

isn't reliable  because between is endpoint INclusive. effectively the same as >=   <=
which means you'll include midnight of the next day

 >=  and <  is correct,
 inclusive on beginning of range, but exclusive on end of range
link

answered 2011-12-16 at 09:23:40

sdstuber's gravatar image

sdstuber

what about this

Where trunc(creation_dt) in
(
select * from (
  select * from (
    select last_day(add_months(trunc(Sysdate),-1))-n myDay from (select 0 n from dual union select 1 from dual union   select 2 from dual union select 3 from dual union select 4 from dual)
    ) x where to_char(myDay,'d') not in (1,7)
  order by 1 desc
  ) xx where rownum <=3
)

inner select gives you max 5 days which are not sunday, saturday, the other select limits it to 3
link

answered 2011-12-16 at 09:25:14

HainKurt's gravatar image

HainKurt

you're still doing   TRUNC on the creation date which prevents indexes.

also.  why do this...


last_day(add_months(trunc(Sysdate),-1))-n


when

trunc(sysdate,'mm') -1 -n  

would be simpler, or even easier,  just change your union all list to 1-5  instead of 0-4 and then


trunc(sysdate,'mm') -n  

which is effectively what the first 2 were doing .

link

answered 2011-12-16 at 10:22:24

sdstuber's gravatar image

sdstuber

if you want to keep the same model,  use EXISTS instead of IN

note, I removed the implicit conversion on the (1,7) comparison


 WHERE EXISTS
           (SELECT NULL
              FROM (SELECT *
                      FROM (SELECT   *
                                FROM (SELECT TRUNC(SYSDATE, 'mm') - n myday
                                        FROM (SELECT 1 n FROM DUAL
                                              UNION
                                              SELECT 2 FROM DUAL
                                              UNION
                                              SELECT 3 FROM DUAL
                                              UNION
                                              SELECT 4 FROM DUAL
                                              UNION
                                              SELECT 5 FROM DUAL)) x
                               WHERE TO_CHAR(myday, 'd') NOT IN ('1', '7')  -- or TO_CHAR(myday,'Dy') NOT IN ('Sat','Sun')
                            ORDER BY 1 DESC) xx
                     WHERE ROWNUM <= 3)
             WHERE creation_dt >= myday AND creation_dt < myday + 1)
link

answered 2011-12-16 at 10:25:48

sdstuber's gravatar image

sdstuber

I know I used trunc :) just to give different methods to find last 3 day, and you are right about to make it simple

select * from (
select * from (
select trunc(Sysdate,'mm')-n myDay from (select 1 n from dual union select 2 from dual union select 3 from dual union select 4 from dual union select 5 from dual)
) x where to_char(myDay,'d') not in (1,7)
order by 1 desc
) xx where rownum <=3

is better than previous one...
link

answered 2011-12-16 at 10:28:22

HainKurt's gravatar image

HainKurt

and to get rid of trunc we can join above query with original one as

from  reports.cers_rpt_case_detail_v v, (
select * from (
select * from (
select trunc(Sysdate,'mm')-n myDay from (select 1 n from dual union select 2 from dual union select 3 from dual union select 4 from dual union select 5 from dual)
) x where to_char(myDay,'d') not in (1,7)
order by 1 desc
) xx where rownum <=3
) d
where creation_dt >= d.myday and creation_dt < myday +1

:)
link

answered 2011-12-16 at 10:31:47

HainKurt's gravatar image

HainKurt

sdstuber, does it matter to use "exists" or "in" for just 3 value?
link

answered 2011-12-16 at 10:35:07

HainKurt's gravatar image

HainKurt

EXISTS lets you do range comparison on the unmodified creation_dt column

IN does not



"if' the data is already truncated, simply remove TRUNC and the IN will work just fine,  if it's not truncated, then you can't do that.
Since the range comparison is efficient and accurate in all cases,
whether the data is truncated or not, whether there are indexes on that column or not.
There's no good reason to avoid it that I can see
link

answered 2011-12-16 at 10:38:43

sdstuber's gravatar image

sdstuber

I will throw out one caveat...

"IF" there is a function-based index on TRUNC(creation_dt) then my comments above about not using indexes don't apply.

everything else still works,  you just use direct comparison (or IN on a set) rather than range comparisons.
link

answered 2011-12-16 at 10:39:58

sdstuber's gravatar image

sdstuber

sdstuber: Thank you for your assistance! It's greatly appreciated. Your first solution using my query works perfectly and tests well when I changed it to look several months back.

Thanks again!
link

answered 2011-12-16 at 10:45:17

eSeattle's gravatar image

eSeattle

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×51
×132

Asked: 12/15/2011 11:19

Seen: 282 times

Last updated: 12/16/2011 05:07