How do I get a proper weighted average date in SQL

Question

I use this query to get my average date:

CONVERT(DATETIME, AVG(CONVERT(FLOAT, ChargeOffDate))) as [Average C/O Date]

Which correctly give me the date '2003-08-12 14:17:22.103'.

When I use this query to get the weighted average date

CONVERT(DATETIME, SUM(CONVERT(FLOAT, ChargeOffDate) * FaceValue) / SUM(FaceValue))

I get a date like '1938-10-19 21:28:48.000' which is way off from the average date. I feel like this is a simple thing and I'm missing something critical but small.

Example:

declare @temp table (value datetime, [weight] money)
insert into @temp values (NULL,8850.00)
insert into @temp values (NULL,137.91)
insert into @temp values ('2006-01-15',221.13)
insert into @temp values ('2006-10-15',127.40)
insert into @temp values ('2001-07-31',551.44)
insert into @temp values ('1997-10-12',4963.41)
insert into @temp values ('2006-03-15',130.36)
insert into @temp values ('2005-01-07',1306.31)

SELECT
    CONVERT(DATETIME, AVG(CONVERT(FLOAT, value))) as [Avg Date],
    CONVERT(DATETIME, SUM(CONVERT(FLOAT, value) * [weight]) / SUM([weight])) as [Weighted Avg Date]
FROM @temp

This gives '2003-11-25 20:00:00.000' as an Average Date and '1944-10-13 10:52:10.573' as a weighted average. Without the nulls, it gives '1999-12-02 17:10:51.087'. It's possible my problem is the nulls are throwing off my calculations.

Answer 1

Most probably, some of your ChargeOffDate's are NULL, but their FaceValues are not.

These records do not contribute into the first sum but do contribute into the second one.

WITH    q (ChargeOffDate, FaceValue) AS
        (
        SELECT  CAST('20030101' AS DATETIME), 1.0
        UNION ALL
        SELECT  CAST('20030201' AS DATETIME), 2.0
        UNION ALL
        SELECT  CAST('20030301' AS DATETIME), 3.0
        UNION ALL
        SELECT  NULL, 4.0
        )
SELECT  CONVERT(DATETIME, SUM(CONVERT(FLOAT, ChargeOffDate) * FaceValue) / SUM(FaceValue))
FROM    q

----
1961-11-12 21:36:00.000

Use this:

WITH    q (ChargeOffDate, FaceValue) AS
        (
        SELECT  CAST('20030101' AS DATETIME), 1.0
        UNION ALL
        SELECT  CAST('20030201' AS DATETIME), 2.0
        UNION ALL
        SELECT  CAST('20030301' AS DATETIME), 3.0
        UNION ALL
        SELECT  NULL, 4.0
        )
SELECT  CONVERT(DATETIME, SUM(CONVERT(FLOAT, ChargeOffDate) * FaceValue) / SUM(FaceValue))
FROM    q
WHERE   ChargeOffDate IS NOT NULL

----
2003-02-09 20:00:00.000