Monitor status of EOD and SOD

EODandSOD.sql

-- Change 0 to -1 yesterday, -2 day before, ect.
DECLARE @date DATETIME = DateAdd(Day, 0, GetDate());

DECLARE @startedLaterThan DATETIME = DateAdd(Hour, -18, @date);
DECLARE @businessDate DATE = DateAdd(Day, +1, CAST(@startedLaterThan AS DATE));

-- To see details about each store, remove (comment out) this summary header (up to "FROM ("), and footer (last two lines)
SELECT
	AccountingDate
	,"Store Type"
	,"EOD Status"
	,COUNT(*) Stores 
	,MIN(StartTime) StartTime
	,Max(MaxDate) LatestUpdate
	,SUM(PendingFiles) PendingFiles
	,SUM(CompletedFiles) CompletedFiles
	,SUM(UnprocessedReceipts) PendingReceipts
	,MIN(FirstUnprocessedReceiptTime) FirstUnprocessedReceiptTime
	,SUM(CompletedReceipts) CompletedReceipts
FROM (
	SELECT 
		ru.RetailUnitId
		,ru.RetailUnitNo
		,CASE WHEN ru.RetailUnitNo like '2499%' THEN 'Test' ELSE 'Live' END "Store Type"
		,log.AccountingDate
		,CASE WHEN RunStatus = 1 THEN 'Done' WHEN StartTime is not null THEN 'In progress' ELSE 'Not started' END "EOD Status"
		,(SELECT Max(dto) 
			  FROM (VALUES 
				([ScheduleRequestTime])
				,([StartTime])
				,([AdjustmentsApprovedDateTime])
				,([NumberOfCustomersCalculatedDateTime])
				,([CommittedSoldOutDateTime])
				,([SetStartQtyCompletedTime])
				,([CreatedDailyCountingTime])
				,([CompletedDailyCountingWorkflowTime])
				,([LastFileTime])
				) AS value(dto)) as MaxDate
		,log.StartTime
		,log.HostFilesVerified
		,log.AdjustmentsApprovedDateTime
		,log.[NumberOfCustomersCalculatedDateTime]
		,log.[CommittedSoldOutDateTime]
		,log.[SetStartQtyCompletedTime]
		,log.[CreatedDailyCountingTime]
		,log.[CompletedDailyCountingWorkflowTime]
		,log.[NumberOfItemsOnTodaysCounting]
		,log.[RunStatus]
		,log.[RunStatusAtTimeout]
		,FileLog.*
		,Receipts.UnprocessedReceipts
		,Receipts.CompletedReceipts
		,Receipts.FirstReceiptTime
		,Receipts.LastReceiptTime
		,Receipts.FirstUnprocessedReceiptTime
	FROM [Trio]..[RetailUnit] ru
	LEFT OUTER JOIN [TrioLogging].[dbo].[AccountingDayShiftLog] log ON log.RetailUnitId = ru.RetailUnitId and log.StartTime between @startedLaterThan AND DateAdd(Day, 1, @startedLaterThan)
	LEFT OUTER JOIN (
		SELECT 
			AccountingDayShiftLogId
			,SUM(CASE WHEN FileCompleteDateTime IS NULL	THEN 1 ELSE 0 END) PendingFiles
			,SUM(CASE WHEN FileCompleteDateTime IS NULL	THEN 0 ELSE 1 END) CompletedFiles
			,Min(FileCompleteDateTime) FirstFileTime
			,Max(FileCompleteDateTime) LastFileTime
		FROM [TrioLogging].[dbo].[AccountingDayShiftFileLog]
		GROUP BY AccountingDayShiftLogId
	) FileLog ON FileLog.AccountingDayShiftLogId = log.AccountingDayShiftLogId
	LEFT OUTER JOIN (
		SELECT 
			RetailUnitId
			,AccountingDate
			,SUM(CASE WHEN IsProcessed = 0 THEN 1 ELSE 0 END) UnprocessedReceipts
			,MIN(CASE WHEN IsProcessed = 0 THEN CreatedDateTime ELSE null END) FirstUnprocessedReceiptTime
			,SUM(CASE WHEN IsProcessed = 1 THEN 1 ELSE 0 END) CompletedReceipts
			,MIN(CreatedDateTime) FirstReceiptTime
			,MAX(CreatedDateTime) LastReceiptTime
		FROM [TrioReceipt].[dbo].[ReceiptHeader]
		where AccountingDate = CAST(@startedLaterThan AS DATE)
		GROUP BY RetailUnitId,AccountingDate
	) Receipts ON Receipts.RetailUnitId = ru.RetailUnitId --AND Receipts.AccountingDate = log.AccountingDate
	WHERE ru.IsActive = 1 AND ru.StartDate < @businessDate
) EOD
GROUP BY AccountingDate,"Store Type","EOD Status";

