AldersVarer.sql

Begin transaction

INSERT INTO TRIOStaging.dbo.BDMItemTransaction
(BDMItemTransactionId, TransactionNo, ObjectId, RetailUnitNo, StatusId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)
SELECT NewId() BDMItemTransactionId
	,100 TransactionNo
	,il.ItemLocalId ObjectId
	,ru.RetailUnitNo
	,0 StatusId
	,GETDATE() CreatedDate
	,'ph99epp' CreatedBy
	,GETDATE() ModifiedDate
	,'ph99epp' ModifiedBy
FROM [Trio].[dbo].[Item] i
INNER JOIN Trio..ItemLocal il ON il.ItemId = i.ItemId
INNER JOIN Trio..RetailUnit ru ON ru.RetailUnitId = il.RetailUnitId
WHERE LegalAgeQty > 0
	AND ru.IsActive = 1

--commit
--rollback

select count(*), StatusId
from TrioStaging..BDMItemTransaction i 
where CreatedBy = 'ph99epp' and CreatedDate > CAST(GetDate() AS DATE)
group by StatusId

SELECT count(*)
	,ru.RetailUnitNo
FROM [Trio].[dbo].[Item] i
INNER JOIN Trio..ItemLocal il ON il.ItemId = i.ItemId
INNER JOIN Trio..RetailUnit ru ON ru.RetailUnitId = il.RetailUnitId
WHERE LegalAgeQty > 0
	AND ru.IsActive = 1
group by ru.RetailUnitNo
order by count(*) desc