62,243
社区成员




[code=SQL]
declare @MaterialStore table(
ms_ID int ,
m_ID int,
ms_Date datetime,
ms_Worker varchar(20),
ms_GoodNum int,
ms_BadNum int,
ms_IsCount int,
ms_Remark varchar(50)
)
declare @BuyMaterial table(
bm_ID int,
m_ID int,
bm_Code int,
bm_Num int,
bm_UnitPrice int,
bm_TotalPrice int,
bm_Date datetime,
bm_IsDeleted int,
bm_StaffName varchar(20),
bm_Invoice int
)
declare @SellMaterial table(
sm_ID int,
c_ID int,
m_ID int,
sm_Code int,
sm_Num int,
sm_UnitPrice int,
sm_TotalPrice int,
sm_Date datetime,
sm_IsDeleted int,
sm_StaffName int,
sm_IsOut int
)
insert into @MaterialStore (ms_ID,ms_GoodNum,ms_Date) select 1, 100, '2009-9-1'
union all select 2, 150, '2009-9-1'
union all select 3, 120, '2009-9-1'
union all select 1, 130, '2009-9-10'
union all select 3, 170, '2009-9-13'
union all select 2, 110, '2009-9-15'
--select ms_ID,ms_GoodNum,ms_Date from @MaterialStore
insert into @BuyMaterial (m_ID,bm_Num,bm_Date) select 1, 30, '2009-9-10'
union all select 3, 50, '2009-9-13'
--select m_ID,bm_Num,bm_Date from @BuyMaterial
insert into @SellMaterial (m_ID, sm_Num, sm_Date) select 2, 40, '2009-9-15'
--select m_ID, sm_Num, sm_Date from @SellMaterial
select ms_ID 编号,
(select ms_GoodNum from @MaterialStore m where day(ms_Date)=1 and m.ms_ID=t.ms_ID ) 期初,
(select isnull(min(bm_Num),0) from @BuyMaterial where m_ID=t.ms_ID ) 本期入库,
(select isnull(min(sm_Num),0) from @SellMaterial where m_ID=t.ms_ID ) 本期出库,
( select ms_GoodNum from @MaterialStore where ms_Date= (select max(ms_Date) from @MaterialStore m where m.ms_ID=t.ms_ID )) 期末结存
from @MaterialStore t
group by ms_ID
编号 期初 本期入库 本期出库 期末结存
1 100 30 0 130
2 150 0 40 110
3 120 50 0 170
select m_ID,sum(ms_GoodNum) as ms_GoodNum,sum(bm_num) as bm_num,sum(sm_num) as sm_num from(
select m_ID,ms_GoodNum,bm_num=0,sm_num=0 from (select top 1 m_ID,ms_GoodNum from MaterialStore where ms_Date < '2009-9-1' order by ms_Date desc) t1
union
select m_ID,ms_GoodNum=0,bm_num,sm_num from (select m_ID,sum(b.bm_Num) as bm_Num,sm_Num=0 from BuyMaterial group by m_ID having bm_Date < '2009-9-1') t2
union
select m_ID,ms_GoodNum=0,bm_num,sm_num from (select m_ID,bm_Num=0,sum(sm_Num) as sm_num from BuyMaterial group by m_ID having sm_Date < '2009-9-1') t3
) t
group by m_ID
select m.m_ID,sum(m.ms_GoodNum) as total_num,sum(b.bm_Num) as bm_Num,sum(s.sm_Num) as sm_Num,max(m.ms_Date)
from MaterialStore m
join BuyMaterial b on m.m_ID=b.m_ID
join SellMaterial s on m.m_ID=s.m_ID
group by m.m_ID having m.ms_Date < '2009-9-1'