Hi.
I have to find the average between two date fields, realizing I have NULL values See example below:
SELECT AVG(DATEDIFF(DAY,DATEvalue1, DATE_value2))
FROM TABLE2
NULL VALUES AT DATE_VALUE2.
Hi.
I have to find the average between two date fields, realizing I have NULL values See example below:
SELECT AVG(DATEDIFF(DAY,DATEvalue1, DATE_value2))
FROM TABLE2
NULL VALUES AT DATE_VALUE2.
youd need to protect against null.
either only query on non-nulls
or
build a big function to test for null in both fields to prevent errors.
Examples? Please?
Create a query/view "SELECT * FROM TABLE2 WHERE DATEvalue1 IS NOT NULL AND DATEvalue_2 IS NOT NULL;" save it as qryValidDates
Now us your formula SELECT AVG(DATEDIFF(DAY,DATEvalue1, DATE_value2)) FROM qryValidDates
Cheers,
Vlad
Thanks sir.
It worked. One last item. My user would like the data for this past year. How would that look?
Let's still use my example:
SELECT AVG(DATEDIFF(DAY,DATEvalue1, DATE_value2))
FROM TABLE2
Thanks.
If you want to restrict datevalue1 to last year you can modify the first query as follows:
"SELECT * FROM TABLE2 WHERE DATEvalue1 BETWEEN #1/1/2017# AND #12/31/2017# AND DATEvalue_2 IS NOT NULL;"
Cheers,
Vlad
Hi Vlad, would I still be able to use the AVG function with your response above?
perhaps:
"SELECT * FROM TABLE2 WHERE AVG(DATEVALUE_1 BETWEEN #1/1/2017# AND #12/31/2017# AND DATEVALUE_2 IS NOT NULL);"
not really, I think what you want is SELECT AVG(DateDiff(DateValue1,DateValue2) FROM Table2 Where DateValue1 BETWEEN #1/1/2017# AND #12/31/2017# AND DATEVALUE_2 IS NOT NULL;
Cheers,
Vlad
When datevalue1 is NULL, you can substitute it with the current date, if this is acceptable you can make the average over all records, NULL or not with the following SQL statement (assuming you want the number of days between the 2 dates, otherwise substitute the d argument with the correct interval)
select avg(datediff(d,coalesce(datevalue1,getdate()),coalesce(datevalue2,getdate())))as DaysDiffer from Tablename
This worked, slow feedback sorry.