SQL Query help needed with AvailableInventory calculation

Transact-SQL
1

Hi,

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: MEGA

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.

Thanks.

2

Maybe this will work for you:

select a.stkdepo
      ,b.depoadi
      ,sum(a.MevcutMiktar) as MevcutMiktar
      ,sum(a.SerbestMiktar) as SerbestMiktar
  from (select stkdepo
              ,sum(miktar) as MevcutMiktar
              ,sum(miktar) as SerbestMiktar
          from erp1.dbo.stkhardet
         where iptal=0
           and stkkod='STOK3'
         group by stkdepo
        union all
        select stkdepo
              ,0 as MevcutMiktar
              ,sum(miktar) as SerbestMiktar
          from erp2.dbo.stkhardet
         where iptal=0
           and stkkod='STOK3'
           and ((islemturu='C'
           and   belgeturu<>'UR'
                )
            or  islemturu='T'
               )
         group by stkdepo
       ) as a
       left outer join erp1.dbo.stkdepo as b
                    on b.depokodu=a.stkdepo
 group by a.stkdepo
         ,b.depoadi
;
1 Like
3

Hi bitsmed,

Your SQL seems to work. I just need to run some additional tests on it. I couldn't start testing because I couldn't get correct results at first place.

Thank you.

5

I seem to be able to get what I needed with below SQL

select l.stkdepo, sum(l.miktar) as MevcutMiktar,

((sum(l.miktar) +

coalesce((select sum(Erp2.dbo.stkhardet.miktar) from Erp2.dbo.stkhardet
where Erp2.dbo.stkhardet.stkdepo = l.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 = l.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 l

where iptal = 0 and l.stkkod = 'STOK3'
group by l.stkdepo

Result I receive seems to be correct. More testing I need to do though.

stkdepo                                  MevcutMiktar           SerbestMiktar
---------------------------------------- ---------------------- ----------------------
ANADEPO                                  50                     43
DEPOB                                    13                     13
DEPOC                                    7                      7

Thanks.

6

Try this:

select a.stkdepo
      ,b.depoadi
      ,sum(a.MevcutMiktar) as MevcutMiktar
      ,sum(a.SerbestMiktar) as SerbestMiktar
  from (select stkdepo
              ,sum(miktar) as MevcutMiktar
              ,sum(miktar) as SerbestMiktar
          from erp1.dbo.stkhardet
         where iptal=0
           and stkkod='STOK3'
         group by stkdepo
        union all
        select stkdepo
              ,0 as MevcutMiktar
              ,sum(miktar
                  *case
                      when islemturu='C'
                      then  1
                      else -1
                   end
                  ) as SerbestMiktar
          from erp2.dbo.stkhardet
         where iptal=0
           and stkkod='STOK3'
           and ((islemturu='C'
           and   belgeturu<>'UR'
                )
            or  islemturu='T'
               )
         group by stkdepo
       ) as a
       left outer join erp1.dbo.stkdepo as b
                    on b.depokodu=a.stkdepo
 group by a.stkdepo
         ,b.depoadi
;
7

Last SQL also gives correct results.

Thanks.