Calculations using datetime fields

[jf111]

Hi,

I have a Crystal Report which uses SQL to return some data about swipe in/out times.

My intention is to sum the time difference column in order to see how many hours someone worked over a given time period. However, I'm unable to do this because Crystal won't sum datetime fields (I understand this..). I know that I need to convert the field into seconds or minutes, do the sum, then turn it back into hh:mm for display purposes.

I can't get it to work. I have tried bringing the time difference in as a datetime and a string (see the timediff/timediff2 columns) but at this point I'm a bit stuck. Whatever I do I either get errors that my datatype is incorrect or have no option to sum the fields because it remains as a datetime field. Or I try a formula to sum and only get one value for all groups rather than having separate ones (per department, then per person would be ideal).

Please help!

SQL query below. I've tried so many Crystal formulas at this point that I don't know which ones worked/didn't work!

select 
 CONVERT(varchar(11),[6007EventTime]) as Date
 ,[6000UserName]
 ,min([6007EventTime]) as EntryTime
 ,max([6007EventTime]) as ExitTime
 ,departmentname
,CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, min([6007EventTime]), max([6007EventTime])), 0), 114) AS TimeDiff
,CAST((max([6007EventTime])-min([6007EventTime])) as datetime) AS TimeDiff2
from vw_reportquery rq
join vw_ViewUserDetails ud on ud.UserID = rq.UserID
where ud.DepartmentName like '%Commercial%' and ud.DepartmentName like '%Real%' and ud.DepartmentName like '%admin%'
or [6007EventTime] between {?Start} and {?End}
group by CONVERT(varchar(11),[6007EventTime]),departmentname,[6000UserName]
order by departmentname

Sample database output from this query

Date	        6000UserName	EntryTime	        ExitTime	        departmentname	TimeDiff	(No column name)	(No column name)
Oct  2 2015	smith, john	2015-10-02 08:06:48.000	2015-10-02 16:42:33.000	Real Estate	08:35:45:000	1900-01-01 08:35:45.000	1900-01-01 08:35:45.000
Oct  2 2015	rabbit, roger	2015-10-02 08:56:27.000	2015-10-02 18:28:20.000	Real Estate	09:31:53:000	1900-01-01 09:31:53.000	1900-01-01 09:31:53.000
Oct  2 2015	gellar, ross	2015-10-02 09:55:42.000	2015-10-02 18:09:10.000	Real Estate	08:13:28:000	1900-01-01 08:13:28.000	1900-01-01 08:13:28.000
Oct  2 2015	smith, john	2015-10-02 08:15:56.000	2015-10-02 17:02:29.000	Real Estate	08:46:33:000	1900-01-01 08:46:33.000	1900-01-01 08:46:33.000
Oct  2 2015	Jones, Graham	2015-10-02 08:47:08.000	2015-10-02 17:49:25.000	Real Estate	09:02:17:000	1900-01-01 09:02:17.000	1900-01-01 09:02:17.000
Oct  2 2015	green, rachel	2015-10-02 08:42:00.000	2015-10-02 17:33:23.000	Real Estate	08:51:23:000	1900-01-01 08:51:23.000	1900-01-01 08:51:23.000
Oct  2 2015	gellar, monica	2015-10-02 09:04:01.000	2015-10-02 17:30:58.000	Real Estate	08:26:57:000	1900-01-01 08:26:57.000	1900-01-01 08:26:57.000
Oct  2 2015	bing, chandler	2015-10-02 08:31:44.000	2015-10-02 18:01:16.000	Real Estate	09:29:32:000	1900-01-01 09:29:32.000	1900-01-01 09:29:32.000

 

[CrystalizeCanada]

One Approach

Create a formula in Crystal Reports called TimeDiff
//PURPOSE OF FORMULA: Calculate the difference between two date-time fields (result is a decimal)
{MyTable.ExitTime} - {MyTable.EntryTime}

This will give you decimal results like 0.36, 0.4, 0.34 etc

Sum up the formula. You can sum this up by group if you have a group. (in the example below I have a grand total instead of a sub-total)
Create another formula that converts that decimal total to days hours minutes seconds

