Create table jzBillHuihe(BillID varchar(32),--单据号,与下表的BillNo对应
RuzhangTime DateTime --入账时间
) insert jzBillHuihe
select 'SPCGFK081103005','2008-11-03'
union all
select 'SPCGFK081104005','2008-11-04'
union all
select 'SPCGFK081105005','2008-11-05'
union all
select 'SPCGFK081205005','2008-12-05'
union all
select 'SPCGFK081205006','2008-12-05'
create table jzSPCGFuKuanDan
(ID int ,BillNO varchar(32),--单据号
GongyingshangID varchar(32),--供应商
Huilv float,--汇率
TotalSQMoney float,--金额
EmployeeID varchar(32) ---申请人
) insert jzSPCGFuKuanDan
select 1, 'SPCGFK081103005','供应商1',1.0,100,'小王'
union all
select 2, 'SPCGFK081104005','供应商1',1.0,200,'小王'
union all
select 3, 'SPCGFK081105005','供应商2',1.0,300,'小张'
union all
select 4, 'SPCGFK081205005','供应商2',1.0,100,'小张'
union all
select 5,'SPCGFK081205006','供应商1',7.0,200,'小王'
union all
select 6,'SPCGFK081205007','供应商1',7.0,200,'小王' --注意该笔数据是没有做帐的记录,不能统计在内select convert(varchar(10),n.ruzhangtime,120) ruzhangtime , n.billid , m.totalsqmoney * m.huilv je, m.employeeid from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno
union all
select convert(varchar(7),n.ruzhangtime,120) + '月合计' ruzhangtime , billid = '', sum(m.totalsqmoney * m.huilv) je, employeeid = '' from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno group by convert(varchar(7),n.ruzhangtime,120) + '月合计'
union all
select convert(varchar(4),n.ruzhangtime,120) + '年合计' ruzhangtime , billid = '', sum(m.totalsqmoney * m.huilv) je, employeeid = '' from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno group by convert(varchar(4),n.ruzhangtime,120) + '年合计'
order by ruzhangtimedrop table jzBillHuihe , jzSPCGFuKuanDan /*
ruzhangtime billid je employeeid
------------- -------------------------------- ----------------------------------------------------- --------------------------------
2008-11-03 SPCGFK081103005 100.0 小王
2008-11-04 SPCGFK081104005 200.0 小王
2008-11-05 SPCGFK081105005 300.0 小张
2008-11月合计 600.0
2008-12-05 SPCGFK081205005 100.0 小张
2008-12-05 SPCGFK081205006 1400.0 小王
2008-12月合计 1500.0
2008年合计 2100.0 (所影响的行数为 8 行)
*/
RuzhangTime DateTime --入账时间
) insert jzBillHuihe
select 'SPCGFK081103005','2008-11-03'
union all
select 'SPCGFK081104005','2008-11-04'
union all
select 'SPCGFK081105005','2008-11-05'
union all
select 'SPCGFK081205005','2008-12-05'
union all
select 'SPCGFK081205006','2008-12-05'
create table jzSPCGFuKuanDan
(ID int ,BillNO varchar(32),--单据号
GongyingshangID varchar(32),--供应商
Huilv float,--汇率
TotalSQMoney float,--金额
EmployeeID varchar(32) ---申请人
) insert jzSPCGFuKuanDan
select 1, 'SPCGFK081103005','供应商1',1.0,100,'小王'
union all
select 2, 'SPCGFK081104005','供应商1',1.0,200,'小王'
union all
select 3, 'SPCGFK081105005','供应商2',1.0,300,'小张'
union all
select 4, 'SPCGFK081205005','供应商2',1.0,100,'小张'
union all
select 5,'SPCGFK081205006','供应商1',7.0,200,'小王'
union all
select 6,'SPCGFK081205007','供应商1',7.0,200,'小王' --注意该笔数据是没有做帐的记录,不能统计在内select convert(varchar(10),n.ruzhangtime,120) ruzhangtime , n.billid , m.totalsqmoney * m.huilv je, m.employeeid from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno
union all
select convert(varchar(7),n.ruzhangtime,120) + '月合计' ruzhangtime , billid = '', sum(m.totalsqmoney * m.huilv) je, employeeid = '' from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno group by convert(varchar(7),n.ruzhangtime,120) + '月合计'
union all
select convert(varchar(4),n.ruzhangtime,120) + '年合计' ruzhangtime , billid = '', sum(m.totalsqmoney * m.huilv) je, employeeid = '' from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno group by convert(varchar(4),n.ruzhangtime,120) + '年合计'
order by ruzhangtimedrop table jzBillHuihe , jzSPCGFuKuanDan /*
ruzhangtime billid je employeeid
------------- -------------------------------- ----------------------------------------------------- --------------------------------
2008-11-03 SPCGFK081103005 100.0 小王
2008-11-04 SPCGFK081104005 200.0 小王
2008-11-05 SPCGFK081105005 300.0 小张
2008-11月合计 600.0
2008-12-05 SPCGFK081205005 100.0 小张
2008-12-05 SPCGFK081205006 1400.0 小王
2008-12月合计 1500.0
2008年合计 2100.0 (所影响的行数为 8 行)
*/
as
begin
select distinct cast(year(RuzhangTime) as varchar(4)) + cast(month(RuzhangTime) as varchar(4)) into #jzBillHuihe from jzBillHuihe select * from jzSPCGFuKuanDan a inner join
(select GongyingshangID from jzBillHuihe a inner join #jzBillHuihe b on
cast(year(b.RuzhangTime) as varchar(4)) + cast(month(b.RuzhangTime) as varchar(4)) = cast(year(a.RuzhangTime) as varchar(4)) + cast(month(a.RuzhangTime) as varchar(4))) b on a.GongyingshangID =b.GongyingshangID
end第二 显示效果需要在前台做特殊处理
11.3 SPCGFK081103005 100 '小王'
11.4 SPCGFK081104005 200 '小王'
11月合计 300
12.5 SPCGFK081103005 1400 '小王'
12月合计 1400
2008年合计 1700
RuzhangTime DateTime --入账时间
) insert jzBillHuihe
select 'SPCGFK081103005','2008-11-03'
union all
select 'SPCGFK081104005','2008-11-04'
union all
select 'SPCGFK081105005','2008-11-05'
union all
select 'SPCGFK081205005','2008-12-05'
union all
select 'SPCGFK081205006','2008-12-05'
create table jzSPCGFuKuanDan
(ID int ,BillNO varchar(32),--单据号
GongyingshangID varchar(32),--供应商
Huilv float,--汇率
TotalSQMoney float,--金额
EmployeeID varchar(32) ---申请人
) insert jzSPCGFuKuanDan
select 1, 'SPCGFK081103005','供应商1',1.0,100,'小王'
union all
select 2, 'SPCGFK081104005','供应商1',1.0,200,'小王'
union all
select 3, 'SPCGFK081105005','供应商2',1.0,300,'小张'
union all
select 4, 'SPCGFK081205005','供应商2',1.0,100,'小张'
union all
select 5,'SPCGFK081205006','供应商1',7.0,200,'小王'
union all
select 6,'SPCGFK081205007','供应商1',7.0,200,'小王' --注意该笔数据是没有做帐的记录,不能统计在内select convert(varchar(10),n.ruzhangtime,120) ruzhangtime , n.billid , m.totalsqmoney * m.huilv je, m.employeeid from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno and m.employeeid = '小王'
union all
select convert(varchar(7),n.ruzhangtime,120) + '月合计' ruzhangtime , billid = '', sum(m.totalsqmoney * m.huilv) je, employeeid = '' from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno and m.employeeid = '小王' group by convert(varchar(7),n.ruzhangtime,120) + '月合计'
union all
select convert(varchar(4),n.ruzhangtime,120) + '年合计' ruzhangtime , billid = '', sum(m.totalsqmoney * m.huilv) je, employeeid = '' from jzBillHuihe n, jzSPCGFuKuanDan m where n.billid = m.billno and m.employeeid = '小王' group by convert(varchar(4),n.ruzhangtime,120) + '年合计'
order by ruzhangtimedrop table jzBillHuihe , jzSPCGFuKuanDan /*
ruzhangtime billid je employeeid
------------- -------------------------------- ----------------------------------------------------- --------------------------------
2008-11-03 SPCGFK081103005 100.0 小王
2008-11-04 SPCGFK081104005 200.0 小王
2008-11月合计 300.0
2008-12-05 SPCGFK081205006 1400.0 小王
2008-12月合计 1400.0
2008年合计 1700.0 (所影响的行数为 6 行)*/