现有4个表
1.物资分类表 (分类ID, 分类名称)
2.领用单位表 (单位ID, 单位名称)
3.物资基础表 (ID, 物资ID, 物资名称, 物资单价, 当前库存数, 月初库存数, 年初库存数, 库存更新日期)
4.物资采购表 {ID, 物资ID, 采购数量, 采购单价, 采购日期)
4.物资出库表 (ID, 物资ID, 领用单位, 领用数量, 领用单价, 领用日期)第4个表的领用单价在这里暂时没用
现在要求每月初盘点出上月的库盘量,金额,同时显示出各领用单位的领用消耗
查询中不需要考虑年初和月初变动数量,这个由另一个部门子程序控制具体查询结果如下显示,求SQL写法,非常感谢,分不够可以再加,这个项目所有都完成了,就差这最后一步了,谢谢大家==================================================================================================
xxxx年xx月 分类一库存盘点
| 年初库存 | 本年采购 | 本年出库 | 各单位领用数量 | 本期末库存
物资名称 物资单价 | 数量 总价 | 数量 金额 | 数量 金额 | 单位1 |单位2 |...(循环列出) | 数量 金额
xxxx 0 0 0 0 0 0 0 0 0 ..... 0 0本类小计: (只合计出金额部分)
.................... xxxx年xx月 分类一库存盘点
| 年初库存 | 本年采购 | 本年出库 | 各单位领用数量 | 本期末库存
物资名称 物资单价 | 数量 总价 | 数量 金额 | 数量 金额 | 单位1 |单位2 |...(循环列出) | 数量 金额
xxxx 0 0 0 0 0 0 0 0 0 ..... 0 0本类小计: (只合计出金额部分)
....................
xxxx年xx月 分类一库存盘点
| 年初库存 | 本年采购 | 本年出库 | 各单位领用数量 | 本期末库存
物资名称 物资单价 | 数量 总价 | 数量 金额 | 数量 金额 | 单位1 |单位2 |...(循环列出) | 数量 金额
xxxx 0 0 0 0 0 0 0 0 0 ..... 0 0本类小计: (只合计出金额部分)
....................
1.物资分类表 (分类ID, 分类名称)
2.领用单位表 (单位ID, 单位名称)
3.物资基础表 (ID, 物资ID, 物资名称, 物资单价, 当前库存数, 月初库存数, 年初库存数, 库存更新日期)
4.物资采购表 {ID, 物资ID, 采购数量, 采购单价, 采购日期)
4.物资出库表 (ID, 物资ID, 领用单位, 领用数量, 领用单价, 领用日期)第4个表的领用单价在这里暂时没用
现在要求每月初盘点出上月的库盘量,金额,同时显示出各领用单位的领用消耗
查询中不需要考虑年初和月初变动数量,这个由另一个部门子程序控制具体查询结果如下显示,求SQL写法,非常感谢,分不够可以再加,这个项目所有都完成了,就差这最后一步了,谢谢大家==================================================================================================
xxxx年xx月 分类一库存盘点
| 年初库存 | 本年采购 | 本年出库 | 各单位领用数量 | 本期末库存
物资名称 物资单价 | 数量 总价 | 数量 金额 | 数量 金额 | 单位1 |单位2 |...(循环列出) | 数量 金额
xxxx 0 0 0 0 0 0 0 0 0 ..... 0 0本类小计: (只合计出金额部分)
.................... xxxx年xx月 分类一库存盘点
| 年初库存 | 本年采购 | 本年出库 | 各单位领用数量 | 本期末库存
物资名称 物资单价 | 数量 总价 | 数量 金额 | 数量 金额 | 单位1 |单位2 |...(循环列出) | 数量 金额
xxxx 0 0 0 0 0 0 0 0 0 ..... 0 0本类小计: (只合计出金额部分)
....................
xxxx年xx月 分类一库存盘点
| 年初库存 | 本年采购 | 本年出库 | 各单位领用数量 | 本期末库存
物资名称 物资单价 | 数量 总价 | 数量 金额 | 数量 金额 | 单位1 |单位2 |...(循环列出) | 数量 金额
xxxx 0 0 0 0 0 0 0 0 0 ..... 0 0本类小计: (只合计出金额部分)
....................
共5个表
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)实际表就这样,其实Sol_At和Sol_Wt中的Price(单价)只是进出货的价格,盘点时以物资交流基础表(Sol_Mat)的Mat_Price为准,要求查询后如下列出
==================================================================================
1.物资名称 年初数量 年初总金额 本年采购数 本年采购金额 本年出库数 本年出库金额 当前数量 当前总金额 单位1领用数 单位2领用数....(这里单位都显示出来)
2.....................................
3.....................................
4.....................................
===================================================================================注意:
1.单位X领用数,就是把单位表中的单位都列出来,并统计出每个物资交的领用数量,当然可能不少物资领用为0
2.金额的计算直接用数量 * 物资基础表的单价,比如本年采购金额 = 本年采购数 * Mat_Price
3.而本年采购数和出库数就是今年以来累计的数量,通过Sol_AT和Sol_WT可以计算出来
4.这样的盘点每个月进行一下,也就说本月第一天盘点出上月末的数据
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_deparexec('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*/
from sol_depar这里面max应该为sum才对！！