PL/SQL Date Range Issue

[earljgray]

A client has the following specification for record selection based on a report being run relative to today's date:

Daily = M, Tu, W, Th, F where
Monday execution start date is populated with the prior Friday date, and end date is populated with the prior Sunday date.

the Tuesday execution start and end dates are populated with the prior Monday date.

the Wednesday execution start and end dates are populated with the prior Tuesday date.

the Thursday execution start and end dates are populated with the prior Wednesday date.

the Friday execution start and end dates are populated with the prior Thursday date.

I'm thinking a DECODE is required to determine what today's DAY is then select out the {field_date} associated with the rules above. Been away from Oracle too long - anyone have a construct I could use to sort this out?

Appreciate any guidance....

Crystal Reports Design/training/Consultation
ecsdata@ecsdata.com

 

[SantaMufasa]

Earl,

The DECODE function is available for use only within an SQL statement. The CASE construct is available for use either within SQL or PL/SQL:

select * from dates;

TODAY
----------------
27-JUN-11
28-JUN-11
29-JUN-11
30-JUN-11
01-JUL-11

5 rows selected.

(DECODE function)

select to_char(today,'Dy, Mon dd, yyyy') today
      ,to_char(today-decode(to_char(today,'Dy'),'Mon',3,1),'Dy, Mon dd, yyyy') Execution_Start
      ,to_char(today-1,'Dy, Mon dd, yyyy') Execution_End
 from dates
/

TODAY             EXECUTION_START   EXECUTION_END
----------------- ----------------- -----------------
Mon, Jun 27, 2011 Fri, Jun 24, 2011 Sun, Jun 26, 2011
Tue, Jun 28, 2011 Mon, Jun 27, 2011 Mon, Jun 27, 2011
Wed, Jun 29, 2011 Tue, Jun 28, 2011 Tue, Jun 28, 2011
Thu, Jun 30, 2011 Wed, Jun 29, 2011 Wed, Jun 29, 2011
Fri, Jul 01, 2011 Thu, Jun 30, 2011 Thu, Jun 30, 2011

5 rows selected.

(CASE expression)

select to_char(today,'Dy, Mon dd, yyyy') today
      ,case when to_char(today,'Dy') = 'Mon' then to_char(today-3,'Dy, Mon dd, yyyy')
            else to_char(today-1,'Dy, Mon dd, yyyy') end Execution_Start
      ,to_char(today-1,'Dy, Mon dd, yyyy') Execution_End
 from dates
/

TODAY             EXECUTION_START   EXECUTION_END
----------------- ----------------- -----------------
Mon, Jun 27, 2011 Fri, Jun 24, 2011 Sun, Jun 26, 2011
Tue, Jun 28, 2011 Mon, Jun 27, 2011 Mon, Jun 27, 2011
Wed, Jun 29, 2011 Tue, Jun 28, 2011 Tue, Jun 28, 2011
Thu, Jun 30, 2011 Wed, Jun 29, 2011 Wed, Jun 29, 2011
Fri, Jul 01, 2011 Thu, Jun 30, 2011 Thu, Jun 30, 2011

5 rows selected.

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[carp]

You could use a DECODE or a CASE construct here.

SELECT SYSDATE - DECODE(to_char(SYSDATE,'DAY'),'MONDAY',3,1) FROM DUAL

should get you close (this is untested - hey, it's a holiday!), and assumes you will never run it on a weekend.

 

[carp]

Santa says it, I believe it, and that settles it!
Dave, you're too fast for me.

 

[SantaMufasa]

I say we're both fast...Fast Friends for (nearly) 20 years! (Can you believe it's been that long?)

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[earljgray]

To the both of you - great big thanks!

Crystal Reports Design/training/Consultation
ecsdata@ecsdata.com