CODEWITHSUNDEEP
sqlsql-servert-sql
S.Mason
S.Mason

Reputation: 3647

How to re-use complicated formulas multiple times in the query

I have a query with a couple of semi-complicated formulas. I've removed a lot of extra bits and boiled it down to what is relevant to my question.

DECLARE @Company              AS NVARCHAR(8) = 'Acme'
                  , @Plant                AS VARCHAR(20)  = 'Albuquerque'
                  , @Warehouse            AS NVARCHAR(8) = 'TNT-WH1'
                  , @DaysAhead            AS INT         = 10
                  , @FulfillmentThreshold AS INT         = 90;

            SELECT
                @Company AS 'Company'                    
                , orel.SellingReqQty
                , orel.OurJobShippedQty + orel.OurStockShippedQty AS 'ShippedQty'
                , orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty) AS 'ReqQtyRemaining'
                , CASE
                    WHEN ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                            / (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) * 100)
                            >= 100 THEN '100'
                    ELSE
                        ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                            / (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) * 100)
                END
                AS 'PercentAvailToFulfill'

            FROM
                dbo.OrderRel AS orel WITH (NOLOCK)

                INNER JOIN erp.PartWhse AS pw WITH (NOLOCK)
                    ON (pw.Company = orel.Company AND
                        pw.PartNum = orel.PartNum AND
                        pw.WarehouseCode = @Warehouse)

            WHERE
                orel.Company = @Company                
            AND orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty) > 0 -- ReqQtyRemaining
             AND (orel.ReqDate <= DATEADD(DAY, @DaysAhead, GETDATE())
                  OR
                  orel.RushPart_c = 1 )

            AND ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                / (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) * 100)
                > @FulfillmentThreshold

            AND (orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)) > (pw.AllocatedQty + pw.ReservedQty)

As you can see this bit of code which represents a remaining qty is repeated many times but is necessary to calculate for each record returned:

orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty)

I'm looking for a way to make this more readable by changing that into a single variable. Is it possible to set that as a variable calculated for each row so I can refer to it as @ReqQtyRemaining?

Upvotes: 1

Views: 279

Answers (1)

Emin Mesic
Emin Mesic

Reputation: 1811

In your case if all these columns are in the same table you can use COMPUTED column.

So, you just need to modify your table structure to add new computed column.

ALTER TABLE dbo.OrderRel ADD ReqQtyRemaining AS (SellingReqQty - OurJobShippedQty + OurStockShippedQty)

Once you create a computed column this column as a name said will be always automatically updated and it can be used in the query as all other columns, you just cannot update this column manually.

Then you don't need to do the calculation in the queries and the big thing that you can put an index on this column as well.

More information here: https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

If you are unable to change table structure you can try with CTE (Common Table Expressions). However, you will not see any specific performance improvement, you will just improve readability and reusability of the same code. So, then your query should look like this:

DECLARE @Company                AS NVARCHAR(8)  = 'Acme'
        , @Plant                AS VARCHAR(20)  = 'Albuquerque'
        , @Warehouse            AS NVARCHAR(8)  = 'TNT-WH1'
        , @DaysAhead            AS INT          = 10
        , @FulfillmentThreshold AS INT          = 90;

;WITH OrderRelWithComputedCTE AS
(
    SELECT *, (SellingReqQty - OurJobShippedQty + OurStockShippedQty) ReqQtyRemaining
    FROM dbo.OrderRel WITH (NOLOCK)
)
SELECT
    @Company AS 'Company'                    
    , orel.SellingReqQty
    , orel.OurJobShippedQty + orel.OurStockShippedQty AS 'ShippedQty'
    , orel.ReqQtyRemaining
    , CASE
        WHEN ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                / orel.ReqQtyRemaining * 100)
                >= 100 THEN '100'
        ELSE
            ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
                / orel.ReqQtyRemaining * 100)
    END
    AS 'PercentAvailToFulfill'

FROM
    OrderRelWithComputedCTE AS orel
    INNER JOIN erp.PartWhse AS pw WITH (NOLOCK)
               ON (pw.Company = orel.Company AND
                   pw.PartNum = orel.PartNum AND
                   pw.WarehouseCode = @Warehouse)
WHERE
    orel.Company = @Company                
AND orel.SellingReqQty - (orel.OurJobShippedQty + orel.OurStockShippedQty) > 0 -- ReqQtyRemaining
 AND (orel.ReqDate <= DATEADD(DAY, @DaysAhead, GETDATE())
      OR
      orel.RushPart_c = 1 )

AND ((pw.OnHandQty - (pw.AllocatedQty + pw.ReservedQty))
    / orel.ReqQtyRemaining * 100)
    > @FulfillmentThreshold

AND orel.ReqQtyRemaining > (pw.AllocatedQty + pw.ReservedQty)

Pay attention on CTE command at the top of the SELECT statement and on this line:

FROM OrderRelWithComputedCTE AS orel

Upvotes: 1

Related Questions