Sum of time diff

[rxv25]

Hi Experts,

I am trying to write a query which will calculate the time difference and then sum the total , and the result of it will be used again. I am new to SQL and VB6, so I very much apprreciate your help

Here is my query

strSQL = "SELECT Verifier, Sum(TOTALCLAIMS) as TotalDocs, sum(NUMKEYSTROKES)as TotalKS, sum(DATEDIFF('M',ENDTIME,STARTTIME)) as VERIFYTIME, (TotalKS/VERIFYTIME)*60 as KSHR, (KSHR/KSPerDoc) as DocsPerHour, (" & Norm & "/KSHR)*DocsPerHour as NormalizedDocsHR, (TotalKS/KSHR) as Hours, (Hours*60) as Minutes FROM VERIFY WHERE (VFYDATE Between #" & Format(DTPicker1.Value, "MM/DD/YYYY") & "# And #" & Format(DTPicker2.Value, "MM/DD/YYYY") & "#) Group by VERIFYTIME,Verifier"

I am using access DB but my production DB is SQL Server. So I need queries to run in both access and SQL server.

My query was working fin until I added "sum(DATEDIFF('M',ENDTIME,STARTTIME)) as VERIFYTIME, (TotalKS/VERIFYTIME)*60 as KSHR" Now I am getting a run time error , too few paramers expected 1. I verified the column names and its not misspelled anywhere in the query.

[GaryMazzone]

This: VERIFYTIME is not an actual field in the tables. Therefore it can not be used in this way, you will need to duplicate the way you generate that there

sum(DATEDIFF('M',ENDTIME,STARTTIME)) as VERIFYTIME, (TotalKS/sum(DATEDIFF('M',ENDTIME,STARTTIME)))*60 as KSHR

[rxv25]

Thanks Gary, I tried what you said but still getting the same error.

[GaryMazzone]

VERIFYTIME cant be in the group by since is it once again not a column and it is a sum therfore it needs to be in the group not controling the group.

[rxv25]

Its now returning '0' as result for all columns, I know there is data available for the time period I am running this SQL

[rxv25]

I changed DATEDIFF minutes to seconds and it started working fine as the difference between start and end time was very low. It was my bad. Thanks Gary.