I have a tricky one.
I have a lookup table that servers as categories for net square feet (NSF), and each one has an integer min and a max.
I have a historical table for the lookup table because each year there are different expected percentages (how many events we expect to fall into each NSF category).
Now what I want to do is left join two counts from a view that contains information about the events. Each count would have it's own set of filters (or constraints on the result set, however you like to say it).
Initially my thought is to just join to the view two times, which is simple and intuitively you would think this would work.
Code:
SELECT nh.[year], n.sortOrder, COUNT(eh1.eventHistoryId) AS CurrentYearCnt, COUNT(eh2.eventHistoryId) AS PrevYearCnt
FROM nsfCategoryHistory nh
JOIN nsfCategory n ON n.nsfCategoryId = nh.nsfCategoryId
JOIN vwEventHistoryWithInterpolatedAndOutlierAdjusted eh1
ON eh1.[year] = nh.[year]
AND eh1.netSquareFeet > 0
AND eh1.NetSquareFeet IS NOT NULL
AND eh1.isConsumerEvent = 0
AND eh1.HasTwoYearsContinuousData = 1
JOIN vwEventHistoryWithInterpolatedAndOutlierAdjusted eh2
ON eh2.[year] = nh.[year]-1
AND eh2.netSquareFeet > 0
AND eh2.NetSquareFeet IS NOT NULL
AND eh2.isConsumerEvent = 0
AND eh2.HasTwoYearsContinuousData = 1
WHERE nh.[year] > 2000
GROUP BY nh.[year], n.sortOrder
Unfortunately it throws my counts way off, as if it were cross multiplying everything or something, and it makes the counts in both columns equal to each other for each row. This strikes me as very odd. Now I already got a work around from a very nice, helpful poster at sqlservercentral.com using a SUM() with a CASE inside of it. This works for this particular situation but I have other cases where it's not that simple, and I really need three or four joins to the same view to do more complex calculations such as percent change from one year to the next, and weighting.
The bottom line is, I need a way to be able to join multiple times to the same view/table, such that I can perform aggregate functions on each, and it has to be efficient.
Any advice or help anyone can offer would be greatly, greatly appreciated! Thanks in advance.
Join for two different counts on same view
