不用循环,你试试 select insert_time,organ_id,count(a.policy_id)accept_num from tmep_dwf_accept_policy a where a.DEPT_id='104'and a.sell_channel=1 group by convert(date,a.insert_time,121),convert(121,a.accept_date,121)
DECLARE @date_id datetime SET @date_id = '2012-09-05';WITH tmep_dwf_accept_policy(insert_time,dept_id,product_id,actual_date) AS ( SELECT '2012-09-01','116',100787385,'2012-09-05' UNION ALL SELECT '2012-09-01','116',100787385,'2012-09-04' UNION ALL SELECT '2012-09-01','116',100792447,'2012-09-04' UNION ALL SELECT '2012-09-01','116 ',100792496,'2012-09-03' UNION ALL SELECT '2012-09-01','116 ',100795237,'2012-09-01' UNION ALL SELECT '2012-09-01','116 ',100796428,'2012-09-01' UNION ALL SELECT '2012-09-01','116 ',100798639,'2012-09-03' UNION ALL SELECT '2012-09-01','116 ',100800880,'2012-09-03' UNION ALL SELECT '2012-09-01','116 ',100795980, null ) ,t1 AS ( SELECT insert_time, dept_id, COUNT(*) 预收, MIN(actual_date) first_date FROM tmep_dwf_accept_policy WHERE actual_date <= @date_id OR actual_date IS NULL GROUP BY insert_time,dept_id ) ,t2 AS ( SELECT t1.insert_time, t1.dept_id, n.number+1 days, t1.预收, DateAdd(day,n.number,t1.first_date) actual_date FROM t1 JOIN master..spt_values n ON n.type = 'p' AND n.number <= DateDiff(day,first_date,@date_id) ) ,t3 AS ( SELECT insert_time, dept_id, actual_date, COUNT(*) 实收 FROM tmep_dwf_accept_policy WHERE actual_date <= @date_id GROUP BY insert_time,dept_id,actual_date ) SELECT @date_id date_id, t2.dept_id, t2.days, t2.预收, t4.实收 FROM t2 CROSS APPLY (SELECT SUM(实收) 实收 FROM t3 WHERE t3.insert_time = t2.insert_time AND t3.dept_id = t2.dept_id AND t3.actual_date <= t2.actual_date ) t4 ORDER BY t2.insert_time, t2.dept_id, t2.actual_date DESC date_id dept_id days 预收 实收 ----------------------- ------- ----------- ----------- ----------- 2012-09-05 00:00:00.000 116 5 9 8 2012-09-05 00:00:00.000 116 4 9 7 2012-09-05 00:00:00.000 116 3 9 5 2012-09-05 00:00:00.000 116 2 9 2 2012-09-05 00:00:00.000 116 1 9 2
谢谢 不过我这样写想改成循环或动态可以吗 select date_id, dept_id, '5日' days,count(a.product_id) '预收', count(a.product_id)实收 from t_a as a left join t_a as b on a.dept_id=b.dept_id and actual_date<=pt_endtime-1 group by date_id,dept_id union all select date_id, dept_id, '4日' days,count(a.product_id) '预收', count(a.product_id)实收 from t_a as a left join t_a as b on and a.dept_id=b.dept_id and actual_date<=pt_endtime-2 group by date_id,dept_id
select date_id, dept_id, count(a.product_id) '预收', count(b.product_id)'5日实收',count(c.product_id)'4日实收' from t_a as a left join t_a as b on a.dept_id=b.dept_id and actual_date<=pt_endtime-1 left join t_a as c on and a.dept_id=c.dept_id and actual_date<=pt_endtime-2 group by insert_time date_id,dept_id 这样怎么改成循环呢
select insert_time,organ_id,count(a.policy_id)accept_num
from tmep_dwf_accept_policy a
where a.DEPT_id='104'and a.sell_channel=1
group by convert(date,a.insert_time,121),convert(121,a.accept_date,121)
date_id dept_id 预收 5日实收 4日 3日 2日 1日
'2012-09-06','104' ,19, 10, 10, 6, 3 , 3
'2012-09-06','104' ,6, 2, 2 ,2, 2, 0
'
'2012-09-01','104',100787385,'2012-09-04'
'2012-09-01','104',100792447,'2012-09-04'
'2012-09-01','116 ',100792496,'2012-09-03'
'2012-09-01','116 ',100795237,'2012-09-01'
'2012-09-01','116 ',100796428,'2012-09-01'
'2012-09-01','116 ',100798639,'2012-09-03'
'2012-09-01','116 ',100800880,'2012-09-03'
'2012-09-01','116 ',100795980, null
期望结果 date_id 是参数结束时间
date_id dept_id days 预收 实收
2012-9-10 116 5日 9 7 (9.1-9.5)
2012-9-10 116 4日 9 7 (9.1-9.4)
2012-9-10 116 3日 9 5
2012-9-10 118 2日 9 2
2012-9-10 118 1日 9 2
预收:总数只有8条?
dept_id:104 怎么变成 118 了?
没有5日的记录。
'2012-09-01','116',100787385,'2012-09-05'
'2012-09-01','116',100787385,'2012-09-04'
'2012-09-01','116',100792447,'2012-09-04'
'2012-09-01','116 ',100792496,'2012-09-03'
'2012-09-01','116 ',100795237,'2012-09-01'
'2012-09-01','116 ',100796428,'2012-09-01'
'2012-09-01','116 ',100798639,'2012-09-03'
'2012-09-01','116 ',100800880,'2012-09-03'
'2012-09-01','116 ',100795980, null
期望结果 date_id 是参数结束时间 这样的
date_id dept_id days 预收 实收
2012-9-5 116 5日 9 8 (9.1-9.5)
2012-9-5 116 4日 9 7 (9.1-9.4)
2012-9-5 116 3日 9 5 (9.1-9.3)
2012-9-5 118 2日 9 2 (9.1-9.2)
2012-9-5 118 1日 9 2 9.1
SET @date_id = '2012-09-05';WITH tmep_dwf_accept_policy(insert_time,dept_id,product_id,actual_date) AS (
SELECT '2012-09-01','116',100787385,'2012-09-05' UNION ALL
SELECT '2012-09-01','116',100787385,'2012-09-04' UNION ALL
SELECT '2012-09-01','116',100792447,'2012-09-04' UNION ALL
SELECT '2012-09-01','116 ',100792496,'2012-09-03' UNION ALL
SELECT '2012-09-01','116 ',100795237,'2012-09-01' UNION ALL
SELECT '2012-09-01','116 ',100796428,'2012-09-01' UNION ALL
SELECT '2012-09-01','116 ',100798639,'2012-09-03' UNION ALL
SELECT '2012-09-01','116 ',100800880,'2012-09-03' UNION ALL
SELECT '2012-09-01','116 ',100795980, null
)
,t1 AS (
SELECT insert_time,
dept_id,
COUNT(*) 预收,
MIN(actual_date) first_date
FROM tmep_dwf_accept_policy
WHERE actual_date <= @date_id
OR actual_date IS NULL
GROUP BY insert_time,dept_id
)
,t2 AS (
SELECT t1.insert_time,
t1.dept_id,
n.number+1 days,
t1.预收,
DateAdd(day,n.number,t1.first_date) actual_date
FROM t1
JOIN master..spt_values n
ON n.type = 'p'
AND n.number <= DateDiff(day,first_date,@date_id)
)
,t3 AS (
SELECT insert_time,
dept_id,
actual_date,
COUNT(*) 实收
FROM tmep_dwf_accept_policy
WHERE actual_date <= @date_id
GROUP BY insert_time,dept_id,actual_date
)
SELECT @date_id date_id,
t2.dept_id,
t2.days,
t2.预收,
t4.实收
FROM t2
CROSS APPLY (SELECT SUM(实收) 实收
FROM t3
WHERE t3.insert_time = t2.insert_time
AND t3.dept_id = t2.dept_id
AND t3.actual_date <= t2.actual_date
) t4
ORDER BY t2.insert_time, t2.dept_id, t2.actual_date DESC
date_id dept_id days 预收 实收
----------------------- ------- ----------- ----------- -----------
2012-09-05 00:00:00.000 116 5 9 8
2012-09-05 00:00:00.000 116 4 9 7
2012-09-05 00:00:00.000 116 3 9 5
2012-09-05 00:00:00.000 116 2 9 2
2012-09-05 00:00:00.000 116 1 9 2
不过我这样写想改成循环或动态可以吗
select
date_id,
dept_id, '5日' days,count(a.product_id) '预收', count(a.product_id)实收
from t_a as a
left join t_a as b on a.dept_id=b.dept_id and actual_date<=pt_endtime-1
group by date_id,dept_id
union all
select
date_id,
dept_id, '4日' days,count(a.product_id) '预收', count(a.product_id)实收
from t_a as a
left join t_a as b on and a.dept_id=b.dept_id and actual_date<=pt_endtime-2
group by date_id,dept_id
但是(insert_time,dept_id)不同,要用游标;各自的天数也不同。
这样要写成二层循环。
date_id,
dept_id, count(a.product_id) '预收', count(b.product_id)'5日实收',count(c.product_id)'4日实收'
from t_a as a
left join t_a as b on a.dept_id=b.dept_id and actual_date<=pt_endtime-1
left join t_a as c on and a.dept_id=c.dept_id and actual_date<=pt_endtime-2
group by insert_time date_id,dept_id
这样怎么改成循环呢
再用 @insert_time、@dept_id 求出起至日期,循环天数。