Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Compare date/time difference Post Reply Post New Topic
Page  of 4 Next >>
Author Message
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Topic: Compare date/time difference
    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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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})


Edited by DBlank - 27 Sep 2012 at 7:56am
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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'
 
 
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet 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})
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet 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 ' +
totext(
        (remainder(currentfieldvalue,1440)
         /60)
    ,2,''   
    ) + ' hours'

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Sep 2012 at 9:39am
I did not thoroughly test it so you might want to verify the results Ermm
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet 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 '
IP IP Logged
Page  of 4 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.