Congratulations for noting that derived tables need to be named: I'm sure I'm not alone in banging my head, initially trying to get them to work without.
My favorite simple use for derived tables is aggregating aggregates:
SELECT ReferralCount, COUNT(*) AS PatientCount
FROM (SELECT COUNT(*) AS ReferralCount FROM Referrals GROUP BY PatID) AS a
GROUP BY ReferralCount
ORDER BY ReferralCount
which is so much nicer having to create supplementary views / temporary tables.
A few things struck me: you say you want to "calculate current month [...] sales", but the query attempts to use the previous month. Unfortunately, it does so in a way that a @RunDate value in January fails to work. Actually, since the two derived tables are INNER JOINed, if there were no sales in the previous month, there will be no rows in the result.
I'm also slightly concerned that you've split the query into two subqueries, but at least the month has a non-sargable condition. In this case, you don't benefit from doing the two parts separately, and there's no need to since the GROUP BY's are both on Sto_Name.
SELECT b.Sto_Name,
COUNT(CASE WHEN DATEDIFF(mm, Sal_Date, @RunDate) = 1
THEN 1 ELSE NULL END) AS CM_Count,
SUM(CASE WHEN DATEDIFF(mm, Sal_Date, @RunDate) = 1
THEN Sal_Amt ELSE NULL END) AS CM_Sales,
COUNT(CASE WHEN DATEPART(yy, Sal_Date) = DATEPART(yy, @RunDate)
THEN 1 ELSE NULL END) AS YTD_Count,
SUM(CASE WHEN DATEPART(yy, Sal_Date) = DATEPART(yy, @RunDate)
THEN Sal_Amt ELSE NULL END) AS YTD_Sales
FROM Sales a JOIN Stores b ON a.Sto_ID = b.Sto_ID
GROUP BY b.Sto_Name
Course, this might be a bad idea if the WHERE clauses had used BETWEEN.
I also don't like US date-format, but that's just me.
Edited by - Arnold Fribble on 11/20/2001 04:24:10
Article: Using Derived Tables to Calculate Aggregate Values
Posted - 11/18/2001 : 21:42:35
