create table #stockin(fdate datetime,fitemname varchar(100),fqtyin decimal(18,2))
insert into #stockin values('2003-1-3','AAA',125)
insert into #stockin values('2003-1-10','AAA',120)
insert into #stockin values('2003-1-13','AAA',-20)
insert into #stockin values('2003-1-30','AAA',25)
insert into #stockin values('2003-2-3','AAA',100)
insert into #stockin values('2003-3-5','AAA',120)select fdate,fitemname,fqtyin,结余数量 from(
select CONVERT(varchar(10),fdate,120) fdate,fitemname,fqtyin,(select sum(fqtyin) from #stockin where fdate<=tem.fdate) 结余数量,CONVERT(varchar(10),fdate,120) flag1,CONVERT(varchar(7),fdate,120) flag2,CONVERT(varchar(4),fdate,120) flag3 from #stockin tem
union all
select CONVERT(varchar(10),fdate,120),fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(day,fdate,tem.fdate)>=0),CONVERT(varchar(10),fdate,120) flag1,CONVERT(varchar(7),fdate,120) flag2,CONVERT(varchar(4),fdate,120) flag3 from (select CONVERT(varchar(10),fdate,120) fdate,'本日发生额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(varchar(10),fdate,120)) tem
union all
select '',fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(month,fdate,tem.fdate+'-01')>=0),fdate+'-32',fdate,left(fdate,4) from (select CONVERT(char(7),fdate,120) fdate,'本月发生额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(char(7),fdate,120)) tem
union all
select '',fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(year,fdate,tem.fdate+'-01-01')>=0),fdate+'-13-33',fdate+'-13',fdate from (select CONVERT(char(4),fdate,120) fdate,'本年累计额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(char(4),fdate,120)) tem) tem2
order by flag3,flag2,flag1
insert into #stockin values('2003-1-3','AAA',125)
insert into #stockin values('2003-1-10','AAA',120)
insert into #stockin values('2003-1-13','AAA',-20)
insert into #stockin values('2003-1-30','AAA',25)
insert into #stockin values('2003-2-3','AAA',100)
insert into #stockin values('2003-3-5','AAA',120)select fdate,fitemname,fqtyin,结余数量 from(
select CONVERT(varchar(10),fdate,120) fdate,fitemname,fqtyin,(select sum(fqtyin) from #stockin where fdate<=tem.fdate) 结余数量,CONVERT(varchar(10),fdate,120) flag1,CONVERT(varchar(7),fdate,120) flag2,CONVERT(varchar(4),fdate,120) flag3 from #stockin tem
union all
select CONVERT(varchar(10),fdate,120),fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(day,fdate,tem.fdate)>=0),CONVERT(varchar(10),fdate,120) flag1,CONVERT(varchar(7),fdate,120) flag2,CONVERT(varchar(4),fdate,120) flag3 from (select CONVERT(varchar(10),fdate,120) fdate,'本日发生额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(varchar(10),fdate,120)) tem
union all
select '',fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(month,fdate,tem.fdate+'-01')>=0),fdate+'-32',fdate,left(fdate,4) from (select CONVERT(char(7),fdate,120) fdate,'本月发生额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(char(7),fdate,120)) tem
union all
select '',fitemname,fqtyin,(select sum(fqtyin) from #stockin where datediff(year,fdate,tem.fdate+'-01-01')>=0),fdate+'-13-33',fdate+'-13',fdate from (select CONVERT(char(4),fdate,120) fdate,'本年累计额' fitemname,sum(fqtyin) fqtyin from #stockin group by CONVERT(char(4),fdate,120)) tem) tem2
order by flag3,flag2,flag1
create table stockin(
fdate datetime,
fitemname varchar(100),
fqtyin decimal(18,2)
)
--加字段
alter table stockin add fmonth datetime
--建资料
insert into stockin values('2003-1-3','AAA',125,'2003-1-31 23:59:59')
insert into stockin values('2003-1-10','AAA',120,'2003-1-31 23:59:59')
insert into stockin values('2003-1-13','AAA',-20,'2003-1-31 23:59:59')
insert into stockin values('2003-1-30','AAA',25,'2003-1-31 23:59:59')
insert into stockin values('2003-2-3','AAA',100,'2003-2-28 23:59:59')
insert into stockin values('2003-3-5','AAA',120,'2003-3-31 23:59:59')
--我查
select fdate 日期, fitemname 项目, fqtyin 发生数量, --挑日常资料
(select sum(fqtyin) from stockin where fdate<=s1.fdate) 结余数量
from stockin s1
union
select fdate 日期, '本日发生额' 项目, fqtyin 发生数量, --挑日结资料
(select sum(fqtyin) from stockin where fdate<=s2.fdate) 结余数量
from stockin s2
union
select fmonth 月份, '本月合计' 项目,sum(fqtyin) 发生数量, --挑月结资料
(select sum(fqtyin)
from
(select fmonth,sum(fqtyin) fqtyin from stockin group by fmonth) mm
where fmonth<=s3.fmonth) 结余数量
from stockin s3
group by fmonth
union
select fmonth 月份, '本年累计' 项目, --年结资料
(select sum(fqtyin)
from (select fmonth,sum(fqtyin) fqtyin from stockin group by fmonth) mm
where fmonth<=s4.fmonth) 发生数量,
(select sum(fqtyin) from stockin where fdate<=s4.fmonth) 结余数量
from stockin s4
group by fmonth--说明
1.建一辅助字段 fmonth,其用意看看挑本月合计和本年累计就知道了,其值定义很精确用于排序
2.本年累计和本月合计顺序不对,将就着用吧
3.可能不用辅助字段fmonth也行,太晚了想睡觉,no thing...