Скрипты SQL для проверки данных

Проверка позиции на сайте

В параметрах запроса указать (заменить):

• {Логин} - логин пользователя на сайте

• {Артикул} - очищенный артикул для поиска

• {Бренд} - бренд

SET NOCOUNT ON;

-- параметры
DECLARE @webUserLogin NVARCHAR(100) = N'{Логин}';
DECLARE @vendocrPurifiedCode NVARCHAR(200) = N'{Артикул}';
DECLARE @vendorName NVARCHAR(100) = N'{Бренд}';

-- проверка товара
DECLARE @productId UNIQUEIDENTIFIER;
DECLARE @restUnitId UNIQUEIDENTIFIER;
SELECT
    @productId = [product].[ArtID]
  , @restUnitId = [restUnit].[RestUnitOfIssueID]
FROM
    [dbo].[Articles] [product]
LEFT JOIN [dbo].[RestUnitsOfIssue] [restUnit]
    ON [restUnit].[RestUnitOfIssueID] = [product].[RestUnitOfIssueID]
WHERE
    [product].[SearchArticle] = @vendocrPurifiedCode
    AND [product].[BrandName] = @vendorName;

IF @productId IS NULL
    BEGIN
        IF NOT EXISTS
        (
            SELECT
                1
            FROM
                [dbo].[SiteVirtualStorePriceLists] [priceList]
            WHERE
                [priceList].[ProductSearchArticle] = @vendocrPurifiedCode
                AND [priceList].[ProductBrandAlias] = @vendorName
        )
            BEGIN
                PRINT 'Товар не найден в списке номенклатуры и в прайс-листе';
                RETURN;
            END;
    END;

-- проверка единицы измерений
IF @productId IS NOT NULL
   AND @restUnitId IS NULL
    BEGIN
        PRINT 'Для товара не указана единица измерения';
    END;

-- проверка кросса "сам на себя"
IF NOT EXISTS
(
    SELECT
        1
    FROM
        [dbo].[ArticlesCrosses] AS [cross]
    WHERE
        [cross].[CrossSearchArticle] = @vendocrPurifiedCode
        AND [cross].[CrossBrandName] = @vendorName
        AND [cross].[CrossType] = 1
)
    BEGIN
        PRINT 'Не найден кросс "сам на себя"';
        RETURN;
    END;

-- проверка белых списков
IF EXISTS
(
    SELECT
        1
    FROM
        zw.[WhitelistedProducts]
)
    BEGIN
        IF NOT EXISTS
        (
            SELECT
                1
            FROM
                [zw].[WhitelistedProducts] [whiteList]
            WHERE
                [whiteList].[ProductSearchArticle] = @vendocrPurifiedCode
                AND [whiteList].[ProductBrandName] = @vendorName
        )
            BEGIN
                PRINT 'Белые списки включены. ТОвар не включен в белый список';
                RETURN;
            END;
    END;

-- проверка черных списков
IF EXISTS
(
    SELECT
        1
    FROM
        [zw].[BlacklistedProducts] [blackList]
    WHERE
        [blackList].[ProductSearchArticle] = @vendocrPurifiedCode
        AND [blackList].[ProductBrandName] = @vendorName
)
    BEGIN
        PRINT 'Товар находится в черном списке';
        RETURN;
    END;

-- поиск и проверка пользователя
DECLARE @webUserId UNIQUEIDENTIFIER;
DECLARE @webUserRestGroupId UNIQUEIDENTIFIER;
SELECT
    @webUserId = [webUser].[UserID]
  , @webUserRestGroupId = [webUser].[RestGroupId]
FROM
    [dbo].[SiteUsers] [webUser]
WHERE
    [webUser].[UserLogin] = @webUserLogin;

IF @webUserId IS NULL
    BEGIN
        PRINT 'Не найден веб-пользователь';
        RETURN;
    END;