//PURPOSE OF FORMULA: Turn a decimal e.g. 2.9 into days hours minutes seconds
//VARIABLES:
numberVar DaysDecimal := Sum ({@TimeDiff}); //e.g. 2.9 days
numberVar CompleteDays := Int(DaysDecimal); //e.g. Int(2.9) = 2 days
numberVar HoursDecimal := DaysDecimal - CompleteDays; //2.9 - 2 = 0.9 days
numberVar CompleteHours := Int(HoursDecimal * 24); //Int(0.9 * 24) = 22 hours
numberVar MinutesDecimal := HoursDecimal * 24 - CompleteHours; //22.96 - 22 = 0.96 hours
numberVar CompleteMinutes := Int(MinutesDecimal * 60); //Int(0.96 * 60) = 57 minutes
numberVar SecondsDecimal := MinutesDecimal * 60 - CompleteMinutes; //57.80 - 57 = 0.8 minutes
numberVar CompleteSeconds := Int(SecondsDecimal * 60); //0.8 * 60 = 48 seconds
//FORMULA RESULT:
ToText(CompleteDays,0) & ' days ' &
ToText(CompleteHours, 0) & ' hours ' &
ToText(CompleteMinutes,0) & ' mins ' &
ToText(CompleteSeconds,0) & ' seconds'
//Or display the time this way
//ToText(CompleteHours, "00") & ':' &
//ToText(CompleteMinutes,"00") & ':' &
//ToText(CompleteSeconds,"00") & ''





Gordon BOCP
Crystalize

 

[CrystalizeCanada]

Here is another simpler version of the second formula.

//PURPOSE OF FORMULA: Turn a decimal e.g. 2.9568 into days hours minutes seconds
//FORMULA RESULT:
ToText(Int(Sum ({@TimeDiff})),0) & ' days ' &
ToText(DateTimevalue(Sum ({@TimeDiff})), "HH:mm:ss")

This gives us the result:
2 days 22:57:48

Gordon BOCP
Crystalize

 

[jf111]

Hi,

Thanks for your help so far.

I've tried this out. The first formula works in terms of bringing back a decimal of the time difference between the two dates. I am able to insert a sum of this and it appears correct. However, I can't get the 2nd formula to work as expected. Either option just puts the value 462 days 18 hours 36mins 44seconds into my report.

Am I missing something in terms of where I insert each formula and how I add the total in for each group?

My desired output is something along these lines, hopefully this helps to see what I'm trying to do?

+-------+---------------------+------------------------+------+--------+
| Name  | EntryTime           | ExitTime               | Dept | Hours  |  
+-------+---------------------+------------------------+------+--------+
| Name1 | 05/04/2016,08:13:32 | 05/04/2016 16:56:18    | HR   | 8:42   |  
+-------+---------------------+------------------------+------+--------+
| Name1 | 04/04/2016 07:54:46 | 04/04/2016 17:38:02    | HR   | 9:43   |  
+-------+---------------------+------------------------+------+--------+
|       |                     |                        |      |        |  
+-------+---------------------+------------------------+------+--------+
|       |                     | Total Hours Employee   |      | 17.85  |  
+-------+---------------------+------------------------+------+--------+
|       |                     | Total Hours Department |      | 100.53 |  
+-------+---------------------+------------------------+------+--------+

Thanks

 

[pmax9999]

I did it this way:

Create the following formula to calculate the seconds between Entry and Exit time. Place it in the detail section;
[Code {@Seconds}]
DATEDIFF('s', {Table.EntryTime}, {Table.ExitTime})
[/Code]

Create the following in the Report Footer:

WHILEPRINTINGRECORDS;
Local NumberVar TOTAL := SUM({@Seconds});
Local NumberVar HRS   := INT(TOTAL/3600);
Local NumberVar MIN   := INT((TOTAL - (HRS*3600))/60);
Local NumberVar SEC   := TOTAL - (HRS*3600) - (MIN*60);

ToText(HRS, '#') + ':' + ToText(MIN, '#') + ':' + ToText(SEC, '0#')

If your report includes groups on Department and/or Person, you could create additional formulas based on the second, but where the TOTAL variable is the sum for the group, eg SUM({@Seconds}, {Table.Department})

Hope this helps.

Cheers
Pete

 

[jf111]

Pete - thank you that's great!

I've managed to sum the relevant fields now and converted it from seconds using this formula.

WhilePrintingRecords;
Local NumberVar seconds := {@Seconds};

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#') + ' minute(s)'