Consumption Report Help: Entire Month

Version 4 V4 Question
1

I currently get the following report which displays the stock that has been consumed in the last concluded work period.

2b0b5472426ead6015340d6dd409272892101a44.png1366×768 41.9 KB

I was wondering if it is possible to get the total stock that has been consumed in a specified duration - like say past 3 days or last whole month, if so how can i change the following code (this current code displays the report shown above):

SELECT [InventoryItemName],

[Consumption] as Stock

FROM [PeriodicConsumptionItems]

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [WarehouseConsumptions] ORDER BY [Id] DESC)

ORDER BY [InventoryItemId]
2

just change the reporting dates at top. You can select your own range.

3

Nope - i would have to change the “Where” in the SQL code. regardless of the dates selected, the script is such it gives the same output, i.e the consumption of the last completed WorkPeriod

1 Like
4

This will allow you to use the Date filters at the top of the Report Screen:

SELECT
  [InventoryItemName] as [Item]
, sum([Consumption]) as [Consumed]

FROM [PeriodicConsumptionItems] i
left join [PeriodicConsumptions] p on p.[Id]=i.[PeriodicConsumptionId]

WHERE 1=1
--and p.[StartDate] >= '2015-01-01'
--and p.[EndDate]   <= '2015-01-31'
and p.[StartDate] >= '{Start}'
and p.[EndDate]   <= '{End}'

GROUP BY [InventoryItemName]
ORDER BY [InventoryItemName]
3 Likes