Запрос для отображения каждого сайты перемещение запаса между всех других сайтов.


Приведенный ниже запрос возвращает набор данных для отчета SSRS. Он хорошо работает и достаточно быстро, но я ищу любые улучшения, которые могут быть сделаны.

Клиент попросил посмотреть все переводы между сайтами и отчет должен выглядеть так:

Сайт 1

  • Сайт 2
  • Сайт 3

Сайт2

  • Сайте 1
  • Сайт 3

Каждый ряд следует также показать значение перехода на этот сайт и с этого сайта. Дублируются данные, как ожидается, т. е. если 1 Сайт передан товар на 2 сайта, то сайт 2 должен показывать одинаковые значения в столбце. Если переключения не происходит на сайте должны быть видны, но со значением 0 в обоих столбцах.

Существует статья должна остаться, а также он используется для тренировки потомков выбранного узла.

WITH Transfers_CTE(FromSiteNo
                  ,ToSiteNo
                  ,Value)
     AS (
     SELECT Transfers.FromSiteNo
           ,Transfers.ToSiteNo
           ,SUM(Transfers.[Value]) AS Value
     FROM dbo.Transfers
     WHERE Transfers.MoveDate BETWEEN @SessionDateFrom AND @SessionDateTo
     GROUP BY Transfers.FromSiteNo
             ,Transfers.ToSiteNo)
     SELECT CS.No AS SiteNo
           ,CS.Name AS SiteName
           ,JoinedSites.No AS OtherSiteNo
           ,JoinedSites.Name AS OtherSiteName
           ,ISNULL(ToTable.[Value],0) AS ToValue
           ,ISNULL(FromTable.[Value],0) AS FromValue
     FROM dbo.CfgSites AS CS
          FULL OUTER JOIN dbo.CfgSites AS JoinedSites ON CS.No != JoinedSites.No
          LEFT JOIN Transfers_CTE AS ToTable ON ToTable.FromSiteNo = CS.No
                                                AND ToTable.ToSiteNo = JoinedSites.No
          LEFT JOIN Transfers_CTE AS FromTable ON FromTable.ToSiteNo = CS.No
                                                  AND FromTable.FromSiteNo = JoinedSites.No
     WHERE EXISTS
(
    SELECT DescendantSites.Descendant
    FROM dbo.DescendantSites
    WHERE DescendantSites.Parent IN(@SiteNo)
    AND DescendantSites.Descendant = CS.No
)
     ORDER BY CS.No;

Подмножество DDL для CfgSites и передачи посмотреть, что включает только обязательные поля

/****** Object:  Table [dbo].[CfgSites]    Script Date: 13/04/2018 08:47:32 ******/

CREATE TABLE [dbo].[CfgSites](
    [No] [smallint] NOT NULL,
    [Name] [varchar](50) NULL)
GO
/****** Object:  View [dbo].[Transfers]    Script Date: 13/04/2018 08:47:32 ******/

CREATE VIEW [dbo].[Transfers]
AS
SELECT [SiteNo] AS FromSiteNo
    ,[Site2No] AS ToSiteNo
    ,[Date] AS MoveDate
    ,[Value]
FROM PluMovement
WHERE MoveType = 4
    AND Processed = 1


112
0
задан 12 апреля 2018 в 11:04 Источник Поделиться
Комментарии
1 ответ

Вот это попытка переписать, но вы не предоставили никаких DDL или DML так что нет никакого способа для меня, чтобы проверить его. Я перечислю причины изменений ниже.

WITH Transfers_CTE (FromSiteNo, ToSiteNo, Value) AS
(
SELECT Transfers.FromSiteNo,
Transfers.ToSiteNo,
SUM( Transfers.Value ) AS Value
FROM dbo.Transfers
WHERE Transfers.MoveDate BETWEEN @SessionDateFrom AND @SessionDateTo
AND FromSiteNo <> ToSiteNo
GROUP BY Transfers.FromSiteNo,
Transfers.ToSiteNo
)
SELECT FromCS.No AS SiteNo,
FromCS.Name AS SiteName,
ToCS.No AS OtherSiteNo,
ToCS.Name AS OtherSiteName,
ISNULL( Transfers.Value, 0 ) AS ToFromValue
FROM dbo.CfgSites AS FromCS
LEFT JOIN Transfers_CTE AS Transfers
ON FromCS.No = Transfers.FromSiteNo
LEFT JOIN dbo.CfgSites AS ToCS
ON Transfers.ToSiteNo = ToCS.No
WHERE EXISTS
( SELECT DescendantSites.Descendant
FROM dbo.DescendantSites
WHERE DescendantSites.Parent IN ( @SiteNo )
AND DescendantSites.Descendant = FromCS.No)
ORDER BY FromCS.No
;


  1. Так Cs.No и JoinedSites.No не должны быть равными, и они присоединились к От & До значения ТКЛР, я добавил фильтр в КТР, чтобы исключить любые передачи, где и с сайта совпадают.

  2. Ваше полное внешнее соединение было эффективно левое внешнее соединение, потому что никакого результата от правой стороны стола был бы исключен левой стороны привязка существует пункта.

  3. Попытке присоединиться к переводы на сайтах с обоих направлений. Однако, если А = Б, то Б = А. Итак, если мы получаем все FromSite присоединяется созданная для CS на первом проходе, то нет смысла делать второй проход путем присоединения JoinedSites к FromSite как они уже нашли матч. Это изменение также устраняет ближайшее декартово произведение вступления CfgSites для себя.

  4. Если мы будем рассматривать CS таблице наш ЯКОРЬ, понимая это все с сайтов, то наша проблема будет как получить на сайтах. Отношения между и содержится в Transfers_CTE. Таким образом нам нужно просто вступить обратно CfgSites С помощью ToSiteNo поле для поиска информации.

  5. Совокупная сумма на зерно переводят отношения, и от. Это сохраняется в изменении, но в настоящее время существует только один столбец значение, возвращая значение, которое было передано от и до.

Нет необходимости, чтобы присоединиться CfgSites для себя сразу, если вам также может понадобиться, чтобы показать любой из комбинаций, которые не получили никаких переводов.

Надеюсь, это вернет ваши ожидаемые результаты. Если нет, то предоставить некоторые инструкции DDL/DML и я могу еще раз взглянуть на него.

0
ответ дан 12 апреля 2018 в 03:04 Источник Поделиться