counting.sql

with cte
as
(select ru.RetailUnitNo
, ic.InventoryCountingDate
, i.ItemNo
, icd.StartInventoryQty
, icd.CountedQty
, 'optælling' as type
from trio..RetailUnit ru 
inner join trio..InventoryCounting ic on ru.RetailUnitId = ic.RetailUnitId
inner join trio..InventoryCountingDaily icd on ic.InventoryCountingId = icd.InventoryCountingId
inner join trio..ItemLocal il on icd.ItemLocalId = il.ItemLocalId
inner join trio..Item i on il.ItemId = i.ItemId
where ic.InventoryCountingDate between '2014-01-06' and '2014-01-12'
and icd.CountedQty is not null
union all
select ru.RetailUnitNo, p.RegistrationDate, i.ItemNo, cast(ia.startInventoryQty as int), p.NewQty, 'newPrio' 
from trio..NewPrimoRegistration p 
inner join trio..ItemLocal il on p.ItemLocalId = il.ItemLocalId
inner join trio..RetailUnit ru on il.RetailUnitId = ru.RetailUnitId
inner join trio..Item i on i.ItemId = il.ItemId
inner join trio..ItemLocalInventoryAdjustment ilia on il.ItemLocalId = ilia.ItemLocalId and ilia.ReferenceId = p.NewPrimoRegistrationId
cross apply trio.dbo.GetCalculateCurrentInventoryQty(ilia.ItemLocalId, ilia.ItemLocalInventoryAdjustmentId) ia
where p.IsHandled = 1
and p.RegistrationDate between '2014-01-06' and '2014-01-12'
union all
select ru.RetailUnitNo, r.RegistrationDate, i.ItemNo, cast(ia.startInventoryQty as int), 0, 'sold out'  from 
trio..SoldOutRegistration r 
inner join trio..ItemLocal il on r.ItemLocalId = il.ItemLocalId
inner join trio..Item i on il.ItemId = i.ItemId
inner join trio..RetailUnit ru on il.RetailUnitId = ru.RetailUnitId
inner join trio..ItemLocalInventoryAdjustment ilia on il.ItemLocalId = ilia.ItemLocalId and ilia.ReferenceId = r.SoldOutRegistrationId
cross apply trio.dbo.GetCalculateCurrentInventoryQty(ilia.ItemLocalId, ilia.ItemLocalInventoryAdjustmentId) ia
where r.IsCancelled = 0
and r.RegistrationDate between '2014-01-06' and '2014-01-12')
select * from cte
order by cte.RetailUnitNo, cte.ItemNo, cte.InventoryCountingDate