-- поиск и проверка роли
DECLARE @roleId UNIQUEIDENTIFIER;
SELECT
    @roleId = [webUserWebRole].[RoleID]
FROM
    [dbo].[UsersRoles] [webUserWebRole]
WHERE
    [webUserWebRole].[UserID] = @webUserId;

IF @roleId IS NULL
    BEGIN
        PRINT 'Не найдена роль веб-пользователя';
        RETURN;
    END;

DECLARE @webRoleRestGroupId UNIQUEIDENTIFIER;
SELECT
    @webRoleRestGroupId = [webRole].[SiteUserStoresAvailabilityGroupsID]
FROM
    [dbo].[SiteRoles] [webRole]
WHERE
    [webRole].[RoleID] = @roleId;

-- поиск и проверка договоров
DECLARE @customerId UNIQUEIDENTIFIER;
DECLARE @customerContractId UNIQUEIDENTIFIER;
DECLARE @customerContractPriceTypeId UNIQUEIDENTIFIER;
DECLARE @customerContractBasePriceTypeId UNIQUEIDENTIFIER;
DECLARE @restGroupId UNIQUEIDENTIFIER;
DECLARE @pksd NVARCHAR(300);

DECLARE [customerTreatiesCursor] CURSOR FOR
SELECT
    [customer].[ContractorID] AS [customerId]
  , [customerContract].[ContractorsTreatiesID] AS [customerContractId]
  , [customerContract].[ArticlesPriceTypesID] AS [customerContractPriceTypeId]
  , [priceType].[BasePriceType] AS [customerContracBasetPriceTypeId]
  , ISNULL([customer].[Name], '') + ' (' + ISNULL([customer].[Code], '') + '), ' + ISNULL([customerContract].[Name], '') + ' (' + ISNULL([customerContract].[Code], '') + '), ' + [priceType].[Name]
    + ' (' + CAST([customerContract].[ArticlesPriceTypesID] AS NVARCHAR(100)) + ')' + CHAR(10) AS [Name]
  , COALESCE([customerContract].[SiteRestGroupId], [customer].[SiteRestGroupId], @webUserRestGroupId, @webRoleRestGroupId) AS [RestGroupId]
FROM
    [dbo].[SiteUsersContractors] [webUserCustomer]
INNER JOIN [dbo].[Contractors] [customer]
    ON [customer].[ContractorID] = [webUserCustomer].[ContractorID]
INNER JOIN [dbo].[ContractorsTreaties] [customerContract]
    ON [customerContract].[ContractorID] = [customer].[ContractorID]
INNER JOIN [dbo].[ArticlesPriceTypes] [priceType]
    ON [priceType].[ArticlesPriceTypesID] = [customerContract].[ArticlesPriceTypesID]
WHERE
    [webUserCustomer].[UserID] = @webUserId;

OPEN [customerTreatiesCursor];

FETCH NEXT FROM [customerTreatiesCursor]
INTO
    @customerId
  , @customerContractId
  , @customerContractPriceTypeId
  , @customerContractBasePriceTypeId
  , @pksd
  , @restGroupId;

IF @@FETCH_STATUS <> 0
    BEGIN
        PRINT 'Не найдены догвооры контрагента';
        RETURN;
    END;
