Hi @emre… just playing around and wonder it its doable… I am getting an error
DROP VIEW STOCK
GO
CREATE VIEW STOCK AS
SELECT [InventoryItemName]
FROM [PeriodicConsumptionItems]
LEFT OUTER JOIN [InventoryItems] ON [InventoryItemId] = [InventoryItems].[Id]
WHERE [PeriodicConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND
CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05
GO
SELECT * FROM Stock ORDER BY [InventoryItemName]
GO
You should CREATE the View first by executing it in a Query editor, using the code you provided.
CREATE VIEW STOCK AS
SELECT [InventoryItemName]
FROM [PeriodicConsumptionItems]
LEFT OUTER JOIN [InventoryItems] ON [InventoryItemId] = [InventoryItems].[Id]
WHERE [PeriodicConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
AND
CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05
GO
Once the View is created, it is there forever, unless you delete it from the Database.
Then you should SELECT the View on it’s own within SambaPOS (the last part of your code, without the GO).
The view is already created… thats why I am using the DROP command.
The views, are STATIC, that means tat if you want to see actual data you have to create them again… If you leave a view live the data gets old in the moment any cell of any table that once populated it gets updated…
