--改临时表
select InventoryID,MaterialNo,PeriodStart,PeriodStartAmount,PeriodEnd,PeriodEndAmount,BranchNo,BranchName from View_MaterialInventory
where BranchNo ='ZC' and InventoryID = (dbo.RS_FN_GetInventoryID('2004-4-28','ZC')-1)
--改临时表
Select materialno,sum(InAmount) as InCome,sum(OutAmount) as OutCome from View_MaterialBillQuery
Where BranchNo = 'ZC'
and StockDate >= '2004-4-28'
and DateDiff(hour,'2004-5-27',StockDate)< 24
group by materialno
--改临时表
UPDATE [LocalWebErp603].[dbo].[TBL_StockInventory]
SET [PeriodEndAmount] = [EndCome]
from [dbo].[VIEW_Inventory_end]
where [dbo].[TBL_StockInventory].[MaterialNo] = [dbo].[VIEW_Inventory_end].[MaterialNo] and [InventoryID] = 2
--改临时表
UPDATE [LocalWebErp603].[dbo].[TBL_StockInventory]
SET [PeriodEndAmount] = [EndCome] ,[PeriodStartAmount] = [dbo].[VIEW_Inventory_end].[PeriodEndAmount]
,[InAmount] = [InCome],[OutAmount] = [OutCome]
from [dbo].[VIEW_Inventory_end]
where [dbo].[TBL_StockInventory].[MaterialNo]
= [dbo].[VIEW_Inventory_end].[MaterialNo] and [InventoryID] = 2
--.net 自带的能够处理sql server企业管理器处理视图的缺陷。
--仓库结存表的处理，临时表的期初不能为null，否则永远得不出期末。
--new
--结存表
UPDATE [LocalWebErp603].[dbo].[TBL_StockInventoryTemp]
SET [PeriodStartAmount] = [EndCome]
from [dbo].[VIEW_Inventory_end]
where [MaterialNo] = [dbo].[VIEW_Inventory_end].[MaterailNo]
--临时表
UPDATE [LocalWebErp603].[dbo].[TBL_StockInventory]
SET [PeriodEndAmount] = [EndCome] ,[PeriodStartAmount] = [dbo].[VIEW_Inventory_end].[PeriodEndAmount]
,[InAmount] = [InCome],[OutAmount] = [OutCome]
from [dbo].[VIEW_Inventory_end]
where [dbo].[TBL_StockInventory].[MaterialNo]
= [dbo].[VIEW_Inventory_end].[MaterailNo] and [InventoryID] = 2
--把null的纪录改为0
UPDATE TBL_StockInventory
SET PeriodStartAmount = 0, PeriodEndAmount = 0
WHERE (PeriodStartAmount IS NULL) AND (PeriodEndAmount IS NULL) AND
(InAmount = OutAmount)
--把null的纪录改为0
UPDATE TBL_StockInventoryTemp
SET PeriodStartAmount = 0, PeriodEndAmount = 0
WHERE (PeriodStartAmount IS NULL) AND (PeriodEndAmount IS NULL) AND
(InAmount = OutAmount)
--608 更新
UPDATE [dbo].[TBL_StockInventoryTemp]
SET [InAmount]=[InCome], [OutAmount]=[OutCome]
from [View_StockTempInveryEnd]
WHERE [TBL_StockInventoryTemp].[MaterialNo] = [View_StockTempInveryEnd].[MaterialNo]
And [TBL_StockInventoryTemp].[BranchNo]= 'zc'