WHILE @@FETCH_STATUS = 0
    BEGIN

        PRINT @pksd;

        DECLARE @stockAvailability TABLE
            (
                RepositoryId UNIQUEIDENTIFIER
              , SupplierId UNIQUEIDENTIFIER
              , Quantity DECIMAL(15, 3)
            );

        IF @restGroupId IS NULL
            BEGIN
                INSERT INTO
                    @stockAvailability
                (
                    [RepositoryId]
                  , [SupplierId]
                  , [Quantity]
                )
                SELECT
                    [stockAvailability].[RepositoryId]
                  , [stockAvailability].[SupplierId]
                  , [stockAvailability].[Quantity]
                FROM
                    [dbo].[SiteRests2] [stockAvailability]
                WHERE
                    [stockAvailability].[ProductSearchArticle] = @vendocrPurifiedCode
                    AND [stockAvailability].[ProductBrand] = @vendorName;
            END;
        ELSE
            BEGIN
                INSERT INTO
                    @stockAvailability
                (
                    [RepositoryId]
                  , [SupplierId]
                  , [Quantity]
                )
                SELECT
                    [stockAvailability].[RepositoryId]
                  , [stockAvailability].[SupplierId]
                  , [stockAvailability].[Quantity]
                FROM
                    [dbo].[SiteRestGroupRests2] [stockAvailability]
                WHERE
                    [stockAvailability].[ProductSearchArticle] = @vendocrPurifiedCode
                    AND [stockAvailability].[ProductBrand] = @vendorName
                    AND [stockAvailability].[RestGroupId] = @restGroupId;
            END;

        DECLARE @quantity NVARCHAR(MAX) = N'Остатки:' + CHAR(10) + CHAR(13);
        SELECT
            @quantity = @quantity + COALESCE([stock].[Name], [order].[Number], [supplier].[Name]) + N': ' + CAST([stockAcailability].[Quantity] AS NVARCHAR(10)) + CHAR(10)
        FROM
            @stockAvailability [stockAcailability]
        LEFT JOIN [dbo].[Stores] [stock]
            ON [stockAcailability].[RepositoryId] = [stock].[StoreID]
        LEFT JOIN [dbo].[OrderToSupplier] [order]
            ON [stockAcailability].[RepositoryId] = [order].[OrderToSupplierID]
        LEFT JOIN [dbo].[Contractors] [supplier]
            ON [stockAcailability].[SupplierId] = [supplier].[ContractorID]
        WHERE
            [stockAcailability].[Quantity] <> 0;

        PRINT @quantity;

        DECLARE @price DECIMAL(15, 2);

        IF @productId IS NOT NULL
            BEGIN

                SELECT TOP 1
                       @price = [price].[Price]
                FROM
                    [dbo].[LC_ArticlesPrices] [price]
                WHERE
                    (
                        [price].[ArticlesPriceTypesID] = @customerContractPriceTypeId
                        OR [price].[ArticlesPriceTypesID] = @customerContractBasePriceTypeId
                    )
                    AND [price].[ArtID] = @productId
                    AND [price].[ArticleDescriptionID] IS NULL
                    AND [price].[Period] <= GETDATE()
                ORDER BY
                    CASE
                        WHEN [price].[ArticlesPriceTypesID] = @customerContractPriceTypeId THEN
                            1
                        ELSE
                            0
                    END DESC
                  , [price].[Period] DESC;

                IF @price IS NULL
                    BEGIN
                        PRINT 'Цена компании в срезе цен не указана' + CHAR(10);

                        SELECT TOP 1
                               @price = [price].[Price]
                        FROM
                            [dbo].[ArticlesPrices] [price]
                        WHERE
                            (
                                [price].[ArticlesPriceTypesID] = @customerContractPriceTypeId
                                OR [price].[ArticlesPriceTypesID] = @customerContractBasePriceTypeId
                            )
                            AND [price].[ArtID] = @productId
                            AND [price].[ArticleDescriptionID] IS NULL
                            AND [price].[Period] <= GETDATE()
                        ORDER BY
                            CASE
                                WHEN [price].[ArticlesPriceTypesID] = @customerContractPriceTypeId THEN
                                    1
                                ELSE
                                    0
                            END DESC
                          , [price].[Period] DESC;

                        IF @price IS NULL
                            BEGIN
                                PRINT 'Цена компании не указана' + CHAR(10);
                            END;
                        ELSE
                            BEGIN
                                PRINT 'Цена компании: ' + CAST(@price AS NVARCHAR(10)) + CHAR(10);
                            END;
                    END;
                ELSE
                    BEGIN
                        PRINT 'Цена компании в срезе цен: ' + CAST(@price AS NVARCHAR(10)) + CHAR(10);
                    END;
            END;

        DECLARE @priceListPrices NVARCHAR(MAX) = N'Цены поставщиков:' + CHAR(10) + CHAR(13);

        SELECT
            @priceListPrices
            = @priceListPrices + COALESCE([priceList].[StoreName], [order].[Number], [supplier].[Name], 'Прайс-лист') + N': ' + CAST([supplierPrice].[Price] AS NVARCHAR(10)) + CHAR(10)
        FROM
            [dbo].[LC_ContractorsArticlesPrices] [supplierPrice]
        LEFT JOIN [dbo].[OrderToSupplier] [order]
            ON [supplierPrice].[ArticlesPriceTypesID] = [order].[OrderToSupplierID]
        LEFT JOIN [dbo].[SiteVirtualStorePriceLists] [priceList]
            ON [supplierPrice].[ProductSearchArticle] = [priceList].[ProductSearchArticle]
               AND [supplierPrice].[ProductBrand] = [priceList].[ProductBrandAlias]
               AND [supplierPrice].[ArticlesPriceTypesID] = [priceList].[StoreUid]
               AND [supplierPrice].[Price] = [priceList].[Price]
        LEFT JOIN [dbo].[Contractors] [supplier]
            ON [priceList].[SupplierId] = [supplier].[ContractorID]
        WHERE
            (
                @productId IS NOT NULL
                AND [supplierPrice].[ArtID] = @productId
                OR @productId IS NULL
                   AND [supplierPrice].[ProductSearchArticle] = @vendocrPurifiedCode
                   AND [supplierPrice].[ProductBrand] = @vendorName
                   AND [supplierPrice].[ArtID] IS NULL
            );

        PRINT @priceListPrices;

        FETCH NEXT FROM [customerTreatiesCursor]
        INTO
            @customerId
          , @customerContractId
          , @customerContractPriceTypeId
          , @customerContractBasePriceTypeId
          , @pksd
          , @restGroupId;
    END;
