Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 21st, 2007, 06:37 PM
Friend of Wrox
Join Date: Feb 2006
Location: , , USA.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Default Join for two different counts on same view

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.

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding COUNTs fizzerchris SQL Server 2005 3 February 6th, 2007 05:16 PM
Getting Counts hugh@kmcnetwork.com SQL Language 2 October 27th, 2004 04:01 PM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
join two counts field erin SQL Language 2 October 29th, 2003 04:50 AM
Get Counts harpua Classic ASP Databases 3 June 10th, 2003 12:45 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.