-- To see details about each store, remove (comment out) this summary header (up to "FROM ("), and footer (last two lines)
SELECT 
	BusinessDay
	,"Store Type"
	,"SOD Status"
	,COUNT(*) Stores 
	,MIN(StartTime) StartTime
	,Max(MaxDate) LatestUpdate
	,SUM(CASE WHEN AssortmentTime IS NULL THEN 1 ELSE 0 END) PendingHHTAssortment
	,SUM(CASE WHEN AssortmentTime IS NOT NULL THEN 1 ELSE 0 END) CompletedHHTAssortment
	,SUM(PendingHandTerminals) PendingHHTFiles
	,SUM(Handterminals) - SUM(PendingHandTerminals) CompletedHHTFiles
	,MAX(LatestHandTeminalTime) LatestHandTeminalTime
FROM (
	SELECT
		ru.RetailUnitId
		,ru.RetailUnitNo
		,CASE WHEN ru.RetailUnitNo like '2499%' THEN 'Test' ELSE 'Live' END "Store Type"
		,log.BusinessDay
		,CASE WHEN RunStatus = 1 THEN 'Done' WHEN StartTime is not null THEN 'In progress' ELSE 'Not started' END "SOD Status"
		,(SELECT Max(dto) 
			  FROM (VALUES 
				([StartTime])
				,([UpdatedAllSalesPrices])
				,([UpdatedAllTransferPrices])
				,([RemovedAllPromotionsForBusinessDayShift])
				,([UpdatedAllPromotionItemLocalUnknownItemLocals])
				,([CheckedAllCreateStockCounts])
				,([UpdatedAllInvoiceLineUnknownItemOrderingUnitNos])) AS value(dto)) as MaxDate
		,log.[StartTime]
		,log.[UpdatedAllSalesPrices]
		,log.[UpdatedAllTransferPrices]
		,log.[RemovedAllPromotionsForBusinessDayShift]
		,log.[UpdatedAllPromotionItemLocalUnknownItemLocals]
		,log.[CheckedAllCreateStockCounts]
		,log.[UpdatedAllInvoiceLineUnknownItemOrderingUnitNos]
		,hhLog.AssortmentTime
		,hhLog.HandTerminals
		,hhLog.PendingHandTerminals
		,hhLog.LatestHandTeminalTime
	FROM [Trio]..[RetailUnit] ru
	LEFT OUTER JOIN [TrioLogging].[dbo].[BusinessDayShiftLog] log ON log.RetailUnitId = ru.RetailUnitId and log.BusinessDay = @businessDate
	LEFT OUTER JOIN 
		(
			SELECT 
				assortment.RetailUnitId
				,Max(assortment.EndAt) AssortmentTime
				,Max(ht.FileSentDateTime) LatestHandTeminalTime
				,SUM(CASE WHEN ht.HandheldTerminalAssortmentExportLogId IS NOT NULL THEN 1 ELSE 0 END) HandTerminals
				,SUM(CASE WHEN ht.HandheldTerminalAssortmentExportLogId IS NOT NULL AND ht.FileSentDateTime IS NULL THEN 1 ELSE 0 END) PendingHandTerminals
			FROM TrioLogging..HandheldTerminalAssortmentExportLog assortment
			LEFT OUTER JOIN TrioLogging..HandheldTerminalAssortmentExportFileLog ht ON ht.HandheldTerminalAssortmentExportLogId = assortment.HandheldTerminalAssortmentExportLogId
			WHERE assortment.StartAt between @businessDate and DATEADD(DAY,1,@businessDate) and assortment.RequestedFrom = 'BusinessDayShift'
			GROUP BY assortment.RetailUnitId			
		) hhLog ON hhLog.RetailUnitId = Log.RetailUnitId
	WHERE ru.IsActive = 1 AND ru.StartDate < @businessDate
) SOD
GROUP BY BusinessDay,"Store Type","SOD Status";

Monitor status of EOD and SOD

Run the script at any time to get the current state of EOD and SOD.
Script automagically selects the correct Accounting/Business date.

The first 7 columns are identical for both resultsets:
1: Accounting/Business Date.
2: Current Status.
3: EOD/SOD start time of first store.
4: Max done time (contains latest date of completed flows. When status is "done", this is finished time.)
5: # of stores
6: # of stores started
7: # of stores done
8: # of stores pending

SOD script also monitors the creation of HH assortment which is the last step of EOD.
Total time might be longer than you are used to due to the fact that more is monitored.

Feel free to use it to monitor EOD & SOD on current version as well, but note:
Due to race conditions (and some bugs) in auditing, the script for SOD will report that some/most stores are "Pending".

EOD script should be OK on all environments.