账号 存取日期 借方 贷方 余额
A 2014-10-6 0 100000 100000
A 2014-10-9 20000 0 80000
A 2014-10-11 0 50000 130000
A 2014-11-7 20000 0 110000
A 2014-11-7 0 50000 160000
A 2014-11-15 50000 0 110000
10月份日均为=（9号-6号）*余额（100000）+（11号-9号）*余额（80000）+（31号（当期日期）-11号+1）*余额（130000）/31（当期天数）=102903.23
11月份日均为=（7号-1号）*余额（130000）+（15号-7号）*余额（160000）+（30号（当期日期）-15号+1）*余额（110000）/30=127333.33
------解决思路----------------------
WITH
/* 测试数据
table1(账号,存取日期,借方,贷方,余额)AS(
SELECT 'A','2014-10-06',0,100000,100000.0 UNION ALL
SELECT 'A','2014-10-09',20000,0,80000 UNION ALL
SELECT 'A','2014-10-11',0,50000,130000 UNION ALL
-- SELECT 'A','2014-11-07',20000,0,110000 UNION ALL 日期重复
SELECT 'A','2014-11-07',0,50000,160000 UNION ALL
SELECT 'A','2014-11-15',50000,0,110000
), */
b AS (
SELECT DISTINCT 账号
FROM table1
)
,c AS ( -- 日历
SELECT DateAdd(day,number,'2014-10-01') 日期
FROM master..spt_values
WHERE type = 'p'
AND number < 61
)
,d AS ( -- 每日余额
SELECT b.账号,
c.日期,
ISNULL(e.余额,0) 余额
FROM b
JOIN c
ON 1=1
OUTER APPLY (
SELECT TOP 1 *
FROM table1 t
WHERE t.账号 = b.账号
AND t.存取日期 <= c.日期
ORDER BY t.存取日期 DESC
) e
)
--SELECT * FROM d
SELECT MONTH(日期) 月份,
AVG(余额) 日均余额
FROM d
GROUP BY MONTH(日期)
月份 日均余额
----------- --------------------
10 102903.225806
11 127333.333333