1
\$\begingroup\$

I am trying to use the data from one table to produce the result set | Dept | TotalValue | PercentageOfSite | PercentageOfCompany |

Any improvements or advice on the below would be helpful.

DECLARE @SiteNo INT = 1
DECLARE @SessionDateFrom DATETIME = '2017/02/01'
DECLARE @SessionDateTo DATETIME = '2017/03/01'

    CREATE TABLE #DepartmentSales
(
    DeptName VARCHAR(50), 
    DeptNo SMALLINT,
    Value MONEY,
    SiteNo SMALLINT
)

INSERT INTO #DepartmentSales
SELECT DeptName
    ,DeptNo
    ,SUM(Value) AS SalesValue
    ,SiteNo
FROM PLUSalesDetailExtended
WHERE DATE BETWEEN @SessionDateFrom AND @SessionDateTo
AND TermNo = 0
GROUP BY DeptName, DeptNo, SiteNo

SELECT DeptName
    ,DeptNo
    ,Value AS SalesValue
    ,Value / (SELECT SUM(Value) FROM #DepartmentSales WHERE SiteNo = @SiteNo) AS SitePercent
    ,Value / (SELECT SUM(Value) FROM #DepartmentSales) AS CompanyPercent

FROM #DepartmentSales
WHERE SiteNo = @SiteNo
GROUP BY DeptName
    ,DeptNo
    ,Value

If(OBJECT_ID('tempdb..#DepartmentSales') Is Not Null)
Begin
    Drop Table #DepartmentSales
End

Any further questions please ask and I shall try to answer them.

\$\endgroup\$
0
\$\begingroup\$

Adding the same GROUP BY in the 2nd Select is unneccessary, the data is already unique.

I prefer writing this kind of logic (where you need multiple aggregation levels) as a single Select using Analytical Functions, i.e. Group Sums:

WITH AllSites AS
 (  -- calculate the percentages for all sites
    SELECT DeptName 
        ,DeptNo
        ,SUM(Value) AS SalesValue
        ,SiteNo
    -- Analytical Functions are calculated after GROUP BY/HAVING,
    -- thus you can nest the aggregated Sum in the Group Sum  
        ,SUM(Value) / SUM(SUM(Value)) OVER (PARTITION BY SiteNo) AS SitePercent
        ,SUM(Value) / SUM(SUM(Value)) OVER () AS CompanyPercent
    FROM PLUSalesDetailExtended
    WHERE DATE BETWEEN @SessionDateFrom AND @SessionDateTo
    AND TermNo = 0

    GROUP BY DeptName, DeptNo, SiteNo
 )
-- return only one site
SELECT * FROM AllSites
WHERE SiteNo = @SiteNo

If this is more or less efficient than your current approach depends mainly on he number of sites, at least it's simpler (for me) and easy to extend to multiple/all sites.

|improve this answer|||||
\$\endgroup\$

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.