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!
Sample database output from this query
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!
SQL:
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
SQL:
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