Sum of time diff

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

HTML Code:

---

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. :confused:

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

Code:

---

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

---

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

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.

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

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.