| Author |
Message |
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 27 Sep 2012 at 9:39am |
I did not thoroughly test it so you might want to verify the results 
|
|
IP Logged |
|
rlivermore
Groupie
Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
|
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 Logged |
|
|
|
