January 9th, 2013, 06:02 AM
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 1
Time spent in forums: 4 m 53 sec
Reputation Power: 0
|
|
|
Convert time to float/integer
Hello,
I'm using an SQL query within a VBA application that accesses a database. Specifically, I'm building a list of employees (contact), total hours this month, and total hours last month.
I've done some reading and compiled the following query ('tmStart','TMEnd','LMStart' and 'LMEnd' are all date variables).
Code:
SELECT TM.contact, TM_duration, LM_duration
FROM (SELECT C1.contact, sum(CH.duration) AS TM_duration
FROM contact1 AS C1
INNER JOIN conthist AS CH
ON C1.accountno = CH.accountno
WHERE CH.resultcode = 'AS'
AND C1.key3 = '8.1n Training'
AND CH.ondate BETWEEN #tmStart# AND #TMEnd#
GROUP BY C1.contact) AS TM
INNER JOIN (SELECT C1.contact, sum(CH.duration) AS LM_duration
FROM contact1 AS C1
INNER JOIN conthist AS CH
ON C1.accountno = CH.accountno
WHERE C1.key3 = '8.1n Training'
AND CH.ondate BETWEEN #LMStart# AND #LMEnd#
GROUP BY C1.contact) AS LM
ON TM.contact = LM.contact
The problem is that the CH.duration field stores times as hh:mm:ss but as a string (according to the documentation for our CRM system - Goldmine 6.7), which is incompatible with the SUM() function. When I've tried using a CONVERT() function, I get an error.
Does anyone have a solution or some helpful advice on this one?
Thanks
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
