CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Jonathan DeMarks
Jonathan DeMarks

Reputation: 2441

How do I get a proper weighted average date in SQL

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.

Upvotes: 2

Views: 3609

Answers (1)

Quassnoi
Quassnoi

Reputation: 425391

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

Upvotes: 3

Related Questions