CLOSE [customerTreatiesCursor];
DEALLOCATE [customerTreatiesCursor];

Проверка собственных остатков

В параметрах запроса указать (заменить):

• {Артикул} - очищенный артикул для поиска

• {Бренд} - бренд

SET NOCOUNT ON;
DECLARE @VendorCode NVARCHAR(200) = N'{Артикул}';
DECLARE @VendorName NVARCHAR(100) = N'{Бренд}';
DECLARE @ProductId UNIQUEIDENTIFIER = NULL;
DECLARE @ProductDescriptionId UNIQUEIDENTIFIER = NULL;

IF @ProductId IS NULL
    BEGIN
        SELECT
            @ProductId = [product].[ArtID]
        FROM
            [dbo].[Articles] [product]
        WHERE
            [product].[SearchArticle] = @VendorCode
            AND [product].[BrandName] = @VendorName;
        IF @@ROWCOUNT > 1
            BEGIN
                PRINT 'Найдено более одного товара с указанными артикулом и брендом. Пожалуйста, укажите идентификатор товара. Выбранный идентификатор товара: ' + CAST(@ProductId AS NVARCHAR(50));
            END;

    END;

IF @ProductId IS NULL
    BEGIN
        PRINT 'Не найден товар с указанным артикулом и брендом.';
        RETURN;
    END;

DECLARE @productMovement TABLE
    (
        [MovementType] NVARCHAR(50)
      , [StockId] UNIQUEIDENTIFIER
      , [StockName] NVARCHAR(250)
      , [StockType] NVARCHAR(20)
      , [RegistratorId] UNIQUEIDENTIFIER
      , [Quantity] DECIMAL(15, 3)
      , [Date] DATETIME2(7)
    );

