There are 3 levels of sorting within Archive Shuttle:

  1. Check if container mapping has any priority. If there is some priority, it means 1, otherwise 0. This is sorted in descending order.
  2. Check real priority. This is sorted in ascending order.
  3. Calculation of some ratio of imported and exported count. To calculate this, we use ItemRoutingStatisticsConsolidated table. If ExportedCount is null or = 0, then we use 0 when we calculate the ratio. Ratio is calculated as [ItemRoutingStatisticsConsolidated].[ImportedCount]/[ItemRoutingStatisticsConsolidated].[ExportedCount]. This is sorted in descending order (bigger ratio first).

To better understand run query below. Last 3 column is used for ordering of results.

DECLARE @moduleId uniqueidentifier = '6e37e069-6a57-e811-813c-005056b82cc9'
SELECT [ContainerMapping].[ContainerMappingId], [ContainerMapping].[EnableItemGathering], [ContainerMapping].[EnableMigration], [ContainerMapping].[IsStage2Finished], [ContainerMapping].[SourceContainerId], [ContainerMapping].[Stage2Status],
[ContainerMapping].[TargetContainerId],
CASE WHEN [ContainerMapping].[Priority] IS NULL THEN 0 ELSE 1 END as priorityNull_firstLevelOfOrdering,
[ContainerMapping].[Priority] as realPriority_secondLevelOfOrdering,
CASE WHEN ([ItemRoutingStatisticsConsolidated].[ExportedCount] IS NULL
OR [ItemRoutingStatisticsConsolidated].[ExportedCount] = 0) THEN CAST(0 as float)
ELSE [ItemRoutingStatisticsConsolidated].[ImportedCount] / CAST([ItemRoutingStatisticsConsolidated].[ExportedCount] as float) END as ratio_thirdLevelOfOrdering
FROM (((((([Container] INNER JOIN [ContainerMapping] ON [Container].[ContainerId]=[ContainerMapping].[SourceContainerId])
INNER JOIN [Link] ON [Link].[LinkId]=[Container].[LinkId])
INNER JOIN [ModuleToLinkMapping] ON [Link].[LinkId]=[ModuleToLinkMapping].[LinkId])
LEFT JOIN [ItemRoutingStatisticsConsolidated] ON [ContainerMapping].[ContainerMappingId]=[ItemRoutingStatisticsConsolidated].[ContainerMappingId])
INNER JOIN [Container] [LPA_T1] ON [LPA_T1].[ContainerId]=[ContainerMapping].[TargetContainerId])
LEFT JOIN [Office365MailboxProvisioning] [LPA_O2] ON [LPA_T1].[ContainerId]=[LPA_O2].[ContainerId])
WHERE ( ( [ContainerMapping].[BackOffEnd] IS NULL OR [ContainerMapping].[BackOffEnd] < '2018-05-14 14:37:13')
AND [ContainerMapping].[EnableMigration] = 1
AND [ModuleToLinkMapping].[ModuleId] = @moduleId
AND [LPA_O2].[Office365MailboxProvisioningId] IS NULL)
ORDER BY CASE WHEN [ContainerMapping].[Priority] IS NULL THEN 0 ELSE 1 END DESC,
[ContainerMapping].[Priority] ASC,
CASE WHEN ([ItemRoutingStatisticsConsolidated].[ExportedCount] IS NULL
OR [ItemRoutingStatisticsConsolidated].[ExportedCount] = 0) THEN CAST(0 as float)
ELSE [ItemRoutingStatisticsConsolidated].[ImportedCount] / CAST([ItemRoutingStatisticsConsolidated].[ExportedCount] as float) END DESC

Use moduleId of some export module for which you need to check some eligible mappings.
You don’t need the backoff date, leave it as it is.
3 levels of sorting will return the result in exact order. This query does not care if Container Mapping is leaver or not.
First one record in the query will be handled as first.

Print Friendly, PDF & Email