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
精彩评论