WITH [productStockMovement]
AS (SELECT
        [productOnStcok].[MovementType]
      , [productOnStcok].[RegistratorId]
      , [productOnStcok].[StockId]
      , SUM([productOnStcok].[Quantity]) AS [Quantity]
      , MAX([productOnStcok].[Period]) AS [Date]
    FROM
    (
        SELECT
            'Остатки' AS [MovementType]
          , [productOnStcok].[Registrator] AS [RegistratorId]
          , [productOnStcok].[StoreID] AS [StockId]
          , [productOnStcok].[Quantity]
          , [productOnStcok].[Period]
        FROM
            [dbo].[ArticlesInStores] [productOnStcok]
        WHERE
            @ProductId = [productOnStcok].[ArtID]
            AND
            (
                @ProductDescriptionId IS NULL
                AND [productOnStcok].[ArticleDescriptionID] IS NULL
                OR @ProductDescriptionId = [productOnStcok].[ArticleDescriptionID]
            )
        UNION ALL
        SELECT
            'Перемещение' AS [MovementType]
          , [productTransfer].[Registrator] AS [RegistratorId]
          , [productTransfer].[StoreID] AS [StockId]
          , -[productTransfer].[Quantity]
          , [productTransfer].[Period]
        FROM
            [dbo].[ToTransferArticlesFromStores] [productTransfer]
        WHERE
            @ProductId = [productTransfer].[ArtID]
            AND
            (
                @ProductDescriptionId IS NULL
                AND [productTransfer].[ArticleDescriptionID] IS NULL
                OR @ProductDescriptionId = [productTransfer].[ArticleDescriptionID]
            )
        UNION ALL
        SELECT
            'Резерв' AS [MovementType]
          , [productReserve].[Registrator] AS [RegistratorId]
          , [productReserve].[StoreID] AS [StockId]
          , -[productReserve].[Quantity]
          , [productReserve].[Period]
        FROM
            [dbo].[ReservedArticlesInStores] [productReserve]
        WHERE
            @ProductId = [productReserve].[ArtID]
            AND
            (
                @ProductDescriptionId IS NULL
                AND [productReserve].[ArticleDescriptionID] IS NULL
                OR @ProductDescriptionId = [productReserve].[ArticleDescriptionID]
            )
        UNION ALL
        SELECT
            'Остатки' AS [MovementType]
          , [productRetailStock].[Registrator] AS [RegistratorId]
          , [productRetailStock].[StoreID] AS [StockId]
          , [productRetailStock].[Quantity]
          , [productRetailStock].[Period]
        FROM
            [dbo].[ArticlesInRetails] [productRetailStock]
        WHERE
            @ProductId = [productRetailStock].[ArtID]
            AND
            (
                @ProductDescriptionId IS NULL
                AND [productRetailStock].[ArticleDescriptionID] IS NULL
                OR @ProductDescriptionId = [productRetailStock].[ArticleDescriptionID]
            )
    ) [productOnStcok]
    GROUP BY
        [productOnStcok].[MovementType]
      , [productOnStcok].[StockId]
      , [productOnStcok].[RegistratorId])
INSERT INTO
    @productMovement
(
    [StockId]
  , [StockName]
  , [StockType]
  , [Date]
  , [MovementType]
  , [RegistratorId]
  , [Quantity]
)
SELECT
    [stock].[StoreID]
  , [stock].[Name]
  , [stock].[StoreType]
  , [productStockMovement].[Date]
  , [productStockMovement].[MovementType]
  , [productStockMovement].[RegistratorId]
  , [productStockMovement].[Quantity]
FROM
    [productStockMovement]
LEFT JOIN [dbo].[Stores] [stock]
    ON [productStockMovement].[StockId] = [stock].[StoreID];

DECLARE @StockId UNIQUEIDENTIFIER;

DECLARE [StockCursor] CURSOR FOR
SELECT
    [t].[StockId]
FROM
    @productMovement [t]
GROUP BY
    [t].[StockId];

OPEN [StockCursor];
FETCH NEXT FROM [StockCursor]
INTO
    @StockId;
WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT
            [t].[StockName] AS [Склад]
          , [t].[StockType] AS [ТипСклада]
          , [t].[MovementType] AS [ТипДвижения]
          , [t].[Date] AS [ДатаДвижения]
          , [t].[RegistratorId] AS [Регистратор]
          , [t].[Quantity] AS [Количество]
          , SUM([t].[Quantity]) OVER (ORDER BY
                                          [t].[StockName]
                                        , [t].[Date]
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                                     ) AS [Итог]
        FROM
            @productMovement [t]
        WHERE
            @StockId = [t].[StockId]
        ORDER BY
            [t].[Date];

        SELECT
            [t].[StockName] AS [Склад]
          , [t].[StockType] AS [ТипСклада]
          , [t].[MovementType] AS [ТипДвижения]
          , SUM([t].[Quantity]) AS [Количество]
        FROM
            @productMovement [t]
        WHERE
            @StockId = [t].[StockId]
        GROUP BY
            [t].[StockName]
          , [t].[StockType]
          , [t].[MovementType];

        SELECT
            [productTotal].[StockName] AS [Склад]
          , [productTotal].[StockType] AS [ТипСклада]
          , [productTotal].[Quantity] AS [ВычесленноеКоличество]
          , [productStockTotalTrigger].[Quantity] AS [КоличествоВСрезе]
          , CASE
                WHEN [productTotal].[Quantity] <> [productStockTotalTrigger].[Quantity] THEN
                    'Да'
                ELSE
                    'Нет'
            END AS [Ошибка]
        FROM
        (
            SELECT
                [t].[StockId]
              , [t].[StockName]
              , [t].[StockType]
              , SUM([t].[Quantity]) AS [Quantity]
            FROM
                @productMovement [t]
            WHERE
                @StockId = [t].[StockId]
            GROUP BY
                [t].[StockId]
              , [t].[StockName]
              , [t].[StockType]
        ) [productTotal]
        LEFT JOIN [dbo].[SiteRests2] [productStockTotalTrigger]
            ON [productTotal].[StockId] = [productStockTotalTrigger].[RepositoryId]
               AND @ProductId = [productStockTotalTrigger].[ProductId]
               AND
               (
                   @ProductDescriptionId IS NULL
                   AND [productStockTotalTrigger].[ProductDescriptionId] IS NULL
                   OR @ProductDescriptionId = [productStockTotalTrigger].[ProductDescriptionId]
               );

        FETCH NEXT FROM [StockCursor]
        INTO
            @StockId;
    END;
CLOSE [StockCursor];
DEALLOCATE [StockCursor];

SELECT
    [productTotalTrigger].[QuantityFromStore] AS [КоличествоВСрезеБезГруппДоступности]
  , [productTotalTrigger].[IsFromStore] AS [ЕстьВНаличииВСрезеБезГруппДоступности]
  , CASE
        WHEN [productTotalTrigger].[IsFromStore] = 0
             AND [productTotalTrigger].[QuantityFromStore] > 0
             OR [productTotalTrigger].[IsFromStore] = 1
                AND [productTotalTrigger].[QuantityFromStore] = 0 THEN
            'Да'
        ELSE
            'Нет'
    END AS [Ошибка]
FROM
    [dbo].[SiteRestsSummary2] [productTotalTrigger]
WHERE
    @ProductId = [productTotalTrigger].[ProductId]
    AND
    (
        @ProductDescriptionId IS NULL
        AND [productTotalTrigger].[ProductDescriptionId] IS NULL
        OR @ProductDescriptionId = [productTotalTrigger].[ProductDescriptionId]
    );

