Compare date/time difference
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=17645
Printed Date: 18 May 2024 at 7:23am
Topic: Compare date/time difference
Posted By: rlivermore
Subject: Compare date/time difference
Date Posted: 27 Sep 2012 at 7:37am
|
Running CR 10, SQL 2008 database.
Trying to get report to show our average response time on our ticketing system which is the time a ticket was opened and comparing it to the time the first log for the ticket is created.
So I'm needing to compare tblService.DateReceived + tblService.TimeReceived against tvwr_Time.StartDateTime.
The formula below is able to show how many days between them but I'm really looking for the number of days+minutes between them.
DateDiff('d',{tblServiceOrders.DateReceived},{tvwr_TimeTransactions.StartDateTime})
I'd appreciate any input on how I might be able to achieve this.
|
Replies:
Posted By: DBlank
Date Posted: 27 Sep 2012 at 7:54am
|
you have to use the datediff with minutes.
you can choose to display the total minutes in another way but you need to first get the value in the lowest level incrment that you want to see (minutes)
Also you cannot do mathmetical calculations (sum, average, etc) on the "days:minutes" display type so be aware of that when youa re ready to convert it.
DateDiff('n',{tblServiceOrders.DateReceived},{tvwr_TimeTransactions.StartDateTime})
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 8:18am
|
I understand, thank you! Since the tblService tables separates the Date and Time into 2 fields do you know what the formula would be to combine them and then do the comparison to the tvwr field?
Once I convert it to minutes how can I then have it display something more useful like 2590 minutes to 1 day 19.10 hours?
|
Posted By: DBlank
Date Posted: 27 Sep 2012 at 9:08am
|
I assume this is a SQL and both fields are datetime fields
date(tblservice.datefield) + time(tblservices.timefield)
YOu can create a new formual field and do this or
right click on the minutes result formula
select format field
select common tab
open the display string as formula
enter you formula here
something like
totext(floor(currentfieldvalue,1440)/1440,0,'') + ' days ' +
totext( (remainder(currentfieldvalue,1440) /60) ,2,'' ) + ' hours' |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 9:19am
|
Yes its a SQL 2008 DB. I tried the formula and it complains "the ) is missing" and it highlights the date(tblservice.datefield). The complete formula looks like this:
DateDiff('n',date(tblservice.datefield) + time(tblservices.timefield) ,{tvwr_time.startdatetime}) |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 9:24am
|
you will need to convert the
tblservice.datefield
and
tblservices.timefield
with the actual fields from your DB
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 9:36am
|
Ok that got thru my brain. So it's calculating the minutes correctly now, thank you!!!! So the last part is getting it spit out in easier to read format, like 2590 minutes to 1 day 19.10 hours or does this earlier reply address it? totext(floor(currentfieldvalue,1440)/1440,0,'') + ' days ' + |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 9:38am
|
yes, if you use it as a display formula
If you want to convert the value via another fomrula field you would have to repalce the currentfieldvalue with your other formula field name
|
Posted By: DBlank
Date Posted: 27 Sep 2012 at 9:39am
I did not thoroughly test it so you might want to verify the results 
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 9:46am
|
I followed your instructions and am getting "The ) is missing" and it highlights "floor". @DateTimeRecvd is the minutes formula I created.
totext(floor(@DateTimeRecvd,1440)/1440,0,'') + ' days ' +
totext( (remainder(@DateTimeRecvd,1440) /60) ,2,'' ) + ' hours ' |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 10:07am
|
You need curly brackets around fields (like formulas)
totext(floor( mailto:%7B@DateTimeRecvd,1440%7D%29/1440,0, - { @DateTimeRecvd} mailto:%7b@%29/1440,0, - )/1440,0,'' ) + ' days ' +
totext( (remainder( mailto:%7B@DateTimeRecvd,1440%7D%29/1440,0, - { @DateTimeRecvd},1440) /60) ,2,'' ) + ' hours ' |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 10:21am
|
I copied and pasted the formula in your reply and am getting the same error. So close I can almost taste it ) |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 10:26am
|
oops...I copied and pasted something and it messed up the format. try totext(floor( mailto:%7b@DateTimeRecvd%7d,1440%29/1440,0, - {@DateTimeRecvd},1440)/1440,0,'' ) + ' days ' +
totext( (remainder( mailto:%7b@DateTimeRecvd%7d,1440 - {@DateTimeRecvd},1440 ) /60) ,2,'' ) + ' hours' |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 10:30am
| It's complaining of a missing right bracket ")" at floors |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 10:43am
|
it is floor , not floors totext(floor( mailto:%7b@DateTimeRecvd%7d,1440%29/1440,0, - {@DateTimeRecvd},1440)/1440,0,'' ) + ' days ' + totext((remainder( mailto:%7b@DateTimeRecvd%7d,1440%29/60%29,2, - {@DateTimeRecvd},1440)/60),2,'' ) + ' hours'
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 10:48am
| Yes sorry it was typo on my reply not the actual formula. |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 10:51am
|
I do not get any error on the formula i posted.
try and copy and paste it again maybe?
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 10:55am
| Hmmm, copied and pasted and same issue. Is the formula version sensitive? I'm running CR 10.0 Pro. |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 11:02am
|
I thought floor was available in v10 but maybe is was added later...try using truncate instead totext(truncate( mailto:%7b@DateTimeRecvd%7d/1440%29,0, - {@DateTimeRecvd}/1440),0,'' ) + ' days ' + totext((remainder( mailto:%7b@DateTimeRecvd%7d,1440%29/60%29,2, - {@DateTimeRecvd},1440)/60),2,'' ) + ' hours'
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 11:10am
|
FYI, under math functions I do see truncate but not floor. I tried the truncate option and it's now complaining "a number or currency amount is required here" at @DateTimeRecvd
|
Posted By: DBlank
Date Posted: 27 Sep 2012 at 11:12am
| what is your datetimercvd formula? |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 11:15am
| date({tblService.DateReceived})+time({tblService.TimeReceived}) |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 11:30am
|
ahhh.
I assumed it was the total minutes formula uising the datediff.
what is that formula called?
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 11:39am
|
DateDiff and its formula is:
DateDiff('n',{@DateTimeRecvd},{tvwr_TimeTransactions.StartDateTime})
|
Posted By: DBlank
Date Posted: 27 Sep 2012 at 11:45am
| totext(truncate((DateDiff('n',{@DateTimeRecvd},{tvwr_TimeTransactions.StartDateTime}))/1440),0,'') + ' days ' + totext((remainder((DateDiff('n',{@DateTimeRecvd},{tvwr_TimeTransactions.StartDateTime})),1440)/60),2,'') + ' hours' |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 11:54am
| It's working, awesome work DBlank!!!! What about getting an average of for all of the @DateDiff records? |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 12:00pm
|
create your formula
DateDiff('n',{@DateTimeRecvd},{tvwr_TimeTransactions.StartDateTime})
then use the insert summary (Sigma button) and select average
place it in the group header or footer or report header or footer as desired
|
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 12:14pm
|
I tried that and like fully expected it to work but I got back something like -1,544,159.80
I placed the summary in the Report Footer.
|
Posted By: DBlank
Date Posted: 27 Sep 2012 at 1:24pm
| I think you reversed your dates in your minutes formula so you are getting a negative value. Try and reverse the date order |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 1:31pm
|
I appreciate you sticking with me on this...
I reversed them and am getting the same number without the minus. Here's the formula that I tried swapping the order:
DateDiff('n',{tvwr_TimeTransactions.StartDateTime},{@DateTimeRecvd})
|
Posted By: DBlank
Date Posted: 27 Sep 2012 at 1:39pm
|
You used average?
This is going to be in total minutes not the 'days minutes' format |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 1:39pm
| I think it is set to sum, not average |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 1:50pm
| It's set to average. |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 1:57pm
| Look at each row and see if the data is correct per row then see if there are some really high values that are setting your average off. |
Posted By: rlivermore
Date Posted: 27 Sep 2012 at 2:04pm
| Not sure what constitutes a high number but some of the tickets have been open for more than 20 days and some of them have negative dates/times as the work was completed before the ticket was opened. |
Posted By: DBlank
Date Posted: 27 Sep 2012 at 2:08pm
|
20 days would seem to do it.
If it is not what you want to see then you will have to rethink how or what you want to calculate. |
Posted By: rlivermore
Date Posted: 28 Sep 2012 at 5:16am
| Ok not sure where to go from here but I appreciate all of your help! |