我觉得这样方便统计很多,单位统一用天,type start_zonetime end_zonetime
------ -------------- -------------
未开票 0 15
未开票 16 30
未开票 31 90
未开票 91 180
未开票 181 360
未开票 361 0
------ -------------- -------------
未开票 0 15
未开票 16 30
未开票 31 90
未开票 91 180
未开票 181 360
未开票 361 0
,XSJE=sum(XSJE)
from 统计表 a,数据表 b
where datediff(day,b.RQ,getdate()) between a.start_zonetime and b.end_zonetime
or case when b.start_zonetime=0 and datediff(day,b.RQ,getdate())<=b.end_zonetime then 1 else 0 end=1
or case when b.end_zonetime=0 and datediff(day,b.RQ,getdate())>=b.end_zonetime then 1 else 0 end=1
未开票 0 天 15 天
未开票 15 天 1 月
未开票 1 月 3 月
未开票 3 月 6 月
未开票 6 月 1 年
未开票 1 年 9999 年或者:
Id(严格排序,不许空缺) type zonetime date_type
1 未开票 15 天
2 未开票 1 月
3 未开票 3 月
4 未开票 6 月
5 未开票 1 年
create table T1 (
type varchar(20),
zonetime int,
date_type varchar(20)
)
go
insert T1
select
'未开票', 15, '天'
union all select
'未开票', 1 , '月'
union all select
'未开票', 3 , '月'
union all select
'未开票', 6 , '月'
union all select
'未开票', 1 , '年'go
create table T2 (
RQ datetime,
XH varchar(20),
SL int,
XSDJ numeric(10,1),
XSJE numeric(10,2)
)
goinsert T2
select
'2003-06-01', 'ID000124', 100 , 1.5, 150
union all select
'2003-07-05', 'ID000224', 100 , 2.5 , 250
union all select
'2004-07-01', 'QX000136', 500 ,5.0 , 2500go--产生临时表
select case date_type when '天' then dateadd(day,-zonetime,getdate())
when '周' then dateadd(week,-zonetime,getdate())
when '月' then dateadd(month,-zonetime,getdate())
when '年' then dateadd(year,-zonetime,getdate())
end as Begintime,getdate() as EndTime,
rtrim(zonetime)+date_type+'以内' as demo
into #t
from t1update a
set endtime=isnull((select min(begintime) from #t where Begintime>a.Begintime),endtime)
from #t a
insert #t
select begintime='1900-1-1',endtime=begintime,demo=replace(demo,'以内','以上') from #t
where begintime=(select min(begintime) from #t)--统计查询
select t.demo,isnull(sum(d.XSJE),0) as XSJE from #t t left join t2 d
on d.rq>=t.begintime and d.rq<t.endtime
group by t.demo--删除临是表
drop table #t--删除环境
drop table t2
drop table t1--测试结果
/*
demo XSJE
------------------------------------ ----------------------------------------
15天以内 .00
1年以内 .00
1年以上 400.00
1月以内 2500.00
3月以内 .00
6月以内 .00(所影响的行数为 6 行)
*/
create table T1 (type varchar(20),zonetime int,date_type varchar(20))
insert T1 select '未开票',15,'天'
union all select '未开票',1 ,'月'
union all select '未开票',3 ,'月'
union all select '未开票',6 ,'月'
union all select '未开票',1 ,'年'create table T2 (RQ datetime,XH varchar(20),SL int,XSDJ numeric(10,1),XSJE numeric(10,2))
insert T2 select '2003-06-01','ID000124',100,1.5,150
union all select '2003-07-05','ID000224',100,2.5,250
union all select '2004-07-01','QX000136',500,5.0,2500
go--直接查询(不用临时表,当然效率比临时表低)
select type,xsje=isnull(sum(xsje),0)
from(
select type='['+case
when a.type is null
then '<='+cast(b.zonetime as varchar)+b.date_type
when b.type is null
then '>'+cast(a.zonetime as varchar)+a.date_type
else '>'++cast(a.zonetime as varchar)+a.date_type
+' and <='+cast(b.zonetime as varchar)+b.date_type
end+']'
,start_zonetime=case b.date_type
when '天' then dateadd(day,-b.zonetime,getdate())
when '月' then dateadd(month,-b.zonetime,getdate())
when '年' then dateadd(year,-b.zonetime,getdate())
end
,end_zonetime=case a.date_type
when '天' then dateadd(day,-a.zonetime,getdate())
when '月' then dateadd(month,-a.zonetime,getdate())
when '年' then dateadd(year,-a.zonetime,getdate())
end
,sid=isnull(charindex(a.date_type,'天月年'),0)
from t1 a full join t1 b
on (
select count(*) from t1
where zonetime<a.zonetime
and charindex(date_type,'天月年')=charindex(a.date_type,'天月年')
or charindex(date_type,'天月年')<charindex(a.date_type,'天月年')
)+1=(
select count(*) from t1
where zonetime<b.zonetime
and charindex(date_type,'天月年')=charindex(b.date_type,'天月年')
or charindex(date_type,'天月年')<charindex(b.date_type,'天月年')
)
)a left join t2 b on
case when a.start_zonetime is null then 1
else case when b.RQ>a.start_zonetime then 1 else 0 end
end=1 and
case when a.end_zonetime is null then 1
else case when b.RQ<=a.end_zonetime then 1 else 0 end
end=1
group by a.type,a.sid
order by a.sid
go
--删除环境
drop table t1,t2/*--测试结果type xsje
------------------------------------ ---------------------
[<=15天] .00
[>15天 and <=1月] 2500.00
[>1月 and <=3月] .00
[>3月 and <=6月] .00
[>6月 and <=1年] .00
[>1年] 400.00(所影响的行数为 6 行)--*/
select id=identity(int,1,1),* into #t
from t1
order by charindex(date_type,'天月年')select type,xsje=isnull(sum(xsje),0)
from(
select type='['+case
when a.type is null
then '<='+cast(b.zonetime as varchar)+b.date_type
when b.type is null
then '>'+cast(a.zonetime as varchar)+a.date_type
else '>'++cast(a.zonetime as varchar)+a.date_type
+' and <='+cast(b.zonetime as varchar)+b.date_type
end+']'
,start_zonetime=isnull(case b.date_type
when '天' then dateadd(day,-b.zonetime,getdate())
when '月' then dateadd(month,-b.zonetime,getdate())
when '年' then dateadd(year,-b.zonetime,getdate())
end,'1900-1-1')
,end_zonetime=isnull(case a.date_type
when '天' then dateadd(day,-a.zonetime,getdate())
when '月' then dateadd(month,-a.zonetime,getdate())
when '年' then dateadd(year,-a.zonetime,getdate())
end,'9999-12-31')
,sid=isnull(charindex(a.date_type,'天月年'),0)
from #t a full join #t b
on a.id+1=b.id
)a left join t2 b
on b.RQ>a.start_zonetime
and b.RQ<=a.end_zonetime
group by a.type,a.sid
order by a.sid
drop table #t