4
\$\begingroup\$

I've written a view to retrieve each product's sold quantity and four week sales average by Branch/Route/Customer.

Introduction to Dataset

  • It might be easiest to start with the innermost query (commented as B: SalesByWeek grouped by Branch.) since it's the base and the outer queries are the same query grouped by Route and Customer instead.
  • Branches contain one or many Routes.
  • Routes contain one or many Customers.
  • SAP_VisitPlan is an effectivity table. It defines which customers are on which routes and which routes are on which branches.
  • vw_SalesByWeek is two cache tables unioned to obtain all customer sales week totals.

The View

CREATE VIEW [dbo].[vw_SalesByWeekSummary]
AS
    SELECT VP.Branch
         , VP.ROUTE AS Route
         , VP.SAPCustomerID
         , S.SalesType
         , S.CustomerID
         , S.ProductID
         , S.Date
         , SUM(Quantity) AS CustomerQuantity
         , SUM(FourWeekSalesAvg) AS CustomerFourWeekSalesAvg
         , R.RouteQuantity
         , R.RouteFourWeekSalesAvg
         , R.BranchQuantity
         , R.BranchFourWeekSalesAvg
    FROM vw_SalesByWeek AS S WITH (NOLOCK)
    INNER JOIN SAP_VisitPlan AS VP WITH (NOLOCK)
        ON VP.CustomerID = S.CustomerID
       AND VP.DateFrom <= S.Date
       AND VP.DateTo >= S.Date
    INNER JOIN (

        -- R: SalesByWeek grouped by Route.
        SELECT VP.Branch
             , VP.ROUTE AS Route
             , S.ProductID
             , S.Date
             , SUM(Quantity) AS RouteQuantity
             , SUM(FourWeekSalesAvg) AS RouteFourWeekSalesAvg
             , B.BranchQuantity
             , B.BranchFourWeekSalesAvg
        FROM vw_SalesByWeek AS S WITH (NOLOCK)
        INNER JOIN SAP_VisitPlan AS VP WITH (NOLOCK)
            ON VP.CustomerID = S.CustomerID
           AND VP.DateFrom <= S.Date
           AND VP.DateTo >= S.Date
        INNER JOIN (

            -- B: SalesByWeek grouped by Branch.
            SELECT VP.Branch
                 , ProductID
                 , Date
                 , SUM(Quantity) AS BranchQuantity
                 , SUM(FourWeekSalesAvg) AS BranchFourWeekSalesAvg
            FROM vw_SalesByWeek AS S WITH (NOLOCK)
            INNER JOIN SAP_VisitPlan AS VP WITH (NOLOCK)
                ON VP.CustomerID = S.CustomerID
               AND VP.DateFrom <= S.Date
               AND VP.DateTo >= S.Date
            GROUP BY VP.Branch, ProductID, Date

        ) AS B
            ON B.Branch = VP.Branch
           AND B.ProductID = S.ProductID
           AND B.Date = S.Date
        GROUP BY VP.Branch, VP.ROUTE, S.ProductID, S.Date, B.BranchQuantity, B.BranchFourWeekSalesAvg

    ) AS R
        ON R.Branch = VP.Branch
       AND R.Route = VP.ROUTE
       AND R.ProductID = S.ProductID
       AND R.Date = S.Date
    GROUP BY VP.Branch, VP.ROUTE, VP.SAPCustomerID, S.SalesType, S.CustomerID, S.ProductID, S.Date, R.BranchQuantity, R.BranchFourWeekSalesAvg, R.RouteQuantity, R.RouteFourWeekSalesAvg
GO

vw_SalesByWeek Query

SELECT 'Conv' AS [SalesType]
     , [CustomerID]
     , [ProductID]
     , [WkStartDate] AS [Date]
     , [SoldQuantity] AS [Quantity]
     , [Route]
     , [FourWeekSalesAvg]
     , [NumberOfPriorSalesWeeks]
FROM Cache_ConvSalesByWeek WITH (NOLOCK)
UNION ALL
SELECT 'Scan' AS [SalesType]
     , [CustomerID]
     , [ProductID]
     , [WkStartDate]
     , [SoldQuantity]
     , [Route]
     , [FourWeekSalesAvg]
     , [NumberOfPriorSalesWeeks]
FROM Cache_ScanSalesByWeek WITH (NOLOCK)

Usage Query

SELECT Branch
     , Route
     , SAPCustomerID
     , SalesType
     , CustomerID
     , ProductID
     , Date
     , CustomerQuantity
     , CustomerFourWeekSalesAvg
     , RouteQuantity
     , RouteFourWeekSalesAvg
     , BranchQuantity
     , BranchFourWeekSalesAvg
