Было такое дело, хоть и микрософт , но пришлось переписывать sql с аксеса на нормальный синтаксис и представления это хорошо , но лучше переписать на запрос где - то типа :
declare @PrevDate smalldatetime,
@CurrDate smalldatetime

select @PrevDate = :PrevDate
select @CurrDate = :CurrDate

select
DerivedTable.PrevDate,
DerivedTable.CurrDate,
DerivedTable.ProductID,
Product.ProductName,
'PrevQuantity' = sum( DerivedTable.PrevQuantity ), -- входящий остаток
'InQuantity' = sum( DerivedTable.InQuantity ), -- приход
'OutQuantity' = sum( DerivedTable.OutQuantity ), -- расход
'StoreQuantity' = sum( DerivedTable.StoreQuantity ), -- остаток на скаде
'CreditInQuantity' = sum( DerivedTable.CreditInQuantity ), -- приход по кредитам
'CreditOutQuantity' = sum( DerivedTable.CreditOutQuantity ), -- расход по кредитам
'CreditQuantity' = sum( DerivedTable.CreditQuantity ), -- в кредите итого
'StoreRestQuantity' = sum( DerivedTable.PrevQuantity + DerivedTable.InQuantity - DerivedTable.OutQuantity ), -- Должна быть равна StoreQuantity для несоставных продуктов
'AllRestQuantity' = sum( DerivedTable.PrevQuantity + DerivedTable.InQuantity - DerivedTable.OutQuantity + DerivedTable.CreditQuantity ) -- Полные остатки с учётом продуктов в кредите
from (
-- Расход по Заказам составных и не составных продуктов
select 'PrevDate' = @PrevDate,
'CurrDate' = @CurrDate,
'ProductID' = Product.ProductID,
'PrevQuantity' = 0 ,
'InQuantity' = 0 ,
'OutQuantity' = sum( Quantity ),
'StoreQuantity' = 0,
'CreditInQuantity' = 0,
'CreditOutQuantity'= 0,
'CreditQuantity' = 0
from DBSOrdering..OrderItem OrderItem,
DBSOrdering..Orders Orders,
DBSOrdering..Product Product
where OrderItem.OrderID = Orders.OrderID
AND OrderItem.ProductID = Product.ProductID
AND Orders.OrderDate = @CurrDate
GROUP BY Product.ProductID
union -- Обединение с содержимым составного продукта
select 'PrevDate' = @PrevDate,
'CurrDate' = @CurrDate,
'ProductID' = ProductContent.ProductID,
'PrevQuantity' = 0 ,
'InQuantity' = 0 ,
'OutQuantity' = sum (OrderItem.Quantity * ProductContent.Quantity ),
'StoreQuantity' = 0,
'CreditInQuantity' = 0,
'CreditOutQuantity'= 0,
'CreditQuantity' = 0
from DBSOrdering..ProductContent ProductContent,
DBSOrdering..Product Product,
DBSOrdering..OrderItem OrderItem,
DBSOrdering..Orders Orders
where Orders.OrderID = OrderItem.OrderID
AND ProductContent.ParentID = Product.ProductID
AND OrderItem.ProductID = Product.ProductID
AND Product.Compound = 'T'
AND Orders.OrderDate = @CurrDate
GROUP BY ProductContent.ProductID
union -- Расход и приход по накладным
select 'PrevDate' = @PrevDate,
'CurrDate' = dbo.Invoice.InvoiceDate,
'ProductID' = dbo.InvoiceItem.ProductID,
'PrevQuantity' = 0,
'InQuantity' = case dbo.InvoiceType.OnCredit
WHEN 0 THEN 0
WHEN 1 THEN dbo.InvoiceItem.Quantity
WHEN 2 THEN 0
else 0
END,
'OutQuantity' = case dbo.InvoiceType.OnDebt
WHEN 0 THEN 0
WHEN 1 THEN dbo.InvoiceItem.Quantity
WHEN 2 THEN 0
else 0
END,
'StoreQuantity' = 0,
'CreditInQuantity' = case dbo.InvoiceType.OnCredit
WHEN 0 THEN 0
WHEN 1 THEN 0
WHEN 2 THEN dbo.InvoiceItem.Quantity
else 0
END,
'CreditOutQuantity'= case dbo.InvoiceType.OnDebt
WHEN 0 THEN 0
WHEN 1 THEN 0
WHEN 2 THEN dbo.InvoiceItem.Quantity
else 0
END,
'CreditQuantity' = 0
from dbo.InvoiceItem
LEFT JOIN dbo.Invoice ON ( dbo.InvoiceItem.InvoiceID = dbo.Invoice.InvoiceID )
LEFT JOIN dbo.InvoiceType ON ( dbo.Invoice.Type = dbo.InvoiceType.TypeID )
where dbo.Invoice.InvoiceDate = @CurrDate
union --- объединение по составному продукту в инвойсах
select 'PrevDate' = @PrevDate,
'CurrDate' = @CurrDate,
'ProductID' = ProductContent.ProductID,
'PrevQuantity' = 0 ,
'InQuantity' = case InvoiceType.OnCredit
WHEN 0 THEN 0
WHEN 1 THEN sum ( InvoiceItem.Quantity * ProductContent.Quantity )
WHEN 2 THEN 0
else 0
END,
'OutQuantity' = case InvoiceType.OnDebt
WHEN 0 THEN 0
WHEN 1 THEN sum (InvoiceItem.Quantity * ProductContent.Quantity )
WHEN 2 THEN 0
else 0
END,
'StoreQuantity' = 0,
'CreditInQuantity' = case InvoiceType.OnCredit
WHEN 0 THEN 0
WHEN 1 THEN 0
WHEN 2 THEN sum ( InvoiceItem.Quantity * ProductContent.Quantity )
else 0
END,
'CreditOutQuantity'= case InvoiceType.OnDebt
WHEN 0 THEN 0
WHEN 1 THEN 0
WHEN 2 THEN sum (InvoiceItem.Quantity * ProductContent.Quantity )
else 0
END,
'CreditQuantity' = 0
from DBSOrdering..ProductContent ProductContent,
DBSOrdering..Product Product,
dbo.InvoiceItem InvoiceItem,
dbo.Invoice Invoice,
dbo.InvoiceType InvoiceType
where
Invoice.InvoiceID = InvoiceItem.InvoiceID
AND Invoice.Type = InvoiceType.TypeID
AND InvoiceItem.ProductID = Product.ProductID
AND ProductContent.ParentID = Product.ProductID
AND Product.Compound = 'T'
AND Invoice.InvoiceDate = @CurrDate
GROUP BY ProductContent.ProductID ,InvoiceType.OnCredit,InvoiceType.OnDebt
union --- Входящий остаток на тек день
select 'PrevDate' = @PrevDate,
'CurrDate' = @CurrDate,
'ProductID' = GeneralStore.ProductID ,
'PrevQuantity' = sum(Quantity),
'InQuantity' = 0,
'OutQuantity' = 0,
'StoreQuantity' = 0,
'CreditInQuantity' = 0,
'CreditOutQuantity'= 0,
'CreditQuantity' = 0
from DBSOrdering..GeneralStore GeneralStore
where ValidDate = @PrevDate
GROUP BY ValidDate,ProductID
union --- Остаток на складе
select 'PrevDate' = @PrevDate,
'CurrDate' = @CurrDate,
'ProductID' = GeneralStore.ProductID ,
'PrevQuantity' = 0,
'InQuantity' = 0,
'OutQuantity' = 0,
'StoreQuantity' = sum(GeneralStore.Quantity),
'CreditInQuantity' = 0,
'CreditOutQuantity'= 0,
'CreditQuantity' = 0
from DBSOrdering..GeneralStore GeneralStore
where ValidDate = @CurrDate
GROUP BY ValidDate,ProductID
union --- Остатки в кредите
select 'PrevDate' = @PrevDate,
'CurrDate' = @CurrDate,
'ProductID' = StoreItem.ProductID ,
'PrevQuantity' = 0,
'InQuantity' = 0,
'OutQuantity' = 0,
'StoreQuantity' = 0,
'CreditInQuantity' = 0,
'CreditOutQuantity'= 0,
'CreditQuantity' = sum ( StoreItem.Quantity )
from dbo.StoreItem StoreItem
GROUP By StoreItem.ProductID
----
--ORDER BY Product.ProductID
) AS DerivedTable ,DBSOrdering..Product Product
where DerivedTable.ProductID = Product.ProductID
GROUP BY PrevDate,CurrDate,DerivedTable.ProductID,Product.ProductName
ORDER BY DerivedTable.ProductID