SELECT
    [productTotalTrigger].[RestGroupId] [ГруппаДоступности]
  , [productTotalTrigger].[QuantityFromStore] AS [КоличествоВСрезеCГруппамиДоступности]
  , [productTotalTrigger].[IsFromStore] AS [ЕстьВНаличииВСрезеСГруппамиДоступности]
  , CASE
        WHEN [productTotalTrigger].[IsFromStore] = 0
             AND [productTotalTrigger].[QuantityFromStore] > 0
             OR [productTotalTrigger].[IsFromStore] = 1
                AND [productTotalTrigger].[QuantityFromStore] = 0 THEN
            'Да'
        ELSE
            'Нет'
    END AS [Ошибка]
FROM
    [dbo].[SiteRestGroupRestsSummary2] [productTotalTrigger]
WHERE
    @ProductId = [productTotalTrigger].[ProductId]
    AND
    (
        @ProductDescriptionId IS NULL
        AND [productTotalTrigger].[ProductDescriptionId] IS NULL
        OR @ProductDescriptionId = [productTotalTrigger].[ProductDescriptionId]
    );

-- раскомментировать и запустить для перерасчета остатков
--EXEC [dbo].[RecalculateFromRetailRests];
--GO
--EXEC [dbo].[RecalculateFromStoreRests];
--GO
--EXEC [dbo].[RecalculateRestGroupSummary];

Проверка корректности пересчета среза цен

Если используется механизм подтяжки цен предложений поставщиков до собственных цен

SELECT
    [rs].[Id]
  , [rs].[ProductSearchArticle]
  , [rs].[ProductBrand]
  , [rs].[ProductId]
  , [rs].[ProductDescriptionId]
  , [rs].[Quantity]
  , [rs].[SortOrder]
  , [rs].[QuantityFromStore]
  , [rs].[QuantityInTransit]
  , [rs].[QuantityFromPrice]
  , [rs].[IsFromStore]
  , [rs].[IsInTransit]
  , [rs].[IsFromPrice]
  , [rs].[MinQuantity]
  , [rs].[MaxQuantity]
FROM
    [dbo].[SiteRestsSummary2] [rs]
WHERE
    [rs].[QuantityFromStore] > 0
    AND [rs].[IsFromStore] = 0
    OR [rs].[QuantityFromStore] = 0
       AND [rs].[IsFromStore] = 1
    OR [rs].[QuantityFromPrice] > 0
       AND [rs].[IsFromPrice] = 0
    OR [rs].[QuantityFromPrice] = 0
       AND [rs].[IsFromPrice] = 1
    OR [rs].[QuantityInTransit] > 0
       AND [rs].[IsInTransit] = 0
    OR [rs].[QuantityInTransit] = 0
       AND [rs].[IsInTransit] = 1;


SELECT
    *
FROM
    [dbo].[SiteRestGroupRestsSummary2] [rs]
WHERE
    [rs].[QuantityFromStore] > 0
    AND [rs].[IsFromStore] = 0
    OR [rs].[QuantityFromStore] = 0
       AND [rs].[IsFromStore] = 1
    OR [rs].[QuantityFromPrice] > 0
       AND [rs].[IsFromPrice] = 0
    OR [rs].[QuantityFromPrice] = 0
       AND [rs].[IsFromPrice] = 1
    OR [rs].[QuantityInTransit] > 0
       AND [rs].[IsInTransit] = 0
    OR [rs].[QuantityInTransit] = 0
       AND [rs].[IsInTransit] = 1;

При наличии ошибок в срезе необходимо выполнить пересчет остатков

EXEC [dbo].[RecalculateRestGroupSummary];
GO

Очистка остатков и отключение триггеров (перед полным обменом)

Позволяет очистить остатки и отключить триггеры (для ускорения полного обмена)

-- Выполнить на базе перед полным обменом ВСЕМИ остатками
-- очистка срезов и отключение индексов
TRUNCATE TABLE [dbo].[SiteRestGroupRests2];
ALTER INDEX ALL ON [SiteRestGroupRests2] DISABLE;
GO
TRUNCATE TABLE [dbo].[SiteRestGroupRestsSummary2];
ALTER INDEX ALL ON [SiteRestGroupRestsSummary2] DISABLE;
GO
TRUNCATE TABLE [dbo].[SiteRests2];
ALTER INDEX ALL ON [SiteRests2] DISABLE;
GO
TRUNCATE TABLE [dbo].[SiteRestsSummary2];
ALTER INDEX ALL ON [SiteRestsSummary2] DISABLE;
GO

