views:

791

answers:

2

I have the following query in MSSQL:

select 
TRANSACTION_TYPE_ID
,COUNT(TRANSACTION_TYPE_ID)AS NUMBER_OF_TRANSACTIONS
,CAST(SUM(AMOUNT)AS DECIMAL (30,2)) AS TOTAL
FROM 
    [ONLINE_TRANSACTION] 
WHERE CONVERT(CHAR(8), CREATED_ON, 114) >='17:30' AND AMOUNT IS NOT NULL AND     
TRANSACTION_TYPE_ID !='CHEQUE-STOP-TRANS-TYPE' 
GROUP BY TRANSACTION_TYPE_ID
ORDER BY TRANSACTION_TYPE_ID

I want to show the type of transactions TRANSATION_TYPE_ID as above the total amount of each type of transaction as above BUT also the average time these transactions occurred CREATED_ON which is datetime I still have not find a good way of doing this?

+1  A: 

One way would be to convert the time to seconds, calculate the average, and then convert it back to hours, minutes and seconds for the result.

Randolph Potter
good idea, i am sort of looking for some source code pointers thanks Randolph
Andreas
+2  A: 

Based on Randolph Potter's answer, you can find the average time like:

avg(DATEPART(hh,created_on)*60 + DATEPART(mi,created_on)) % 24 as AvgHour,
avg(DATEPART(hh,created_on)*60 + DATEPART(mi,created_on)) / 24 as AvgMinute
Andomar
i found this solution aswell--> (stackoverflow.com/questions/529144/…) CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(created_on, 12) as datetime) AS FLOAT)) AS DATETIME), 114) + '0'AS AVARAGE_TIME Implementing both Andomars reply and the one mentioned above i get different results.which is the correct one?
Andreas
I assumed created_on was a datetime column, but then left(created_on,12) would give a syntax error. Please edit your question and add the data type of the column, with some example rows.
Andomar
This is incorrect.
Jeremy Roberts