FROM vw_SalesByWeekSummary
WHERE Route = '0600'
  AND Date = '08/06/2018'

Concerns

  • My concern is readability. I don't like that this query is basically the same query 3 times with different groupings for the sums.
  • I originally wrote this as three views that built on one-another. That is, one for the branch sums, one for the route sums, and one for the customer sums where each subsequent view used the prior. My co-workers did not like this as they fear a dependency-hell style scenario.
  • I have two other queries similar to this structurally, so any improvements that could be made will be incredibly helpful and appreciated.

Question

Is there a way to write this query in a more concise or conceptually simplistic way, possibly with less repetition?

\$\endgroup\$
  • \$\begingroup\$ What version of SQL Server are you using? \$\endgroup\$ – aduguid Aug 15 '18 at 23:39
  • \$\begingroup\$ SQL Server 2016 \$\endgroup\$ – Shelby115 Aug 15 '18 at 23:52
  • \$\begingroup\$ Can you post the source SQL for vw_SalesByWeek? \$\endgroup\$ – aduguid Aug 16 '18 at 0:06
  • \$\begingroup\$ I've added it to the question. vw_SalesByWeek is just a simple union between two tables that have sales for two different types of customers. \$\endgroup\$ – Shelby115 Aug 16 '18 at 12:41
2
\$\begingroup\$

I would test using the OVER clause with aggregate functions. This may speed up your results and make it a bit more readable. You may also want to check out the ranking functions.

Here is an example of your view I updated to use the OVER clause.

CREATE VIEW [dbo].[vw_SalesByWeekSummary]
AS

    SELECT 
           VP.[Branch]
         , [Route] = VP.[ROUTE]
         , VP.[SAPCustomerID]
         , S.[SalesType]
         , S.[CustomerID]
         , S.[ProductID]
         , S.[Date]
         , [CustomerQuantity] = SUM(S.[Quantity]) OVER (PARTITION BY VP.[Branch], VP.[ROUTE], VP.[SAPCustomerID], S.[SalesType], S.[CustomerID], S.[ProductID], S.[Date])
         , [CustomerFourWeekSalesAvg] = SUM(S.[FourWeekSalesAvg]) OVER (PARTITION BY VP.[Branch], VP.[ROUTE], VP.[SAPCustomerID], S.[SalesType], S.[CustomerID], S.[ProductID], S.[Date])
         , [RouteQuantity] = SUM(S.[Quantity]) OVER (PARTITION BY VP.[Branch], VP.[ROUTE], S.[ProductID], S.[Date]) 
         , [RouteFourWeekSalesAvg] = SUM(S.[FourWeekSalesAvg]) OVER (PARTITION BY VP.[Branch], VP.[ROUTE], S.[ProductID], S.[Date]) 
         , [BranchQuantity] = SUM(S.[Quantity]) OVER (PARTITION BY VP.[Branch], S.[ProductID], S.[Date]) 
         , [BranchFourWeekSalesAvg] = SUM(S.[FourWeekSalesAvg]) OVER (PARTITION BY VP.[Branch], S.[ProductID], S.[Date]) 
    FROM 
        vw_SalesByWeek AS S WITH (NOLOCK)
        INNER JOIN SAP_VisitPlan AS VP WITH (NOLOCK) ON VP.[CustomerID] = S.[CustomerID] AND VP.[DateFrom] <= S.[Date] AND VP.[DateTo] >= S.[Date] 


GO

I use Display Estimated Execution Plan Ctrl + L in Microsoft SQL Server Management Studio (SSMS) to check the performance of the query by pasting both SQL statements in a query window.

Here's an example.

screenshot

\$\endgroup\$
  • \$\begingroup\$ The PARTITION BY lines are throwing an error complaining about not being in the GROUP BY. Trying to figure this out on my own, but it's going to take a while since I've never used the OVER clause. \$\endgroup\$ – Shelby115 Aug 16 '18 at 12:48
  • 1
    \$\begingroup\$ I removed the GROUP BY and added an OVER clause to the Customer quantity and FourWeekSalesAvg and it seems to be working now. Going to experiment around with this a bit. \$\endgroup\$ – Shelby115 Aug 16 '18 at 12:53
  • 1
    \$\begingroup\$ Cool, that's exactly what I was thinking \$\endgroup\$ – aduguid Aug 16 '18 at 12:54
  • \$\begingroup\$ @aduguid What program do you use to test these queries with? \$\endgroup\$ – oliverbj Aug 16 '18 at 13:00
  • 2
    \$\begingroup\$ I didn't even know about the OVER clause, I've been missing out. Not only is my query more concise but it's performing on average 13x faster. I had two other similar queries to so that's a great deal of help to me. Thanks! \$\endgroup\$ – Shelby115 Aug 16 '18 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.