Using MS-SQL Server 2012. I want to display current inventory and available inventory side by side. Below is the query I have managed to write which is not displaying correct results.
stkhardet table in Erp1 table contains inventory movements
stkhardet table in Erp2 table contains reservations
sum(Erp1.stkhardet.miktar) gives me CurrentInventory (aka MevcutMiktar below)
sum(Erp2.stkhardet.miktar) gives me Reservations
(CurrentInventory - Reservations) gives me AvailableInventory (aka SerbestMiktar below).
Not all records are causing a reservation in Erp2 database. I normally run two separate SQLs for getting reservation totals when checking inventory in my application. However, reporting them on screen in a single SQL is what I couldn't do.
Here you can find database backups: https://mega.nz/#!iosjiBgI!7R_Dm8ruXdXRYzEu8eItL2wZrsOEj3a3cg3ieUgJ63w
Below is the SQL I run and get wrong results while connected to Erp1 database:
-- This SQL should be run while connected to Erp1 database select stkhardet.stkdepo, stkdepo.depoadi, sum(stkhardet.miktar) as MevcutMiktar, -- Calculate CurrentInventory + Inventory Exit - Inventory Transfer between warehouses -- Inventory Exits are recorded as negative numbers, care should be taken for it. Filtering: islemturu = 'C' and belgeturu <> 'UR' gives us Inventory Exits which are waiting approval -- Inventory Movements are recorded as positive numbers. Filtering: islemturu = 'T' gives us Inventory Movements waiting for approvals which are waiting for approval ((sum(stkhardet.miktar) + coalesce((select sum(Erp2.dbo.stkhardet.miktar) from Erp2.dbo.stkhardet where Erp2.dbo.stkhardet.stkdepo = stkhardet.stkdepo and Erp2.dbo.stkhardet.stkkod = stkhardet.stkkod and Erp2.dbo.stkhardet.iptal = 0 and Erp2.dbo.stkhardet.islemturu = 'C' and Erp2.dbo.stkhardet.belgeturu <> 'UR') , 0)) + coalesce((select sum(Erp2.dbo.stkhardet.miktar) from Erp2.dbo.stkhardet where Erp2.dbo.stkhardet.stkdepo = stkhardet.stkdepo and Erp2.dbo.stkhardet.stkkod = stkhardet.stkkod and Erp2.dbo.stkhardet.iptal = 0 and Erp2.dbo.stkhardet.islemturu = 'T') , 0)) as SerbestMiktar from stkhardet left join stkdepo on stkhardet.stkdepo = stkdepo.depokodu where iptal = 0 and stkhardet.stkkod = 'STOK3' group by stkhardet.stkdepo, stkdepo.depoadi
Result of the above SQL is as follows:
stkdepo depoadi MevcutMiktar SerbestMiktar ANADEPO ANA DEPO ADI 50 48 DEPOB B DEPOSU 13 11 DEPOC C DEPOSU 7 5
Correct results would be like below:
stkdepo depoadi MevcutMiktar SerbestMiktar ANADEPO ANA DEPO ADI 50 48 DEPOB B DEPOSU 13 13 DEPOC C DEPOSU 7 7
Which means, only first warehouse has a reservation of 2 pieces, and rest of the warehouses are all without any reservation.
I believe my above SQL calculates all reservations for all warehouses. Where as I should tell SQL to calculate AvailableInventory separately for each "inventory and warehouse" together for each listed row.