Compare date/time difference
Printed From: Crystal Reports Book — Forum Name: Technical Questions
Posted By: rlivermore — 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.
Posted By: DBlank — 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}) Edited by DBlank - 27 Sep 2012 at 7:56am
Posted By: rlivermore — 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 — 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 — 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 — 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 — 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 ' +
totext(
(remainder(currentfieldvalue,1440)
/60)
,2,''
) + ' hours'
Posted By: DBlank — 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 — 27 Sep 2012 at 9:39am
I did not thoroughly test it so you might want to verify the results 
Posted By: rlivermore — 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 — 27 Sep 2012 at 10:07am
You need curly brackets around fields (like formulas)
totext(floor( {@DateTimeRecvd })/1440,0,'') + ' days ' + totext( (remainder( {@DateTimeRecvd },1440) /60) ,2,'' ) + ' hours '
Posted By: rlivermore — 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 — 27 Sep 2012 at 10:26am
oops...I copied and pasted something and it messed up the format. try
Posted By: rlivermore — 27 Sep 2012 at 10:30am
It's complaining of a missing right bracket ")" at floors
Posted By: DBlank — 27 Sep 2012 at 10:43am
it is floor , not floors
Posted By: rlivermore — 27 Sep 2012 at 10:48am
Yes sorry it was typo on my reply not the actual formula.
Posted By: DBlank — 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 — 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 — 27 Sep 2012 at 11:02am
I thought floor was available in v10 but maybe is was added later...try using truncate instead
Edited by DBlank - 27 Sep 2012 at 11:02am
Posted By: rlivermore — 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 — 27 Sep 2012 at 11:12am
what is your datetimercvd formula?
Posted By: rlivermore — 27 Sep 2012 at 11:15am
date({tblService.DateReceived})+time({tblService.TimeReceived})
Posted By: DBlank — 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 — 27 Sep 2012 at 11:39am
DateDiff and its formula is:
DateDiff('n',{@DateTimeRecvd},{tvwr_TimeTransactions.StartDateTime})
Posted By: DBlank — 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 — 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 — 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 — 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 — 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 — 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 — 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 — 27 Sep 2012 at 1:39pm
I think it is set to sum, not average
Posted By: rlivermore — 27 Sep 2012 at 1:50pm
It's set to average.
Posted By: DBlank — 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 — 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 — 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 — 28 Sep 2012 at 5:16am
Ok not sure where to go from here but I appreciate all of your help!
|
