Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert Query SQL into Select Expert Crystal

Status
Not open for further replies.

Cryst4LN00B

IS-IT--Management
Aug 4, 2020
2
BE
Hello there,

So I have a report that I need to get the last 7 days of Work counting from today, excluding Saturday and Sunday.
I'm new to Crystal and I searched on several websites and the only thing I get is with 2 intervals dates,

Using this query works

SELECT COUNT(*) FROM T_Orders WHERE DATE_Limit BETWEEN SYSDATE AND SYSDATE + 7 and trim(' ' from to_char(DATE_Limit ,'DAY')) not in ('SATURDAY','SUNDAY')

The problem is what command on Crystal Reports is the same as trim and for SYSDATE ?

Can anyone explain to me how do I convert this into a Select Expert-Record on Crystal Reports ?

Thanks for your help in advance,

 
trim is trim and sysdate is currentdate(). Of course not sure why you would need to convert it to Crystal Syntax. Depending on what you are doing, you can use a command to be your data source.
 
What kray4660 is saying is you copy and paste your SQL directly into crystal reports and use it as your datasource, so there is no need to try to "translate" your logic into the select expert.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

 
Thanks kray4660 for your support!

In fact my problem is I need to get a result from TODAY and -7 days of working including today and exclude SAT and Sun weekends as well, and I cannot find any kind of information on google.
Just the normal case using 2 different defined dates, nothing else.
 
My misunderstanding. I thought you had a working query that gave you the results you needed. There is a function in Crystal called DayOfWeek that returns a number (1 to 7) for which day of the week is it. Sunday is the default for 1. But you can change that. Thus a kinda of guess to write a formula in the Select Expert. I probably would look something like this.

{Date_Limit) >= currentDate and {Date_Limit) <= currentDate + 7 and not(DayOfWeek in (1,7))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top