Moved Data to SQL Server now AVG datefield queries not working

[mcaramuta]

I have searched and searched for the answer and I am certain it has been asked many times before. Unfortunately I cannot find it here and the answers I found doing a web search were not comprehensible to me due to my inexperience.

I have a fairly robust database in Access 07. I recently moved the data to MS SQL Server 05 using Access as a front.

Most of my queries work. However, queries that try to average dates are not supported in SQL Server and are not working. For example.

SELECT [Closed Loans Mainsail].Branch, Avg([A INVESTOR SETTLEMENT TABLE].[Purchase Date]-[Closed Loans Mainsail].[Closing Date]) AS [Avg Days from Close to Investor Purchase]
FROM [Closed Loans Mainsail] INNER JOIN [A INVESTOR SETTLEMENT TABLE] ON [Closed Loans Mainsail].[Loan Number] = [A INVESTOR SETTLEMENT TABLE].[Loan Number]
GROUP BY [Closed Loans Mainsail].Branch;


This worked great until data was moved into SQL Server. Now I receive error #8117 Datatype datetime is invalid for sum operator.

I'm sure there is a common solution you more experienced folks are aware of.

Would anyone care to help? I have to present this information to our executive committee on Monday and now I can't.

Thank you very much.
Marc


(One side note. Some of the datetime queries are working fine. This is very perplexing as I cannot figure out why some work and others don't. Here is an example of one that works.

SELECT [A ENCOMPASS DATA].Branch, Avg((([Closed Loans JV].[Closing Date])-([A ENCOMPASS DATA].[File Started]))) AS [Avg Days From Lead To Close]
FROM [A ENCOMPASS DATA] INNER JOIN [Closed Loans JV] ON [A ENCOMPASS DATA].[Loan Number] = [Closed Loans JV].[Loan Number]
WHERE (((([A ENCOMPASS DATA].[Branch])>"209" And ([A ENCOMPASS DATA].[Branch])<"228")=False))
GROUP BY [A ENCOMPASS DATA].Branch;

It is basically the same as the one that does not work.

 

[jwhite]

The only difference I see is the number of (). Made to match the one that works:

Avg((([A INVESTOR SETTLEMENT TABLE].[Purchase Date])-([Closed Loans Mainsail].[Closing Date]))) AS [Avg Days from Close to Investor Purchase]

If the first Date is 02/10/08 and the second Date is 02/26/08, what is supposed to be the correct answer?

 

[mcaramuta]

Thank you. It seems to be more than the commas. The one below has all the commas and does not work. Worked fine before migrating to SQL.


SELECT [A ENCOMPASS DATA].Branch, Avg((([A LYDIAN DATA].[File received Date])-([A ENCOMPASS DATA].[File Started]))) AS [Avg Days From Lead To Loan]
FROM [A ENCOMPASS DATA] INNER JOIN [A LYDIAN DATA] ON [A ENCOMPASS DATA].[Loan Number] = [A LYDIAN DATA].[Loan Number]
WHERE (((([A ENCOMPASS DATA].[Branch])>"209" And ([A ENCOMPASS DATA].[Branch])="228")=False))
GROUP BY [A ENCOMPASS DATA].Branch;

 

[jwhite]

Perhaps overlooked, but what would be the answer to the example dates I gave?

Perhaps some of the field values for [File received Date] and/or [File Started] are not proper date formats, or are Null?

 

[mcaramuta]

FDCUSA Thank you. Sorry I overlooked that question. I apologize as I will not do the math here either, but only because I closed in on a solution that touches on what you commented about NULL.

DAY: Avg(CDbl([A LYDIAN DATA].[File received Date])-CDbl([A ENCOMPASS DATA].[File Started]))

CDbl - converts the dates to FLOAT and then allows me to run calculations on them.

But now I have a new issue - When [File Received Date] has a value, but [File Started date] does not CDbl throws an "#ERROR". This keeps the AVG function from working. In pure ACCESS calculations when one of the fields was missing a NULL was created. This allowed AVG to work. In SQL I receive #ERROR.

Do you know any way to have ERROR be NULL?

And....many many thanks for your assistance.

 

[mcaramuta]

FDCUSA - Thank you for pointing me in the right direction. Once I started using CDbl I was on the right track, BUT the second issue was indeed the NULL problem. Bad table data!. There should have been no NULL values in File Received Date. Once I eliminated the bad data the code worked beautifully. Here is the final code if anyone is interested. This AVG date issue is a problem and the other solutions I have seen are more complicated. This is an easy one.

SELECT [A ENCOMPASS DATA].Branch, Avg(CDbl([A LYDIAN DATA].[File received Date])-CDbl([A ENCOMPASS DATA].[File Started])) AS [AVG Days Lead to Loan]
FROM [A ENCOMPASS DATA] INNER JOIN [A LYDIAN DATA] ON [A ENCOMPASS DATA].[Loan Number] = [A LYDIAN DATA].[Loan Number]
WHERE ((Not (([A ENCOMPASS DATA].Branch)>"209" And ([A ENCOMPASS DATA].Branch)<"228")) AND (([A LYDIAN DATA].[File Received Date]) Is Not Null))
GROUP BY [A ENCOMPASS DATA].Branch;

Again, Many Thanks

 

[jwhite]

Good job working through it! Sometimes when we are programming, we overlook that the very problem could simply be the data we are attempting to work with.

 

[mcaramuta]

You're right about that. Funny thing is I know how to solve the problem is SQL Server by using CAST instead of CDbl. I wasted a few hours trying to figure out why I could not get CAST to work. Only then did I realize that I was using CAST in an ACCESS query...thinking I was in SQL Server. It was a long night and I was tired. I can't believe I was in Access and thinking I was in SQL Server. Duhhhhh. Sometimes you have to walk away....sleep....and then try again in the morning.