开发者

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 i开发者_JAVA百科nto @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.


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