-- очистка ВС
TRUNCATE TABLE [dbo].[SiteVirtualStorePriceLists]; -- закомментируйте эту строку, если не требуется полный обмен ВС
DISABLE TRIGGER ALL ON [SiteVirtualStorePriceLists]; -- закомментируйте эту строку, если не требуется полный обмен ВС
GO

-- очитска собственных остатков и отключение триггеров
TRUNCATE TABLE [dbo].[OrderingBuyers];
DISABLE TRIGGER ALL ON [OrderingBuyers];
GO
TRUNCATE TABLE [dbo].[OrdersToSuppliers];
DISABLE TRIGGER ALL ON [OrdersToSuppliers];
GO
TRUNCATE TABLE [dbo].[ArticlesInRetails];
DISABLE TRIGGER ALL ON [ArticlesInRetails];
GO
TRUNCATE TABLE [dbo].[ArticlesInStores];
DISABLE TRIGGER ALL ON [ArticlesInStores];
GO
TRUNCATE TABLE [dbo].[ToTransferArticlesFromStores];
DISABLE TRIGGER ALL ON [ToTransferArticlesFromStores];
GO
TRUNCATE TABLE [dbo].[ReservedArticlesInStores];
DISABLE TRIGGER ALL ON [ReservedArticlesInStores];

Включение триггеров и пересчет остатков (после полного обмена)

После полного обмена, если перед этим выключали триггеры

-- Выполнить на базе после полного обмена ВСЕМИ остатками
-- перерерасчет срезов
ALTER INDEX [PK_SiteRestGroupRests2] ON [SiteRestGroupRests2] REBUILD;
GO
ALTER INDEX [PK_SiteRestGroupRestsSummary2]
ON [SiteRestGroupRestsSummary2]
REBUILD;
GO
ALTER INDEX [PK_SiteRests2] ON [dbo].[SiteRests2] REBUILD;
GO
ALTER INDEX [PK_SiteRestsSummary2] ON [dbo].[SiteRestsSummary2] REBUILD;
GO
EXEC [dbo].[RecalculateFromPriceRests];
GO
EXEC [dbo].[RecalculateFromPricePrices];
GO
EXEC [dbo].[RecalculateFromRetailRests];
GO
EXEC [dbo].[RecalculateFromStoreRests];
GO
EXEC [dbo].[RecalculateInTransitRests];
GO
EXEC [dbo].[RecalculateInTransitPrices];
GO
EXEC [dbo].[RecalculateRestGroupSummary];
GO

-- включение триггеров
ENABLE TRIGGER ALL ON [OrderingBuyers];
GO
ENABLE TRIGGER ALL ON [OrdersToSuppliers];
GO
ENABLE TRIGGER ALL ON [ArticlesInRetails];
GO
ENABLE TRIGGER ALL ON [ArticlesInStores];
GO
ENABLE TRIGGER ALL ON [ToTransferArticlesFromStores];
GO
ENABLE TRIGGER ALL ON [ReservedArticlesInStores];
GO
ENABLE TRIGGER ALL ON [SiteVirtualStorePriceLists];
GO

-- включение индексов
ALTER INDEX ALL ON [SiteRestGroupRests2] REBUILD;
GO
ALTER INDEX ALL ON [SiteRestGroupRestsSummary2] REBUILD;
GO
ALTER INDEX ALL ON [SiteRests2] REBUILD;
GO
ALTER INDEX ALL ON [SiteRestsSummary2] REBUILD;
GO
ALTER INDEX ALL ON [SiteVirtualStorePriceLists] REBUILD;
GO