Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1174
  • Last Modified:

Crystal reports last 7 days exclude weekends

Hello,

I need to write a formula for getting dynamic last 5 days excluding weekends. For example

Day 1 = currentdate (11/5/2014)
Day 2 = currentdate -1 (11/4/2014)
Day 3 = Currentdate - 2 (11/3/2014)
Day 4 = currentdate -3 (10/31/2014) Since this is a weekend I need the last Friday's date
Day 5 = currentdate -4 (10/30/2014)

How can I do that? please assist.
Thank you
0
angel7170
Asked:
angel7170
  • 5
  • 3
  • 3
  • +2
1 Solution
 
johnsoneSenior Oracle DBACommented:
This should do it:

SELECT wdate 
FROM   (SELECT wdate 
        FROM   (SELECT Trunc(SYSDATE) - LEVEL + 1 wdate 
                FROM   dual 
                CONNECT BY LEVEL < 14) 
        WHERE  Trim(To_char(wdate, 'day')) NOT IN ( 'saturday', 'sunday' ) 
        ORDER  BY wdate DESC) 
WHERE  ROWNUM <= 5; 

Open in new window


I'm sure there is a fancier way to do it, but this works.  I used 14 as an arbitrary number.  Ideally the most number of days you should have to go back is 9 or 10, but the extra 4 doesn't hurt the query.
0
 
vastoCommented:
You can get the last 7 days and exclude days 1 and 7 (Sunday and Saturday)
The formula might be something like


<<your date field>> IN currentdate-7 to currentdate and not (DayOfWeek<<your date field>>)  in [1, 7])

If you need to get the last full week you can use LastFullWeek function
0
 
johnsoneSenior Oracle DBACommented:
I would discourage you from using day of week 1 and 7.  The day of week is determined by NLS settings and may not necessarily be 1 and 7.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
angel7170Author Commented:
Thank you!

sorry, I was looking for a formula to get last 7 days. These formulas will be used as a header in Crystal reports.

For example

Formula 1 : This should be currentdate
Formula 2 : This should be currentdate - 1 but if it is a weekend subtract days based Saturday or Sunday
Formula 3 : This should be currentdate - 2 but if it is a weekend subtract days based Saturday or Sunday
.........etc

I apologize for not explaining clearly.
0
 
mlmccCommented:
What do you want the formula to display?

mlmcc
0
 
angel7170Author Commented:
display the date
0
 
mlmccCommented:
I reread your previous comment.  You have 5 formulas and want to display 5 dates.

Will the report ever be run on a Saturday or Sunday?

mlmcc
0
 
awking00Commented:
Will you ever be running the query on a Saturday or Sunday?
0
 
angel7170Author Commented:
The report will not be run on weekends only during the weekdays.

Report format is attached in the spreadsheet
0
 
angel7170Author Commented:
Sample format
Book1.xlsx
0
 
johnsoneSenior Oracle DBACommented:
I'm confused as well.  The query that I posted will give you the dates of the last 5 non-weekend days.  That seems to be what you are asking for.
0
 
mlmccCommented:
He wants Crystal formulas to get them.

Formula 1 - Today
CurrentDate

Open in new window


Formula 2 - 1DayAgo
If DayOfWeek (CurrentDate) = crMonday then
    CurrentDate - 3
Else
    CurrentDate -1

Open in new window


Formula 3 - 2DaysAgo
If DayOfWeek (CurrentDate) <= crTuesday then
    CurrentDate - 4
Else
    CurrentDate - 2

Open in new window


Formula 4 - 3DaysAgo
If DayOfWeek (CurrentDate) <= crWednesday then
    CurrentDate - 5
Else
    CurrentDate - 3

Open in new window


Formula 5 - 4DaysAgo
If DayOfWeek (CurrentDate) <= crThursday then
    CurrentDate - 6
Else
    CurrentDate - 4

Open in new window


mlmcc
0
 
angel7170Author Commented:
Thank you. This is exactly what I needed.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now