/*1.物资分类表 (分类ID, 分类名称)
2.领用单位表 (单位ID, 单位名称)
3.物资基础表 (物资ID, 物资名称, 物资单价, 当前库存数, 月初库存数, 年初库存数, 库存更新日期)
4.物资采购表 {ID, 物资ID, 采购数量, 采购单价, 采购日期)
5.物资出库表 (ID, 物资ID, 领用单位, 领用数量, 领用单价, 领用日期)
'实际表对应如下
1.Sol_Class (ClassID, ClassName)
2.Sol_Depar (Depar_ID, Depar_Name)
3.Sol_Mat (Mat_ID, Mat_Name, Mat_Price, Mat_DQKC, Mat_YCKC, Mat_NCKC, Mat_KCRQ)
4.Sol_At (At_ID, Mat_ID, At_Num, At_Price, At_Date)
5.Sol_Wt (Wt_ID, Mat_ID, Depar_ID, Wt_Num, Wt_Price, Wt_Date)
*/
create table Sol_Class (ClassID int, ClassName varchar(10))
insert into sol_class select 1 ,'type1'
create table Sol_Depar (Depar_ID int, Depar_Name varchar(10))
insert sol_depar select 1,'depar1'
insert sol_depar select 2,'depar2'
create table Sol_Mat (Mat_ID int, Mat_Name varchar(10), Mat_Price int, Mat_DQKC int, Mat_YCKC int, Mat_NCKC int, Mat_KCRQ datetime)
insert sol_mat select 1,'test1',10,10,5,100,getdate()
insert sol_mat select 2,'test2',100,100,50,1000,getdate()
create table Sol_At (At_ID int, Mat_ID int, At_Num int, At_Price int, At_Date datetime)
insert sol_at select 1,1,10,10,getdate()
insert sol_at select 1,2,102,102,getdate()
create table Sol_Wt (Wt_ID int, Mat_ID int, Depar_ID int, Wt_Num int, Wt_Price int, Wt_Date datetime)
insert sol_Wt select 1,1,1,10,9,getdate()
insert sol_Wt select 1,1,2,12,8,getdate()
insert sol_Wt select 1,2,1,122,8,getdate()
go
--1.物资名称 年初数量 年初总金额 本年采购数 本年采购金额
--本年出库数 本年出库金额 当前数量 当前总金额 单位1领用数 单位2领用数....(这里单位都显示出来)
select a.mat_id,a.Mat_Name as 物资名称,a.Mat_NCKC as 年初数量,a.Mat_NCKC * a.Mat_Price as 年初总金额,
b.sum_at as 本年采购数,b.sum_atj as 本年采购金额,
c.sum_wt as 本年出库数,c.sum_wtj as 本年出库金额,Mat_DQKC as 当前数量,
a.Mat_DQKC * Mat_Price as 当前总金额,datepart(yy,a.Mat_KCRQ) as n
into tmp
from Sol_Mat a left join
(select mat_id,sum(at_num) as sum_at,sum(at_num * at_price) as sum_atj ,datepart(yy,At_Date) as n
from sol_at group by datepart(yy,At_Date) ,mat_id ) b on a.mat_id = b.mat_id and b.n = datepart(yy,a.Mat_KCRQ)
left join
(select mat_id,sum(wt_num) as sum_wt,sum(wt_num * wt_price) as sum_wtj ,datepart(yy,wt_Date) as n
from Sol_Wt group by datepart(yy,wt_Date) ,mat_id ) c on a.mat_id = c.mat_id and b.n = datepart(yy,a.Mat_KCRQ)
declare @s varchar(1000)
select @s = isnull(@s + ',' ,'') + '[ Depar_'+ Depar_Name+ ']= max(case when Depar_ID = '+ ltrim (Depar_ID)+ ' then Wt_Num end)'
from sol_depar
exec('select a.物资名称 ,a.年初数量 ,a.年初总金额 ,a.本年采购数 ,a.本年采购金额
,a.本年出库数 ,a.本年出库金额 ,a.当前数量 ,a.当前总金额,b.* from tmp a left join (select mat_id,'+ @s + ',datepart(yy,wt_Date) as n from Sol_Wt group by mat_id,datepart(yy,wt_Date)) b
on a.mat_id = b.mat_id and a.n = b.n')
drop table Sol_Class,Sol_Depar,Sol_Mat,Sol_At,Sol_Wt,tmp
/*
物资名称 年初数量 年初总金额 本年采购数 本年采购金额 本年出库数 本年出库金额 当前数量 当前总金额 mat_id Depar_depar1 Depar_depar2 n
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------- ------------- -----------
test1 100 1000 10 100 22 186 10 100 1 10 12 2008
test2 1000 100000 102 10404 122 976 100 10000 2 122 NULL 2008
*/
