@emre, been scratching my head about this for about an hour wondering why my query was pulling a bunch of NULLs, then I realized there are misnamed columns. As shown below, the column names outlined in RED have their names reversed in table [PeriodicConsumptionItems]:
I realize fixing this is going to break some Custom Reports for some people (yes, I’m thinking about you@gerlandog), but is there a chance this could be corrected sooner rather than later?
When I did the reports I noticed something strange but did not pay much attention… the database fields did not sound entirely correct, but again… was more interested in having it working than analyzing, LOL!!!
I want to save this query here for updating later when the fixes are in place. This is a properly constructed Stock Level Report (originally based on query from @gerlandog).
use [SambaPOS4]
SELECT
wp.[Id] as [WPID]
--, wp.[StartDate] as [WPBeg]
--, wp.[EndDate] as [WPEnd]
--, pci.[Id] as [pciID]
--, pc.[Id] as [pcID]
--, pci.[PeriodicConsumptionId] [pcIDbad]
--, wc.[Id] as [wcID]
--, pci.[WarehouseConsumptionId] as [wcIDbad]
--, wc.[WarehouseId] as [whID]
, wh.[Name] as [Warehouse]
, ii.[GroupCode] as [ItemType]
, ii.[Name] as [Item]
, ii.[TransactionUnit]
, CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as [QLarge]
, pci.[UnitMultiplier] as [Multiplier]
, ii.[BaseUnit]
, CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier])) as [QSmall]
FROM [PeriodicConsumptionItems] pci
--left join [PeriodicConsumptions] pc on pc.[Id]=pci.[PeriodicConsumptionId]
--above line will work for next version (4.1.56), until upgrade, must use following line (4.1.55)
left join [PeriodicConsumptions] pc on pc.[Id]=pci.[WarehouseConsumptionId]
left join [WarehouseConsumptions] wc on wc.[PeriodicConsumptionId]=pc.[Id]
left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]
left join [Warehouses] wh on wh.[Id]=wc.[WarehouseId]
left join [WorkPeriods] wp on wp.[Id]=pc.[WorkPeriodId]
WHERE 1=1
-- ensure we are pulling data for latest CLOSED WorkPeriod
AND wp.[Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
-- AND pci.[WarehouseConsumptionId]=wc.[Id]
-- above line will work for next version (4.1.56), until upgrade, must use following line (4.1.55)
AND pci.[PeriodicConsumptionId]=wc.[Id]
-- filter by Warehouse Type... comment line to show all Warehouse Types
AND wh.[WarehouseTypeId]=(select [Id] from [WarehouseTypes] where [Name]='Local Warehouses'
-- filter by Warehouse Name... comment line to show all Warehouses
AND wh.[Name]='SHOP'
)
ORDER BY [Warehouse], [ItemType], [Item]
Yay! As of v4.1.57, the column names are correct, so the following query works. @gerlandog, you will need to update your SQL…
use [SambaPOS4]
SELECT
wp.[Id] as [WPID]
--, wp.[StartDate] as [WPBeg]
--, wp.[EndDate] as [WPEnd]
--, pci.[Id] as [pciID]
--, pc.[Id] as [pcID]
--, pci.[PeriodicConsumptionId] [pcIDbad]
--, wc.[Id] as [wcID]
--, pci.[WarehouseConsumptionId] as [wcIDbad]
--, wc.[WarehouseId] as [whID]
, wh.[Name] as [Warehouse]
, ii.[GroupCode] as [ItemType]
, ii.[Name] as [Item]
, ii.[TransactionUnit]
, CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) as [QLarge]
, pci.[UnitMultiplier] as [Multiplier]
, ii.[BaseUnit]
, CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier])) as [QSmall]
FROM [PeriodicConsumptionItems] pci
left join [PeriodicConsumptions] pc on pc.[Id]=pci.[PeriodicConsumptionId]
left join [WarehouseConsumptions] wc on wc.[PeriodicConsumptionId]=pc.[Id]
left join [InventoryItems] ii on ii.[Id]=pci.[InventoryItemId]
left join [Warehouses] wh on wh.[Id]=wc.[WarehouseId]
left join [WorkPeriods] wp on wp.[Id]=pc.[WorkPeriodId]
WHERE 1=1
-- ensure we are pulling data for latest CLOSED WorkPeriod
AND wp.[Id] = (SELECT max([Id]) FROM [WorkPeriods] WHERE [StartDate]!=[EndDate])
AND pci.[WarehouseConsumptionId]=wc.[Id]
-- filter by Warehouse Type... comment line to show all Warehouse Types
AND wh.[WarehouseTypeId]=(select [Id] from [WarehouseTypes] where [Name]='Local Warehouses'
-- filter by Warehouse Name... comment line to show all Warehouses
AND wh.[Name]='SHOP'
)
ORDER BY [Warehouse], [ItemType], [Item]