Set all daily counting to todays start quantity.sql

Use Trio
GO

BEGIN TRANSACTION

DECLARE @retailUnitNo VARCHAR(13) = '24228';
DECLARE @countingDate DATE = CAST('2014-03-08' AS DATE);
DECLARE @Optalt UNIQUEIDENTIFIER = dbo.GetCommonCodeId('InventoryCountingDailyStatusCode', 'O');
DECLARE @IkkeOptalt UNIQUEIDENTIFIER = dbo.GetCommonCodeId('InventoryCountingDailyStatusCode', 'I');

--UPDATE InventoryCountingDaily
--SET CountedQty = CASE 
--		WHEN StartInventoryQty < 0
--			THEN 0
--		ELSE StartInventoryQty
--		END
--	,InventoryCountingDailyStatusCodeId = @Optalt
--WHERE InventoryCountingDailyId IN (
--		SELECT icd.InventoryCountingDailyId
--		FROM InventoryCounting ic
--		JOIN InventoryCountingDaily icd ON icd.InventoryCountingId = ic.InventoryCountingId
--		JOIN RetailUnit ru ON ru.RetailUnitId = ic.RetailUnitId
--		WHERE ru.RetailUnitNo = @retailUnitNo
--			AND ic.InventoryCountingDate = @countingDate
--			AND icd.InventoryCountingDailyStatusCodeId = @IkkeOptalt
--		);
		
SELECT icd.InventoryCountingDailyId
	,icd.StartInventoryQty
	,icd.CountedQty
	,CASE 
		WHEN icd.StartInventoryQty < 0
			THEN 0
		ELSE icd.StartInventoryQty
		END NewQty
FROM InventoryCounting ic
JOIN InventoryCountingDaily icd ON icd.InventoryCountingId = ic.InventoryCountingId
JOIN RetailUnit ru ON ru.RetailUnitId = ic.RetailUnitId
WHERE ru.RetailUnitNo = @retailUnitNo
	AND ic.InventoryCountingDate = @countingDate
	AND CountedQty IS NOT NULL
	AND icd.InventoryCountingDailyStatusCodeId = @Optalt;

ROLLBACK
--COMMIT