[RESOLVED] [SQL Server] Using CTE to reuse aliases

[nmadd]

SQL Server 9.0.3068

I'm doing some calculations and aggregations on data in my query and I'd like to reuse the results in other calculations. Ex.: ReservesWithheld and DistributionPercentage below.

Instead of redoing the calculations when I want to use DistributionPercentage, I've done them in a CTE and then "reused" them in the query after the CTE. Is it acceptable to proceed like this with a CTE so I can again reference the aliases or is there a better way to do this? How is this approach performance wise?

use SalesData;
go
with SMSales as
(
    select
        s.JournalRef Period,
        s.ItemNumber,
        s.ItemDescription,
        s.CustomerNumber,
        s.CustomerName,
        sum(s.GrossQuantity) GrossUnits,
        sum(s.ReturnQuantity) ReturnUnits,
        sum(s.GrossAmount) GrossSales,
        sum(s.GrossAmount) * .3 ReservesWithheld,
        sum(s.ReturnAmount) ReturnSales,
        case
            when s.CustomerNumber = '0007520' or s.CustomerNumber = '6005019' or sum(s.GrossQuantity) = 0 then .1
            when sum(s.GrossAmount) / sum(s.GrossQuantity) > 5 then 0.15
            else .1
        end as DistributionPercentage
    from dbo.SalesByItem s
    where
        s.JournalRef = 'GPE_200903LM'
    group by
        s.JournalRef,
        s.ItemNumber,
        s.ItemDescription,
        s.CustomerNumber,
        s.CustomerName
)
select
    sm.Period,
    sm.ItemNumber,
    sm.ItemDescription,
    sm.CustomerNumber,
    sm.CustomerName,
    sm.GrossUnits,
    sm.ReturnUnits,
    sm.GrossUnits + sm.ReturnUnits NetUnits,
    sm.GrossSales,
    sm.ReservesWithheld,
    sm.ReturnSales,
    sm.GrossSales - sm.ReservesWithheld + sm.ReturnSales NetSales,
    case
        when sm.GrossUnits = 0 then 0
        else sm.GrossSales / sm.GrossUnits
    end as AvgWholesalePrice,
    sm.DistributionPercentage,
    (sm.GrossSales - sm.ReservesWithheld + sm.ReturnSales) * sm.DistributionPercentage DistributionFee
from SMSales sm
go

[GaryMazzone]

That is how I have used a CTE if needed in more then one place in the proc

[nmadd]

That is how I have used a CTE if needed in more then one place in the proc

Cool. I just wanted to make sure that this was a valid approach without any big performance problems if I need to reuse some calculations more than once.

Thanks Gary!