The below query is used to return a dataset for a SSRS report. It works fine and is quick enough but I am looking for any improvements that could be made.
The client has asked to see all transfers between sites and the report should look like:
- Site 2
- Site 3
- Site 1
- Site 3
Each row should also show the value of the transfer to that site and from that site. Duplicated data is expected I.E If Site 1 transferred goods to Site 2 then Site 2 should display the same value in the from column. If no transfer takes place the site should be visible but with a value of 0 in both To and from columns.
The Exists clause must remain as well as it is used to workout descendants of selected site.
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;
Subset DDL for CfgSites and Transfer View that only includes required fields
/****** 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