# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior 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;
``````

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.
Commented:
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
Senior 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.
Author 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.
Commented:
What do you want the formula to display?

mlmcc
Author Commented:
display the date
Commented:
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
Information Technology SpecialistCommented:
Will you ever be running the query on a Saturday or Sunday?
Author Commented:
The report will not be run on weekends only during the weekdays.

Report format is attached in the spreadsheet
Author Commented:
Sample format
Book1.xlsx
Senior 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.
Commented:
He wants Crystal formulas to get them.

Formula 1 - Today
``````CurrentDate
``````

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

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

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

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

mlmcc

Experts Exchange Solution brought to you by