Technical Questions: Syntax for datediff and date range problems

Print Page | Close Window

Syntax for datediff and date range problems

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12856
Printed Date: 02 May 2024 at 4:59pm

Topic: Syntax for datediff and date range problems

Posted By: ultraca
Subject: Syntax for datediff and date range problems
Date Posted: 08 Apr 2011 at 8:38am

Crystal noob here, taken on more than I can chew...

We had a previous admin that put together a rather complicated report pulling data from sql, inserting into excel and using a bunch of formulas to create subreports.

The first criteria of the data is a list of all open tickets, their ticket number, the open date and the assigned user. That part is simple.

The second criteria for data is a list of closed tickets for the last 365 days.

The criteria of the report are the following:
1. For each ticket number show the number of days open.
   -I tried acheiving this by usind DateDiff in the Section Expert and inserting a new Detail, then using the x-2 (next to Supress) and the following formuala attempts:
       Attempt 1: DateDiff ("d", {open_date}, {close_Date})
       Attempt 2: Local DateTimeVar d1 := {cr.open_date};
           Local DateTimeVar d2 := {cr.close_date};
           DateDiff ("d", d1, d2)
   Both formulas tell me that a boolean is expected (not sure what to do)
   Could this be an issue of date format? Is using the x-2 the correct way to enter calculated results into my report?

2. Create a subreport showing the department which the ticket was generated from (this is in another table). I linked the two tables with the appropirate field without problem. For each department, show the assigned user then the amount of tickets still open within a certain range of days. 0-20, 21-50, 51-70 and so on and at the end show the total tickets. Like above, trying to get a date range formula to work is just not happening for me.

The part where I am really stuck is with the Crystal syntax... it seems to simple when using the formula editor but I just keep getting formula errors.

As and FYI here are the SQL queries that captured the data:
------------ Query 1 ----------------------
SELECT     ref_num, DATEADD(second, call_req."open_date", '12/31/1969 20:00:00') AS open_date,
                          (SELECT     userid
                            FROM          ca_contact
                            WHERE      (contact_uuid = call_req.assignee)) AS assignee
FROM         call_req
WHERE     (status = 'OP') AND (assignee <> 'NULL')
ORDER BY assignee DESC

------------ Query 2 ----------------------
SELECT c.name, a.userid
FROM ca_contact a
inner join call_req b on a.contact_uuid = b.assignee
left outer join ca_resource_department c on a.department=c.id
where b.status = 'OP' AND b.assignee is not null
group by a.userid, c.name
ORDER BY c.name, a.userid DESC

I can post the excel with final results if it helps clear things up.

Replies:

Posted By: mpcrob
Date Posted: 11 Apr 2011 at 4:50am

CR is expecting the number of days to consider as parameter:

DateDiff ("d", {open_date}, {close_Date}) < 365

For the second part I presume you want the different periods in one row for each department.

You have to create formula fields like:

Name: Open_20_0

if datediff("d", {OINV.DocDate}, CurrentDate) in 0 to 20 then 1 else 0

Name: Open_21_50

if datediff("d", {OINV.DocDate}, CurrentDate) in 21 to 50 then 1 else 0

Etc.

Put these fields in one row in the details section.

Group by Department.

Insert Summary Sum for these fields in the group footer.

Suppress the details section et voilá . . . . .

Posted By: ultraca
Date Posted: 11 Apr 2011 at 4:55am

Very much appreciated mpcrob! Might take me a while but I think I should be able to figure this out now :)

Posted By: ultraca
Date Posted: 11 Apr 2011 at 8:33am

argh...

So I think I confused myself using the Section Expert and tried adding a custom Formula Field from the Field Explorer (DateDiff ("d", {cr.open_date},{cr.close_date} ) < 365) then inserting it into the Report, however I just get blank results, no errors, nothing. My date/time format is "mm/dd/yyyy hh:mm:ss am/pm"

Does that format work with "d" type value for datediff?

EDIT:
Uh wait-a-minute... this formula does not compute for Open requests... what I need is the difference between open date and whatever the current date is.

So now I want this:
DateDiff ("d", {cr.open_date},CurrentDateTime ) < 365 but of course I get an error stating the field name is not known, which means the formula needs a field name, but the current table doesnt have a Current date & time value.

Does that mean I need to find that field in the DB or should I be looking into creating a custom one?

Sorry I am trying to talk this out to myself to figure it out...

EDIT 2:
So here is what I did to get the correct results (fyi using the < 365 was producing "true" as result)

DateDiff ("d", {cr.open_date}, CurrentDateTime)

Posted By: mpcrob
Date Posted: 11 Apr 2011 at 9:25am

This is an example of a query I made in our system, so the fieldnames are different:

http://www.justmypics4u.nl/OpenDays.jpg - http://www.justmypics4u.nl/OpenDays.jpg

So you can see where to create the formula fields and where to place them.

These are the results.

With details:

http://www.justmypics4u.nl/OpenDays_results_details.jpg - http://www.justmypics4u.nl/OpenDays_results_details.jpg

Details suppressed:

http://www.justmypics4u.nl/OpenDays_results_details_suppressed.jpg - http://www.justmypics4u.nl/OpenDays_results_details_suppressed.jpg

Does this help?

Edit:

The field CurrentDate is a standard field available in the CR formula editor:

http://www.justmypics4u.nl/CR_Formula_field.jpg - http://www.justmypics4u.nl/CR_Formula_field.jpg

Posted By: ultraca
Date Posted: 12 Apr 2011 at 3:26am

Definitely helps, but here is where I am stuck now.

I was able to create the second part of the report , group by department and show the 0-20 and 21-50 and so on but under the 0-20 etc I want to show the quantity of tickets per user instead of having a user repeated over many rows.

Here is what I currently have:

Here is what I want:

Posted By: mpcrob
Date Posted: 12 Apr 2011 at 3:46am

If you've added the summaries in the group section (I can't see if you did as that would require the design view), then it should be sufficient to hide the details section:

http://www.justmypics4u.nl/Suppress_Details.jpg - http://www.justmypics4u.nl/Suppress_Details.jpg

Posted By: ultraca
Date Posted: 13 Apr 2011 at 1:58am

mpcrob, your assistance has been invaluable!

Here is my final report:

Using if DateDiff ("d", {cr.open_date}, CurrentDateTime) in 0 to 20 then 1 else 0 for the date range queries and inserting summaries and supressing details I was able to complete this:

(there is a minor misconception in my 0-20 range title, although it says Count, it is really Sum - its just a naming issue because I initially chose Count then changed it to Sum later)

Posted By: mpcrob
Date Posted: 13 Apr 2011 at 4:23am

Glad I could be of help.

Are your Group totals correct now?

Because in the examples you show the totals seem to be off, and not in the 0-20 column only.

Posted By: ultraca
Date Posted: 13 Apr 2011 at 4:36am

It is now, the screenshot was from before I fixed the problem.

thanks!

Print Page | Close Window