Filter Date based on friday and holiday

[former_member225386]

Hi All,

I need your suggestion that I need to filter a date coulmn, were it will normally it should show friday only. Suppose if the friday is an public holiday then it suppose need to show the previous day. :S

Is it possible in Crystal report 2008?

Please provider your suggestion.

[former_member225386]

Hi Abhilash,

Is the above requirement can be achived via Command option? And

simply like list all date based on user filter in CR without having any table field.

Thanks!

Regards,

Ramesh Jothimani

[raghavendra_hullur]

Hi,

Assuming you are looking for future Friday details only,

below query gives me all dates and respective day for next 730 days(approximately 2 years and this can be changed).

SELECT TRUNC(SYSDATE) + ROWNUM D, TO_CHAR(TRUNC(SYSDATE) + ROWNUM, 'DAY') D2 

FROM ALL_OBJECTS

WHERE ROWNUM < 730

Since I am using rownum function, I cannot restrict the dates only for Friday at command query level, so I have to use below record selection formula in order to get dates related to Friday only.

{Command.D2} = 'FRIDAY'

This was tested on Oracle database and with Crystal Reports 2011 version, in case you are using a different database, you have to modify the code accordingly.

This may not be the complete solution you are looking for, but I hope this gives an idea on your requirement for you.

Thanks,

Raghavendra

[abhilash_kumar]

I found another query from one of the Oracle Forums. This list all Fridays and you can add this as a Command Object:

SELECT dt, ROWNUM

FROM (SELECT TO_DATE ('01/01/2014', 'mm/dd/yyyy') + (LEVEL - 1) dt

FROM DUAL

CONNECT BY LEVEL <= SYSDATE - TO_DATE ('08/01/2007', 'mm/dd/yyyy'))

WHERE TRIM (TO_CHAR (dt, 'DAY')) = 'FRIDAY' AND

ROWNUM < 200

P.S: This is for Oracle.

-Abhilash

[former_member225386]

Hi,

Previously I used the formula WeekDayName(DayOfWeek(<your database field for date>)) = 'Friday' to filter the record in the report but the requirement has been changed as stated above.

We have a column in database that specify the public holiday date that Coincided with Friday of the week.

So using that public holiday field/column in Database how we can filter the column date in report? else as Abhilash stated do I need to create a column in table to maintain yes or no concept for all Friday to specify if it is a public holiday or not?

Please help me on this.

Thanks!

Regards,

Ramesh Jothimani

[abhilash_kumar]

Yes, having a table in the database is a good way to workaround this. Not only would you need the table to list all holidays, you would also need to make sure it includes dates where : If Friday is a Holiday, then Friday -1

You can then simply add a record selection formula that compares the date field in the original table with the date column in this holiday table.

-Abhilash

[abhilash_kumar]

Hi Ramesh,

There are many ways to do this in CR however, almost all of them are the most inefficient ways to arrive at.

The best solution, as Raghav stated is to create a look-up table that holds all the Fridays and Friday-1 is it's a holiday. This way, you can make sure everything is passed back to the database for processing.

Another way to do this, if you cannot create a look-up table is by adding the logic in a PL/SQL block and using a Stored Proc as the datasource for the report.

-Abhilash

[raghavendra_hullur]

Hi Ramesh,

If you want to show data for Friday only, then you can use a record selection formula like below:

toText(<your date field>, 'dddd') = 'Friday'

But, as Nishit told, I don't think there is any holiday list maintained in database. You may have to first store the holiday list and then use that for checking with your date field.

Thanks,

Raghavendra

[former_member203899]

Dear Ramesh,

Please provide more details for this requirement.

If you want to skip all fridays then it will possible.

But you have to fix particular day.

SQL will can't identify that day having holiday or anything else.

Regards,

Nishit Makadia