If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I am trying to come up with a query that returns a table that looks like this:
BegDate, EndDate, WklyPerPriceChg for PortfolioID1, WklyPerPriceChg for PortfolioID2, WklyPerPriceChg for PortfolioID3, WklyPerPriceChg for PortfolioID4

What I am finding though, is that the self-join only returns rows for dates that have ALL FOUR of the target portfolioID's. What I want is a row for any day that has AT LEAST ONE of the portfolios, and would like NULL or zero returned in the columns for any portfolioID that does NOT have a row present in the table for that date(range).

I thought I would try a FULL OUTER join rather than the inner one (which I would expect to return an output row if ALL portfolio rows were present for that day)...but that returns more of a cartesian product type thang rather than what I am looking for.

I further thought I need something like a GROUP BY T.BegDate, for example, but then it complains that my select columns are not aggregates.

I know this should be fairly easy...I'm just missing the boat by mere inches, I think...

And missing by "mere inches" is still enough to leave me all wet
Thoughts?
Thanks!

aka "Paul"Non est ei similis.

I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

The INNER JOIN is what is killing you. If you are sure that you'll have one portfolio for everything that interests you, just use that as your base table and do LEFT OUTER JOIN for all the other tables. If you don't have a "boat anchor", you'll probably have to do FULL OUTER JOIN on each table, which may cost a lot from the performance perspective.

Seems no matter what I do, I can only get 3 rows back from my select - obviously because of the limit of 3 with respect to the portfolioID '2'

I tried performing the select with LEFT OUTER and FULL OUTER, and STILL, I only get 3 rows returned.

Yet, when I remove the "WHERE SP.PortfolioID = 2" from the where clause, then I get a total of 58 rows returned (the next-restrictive number of that a portfolio-of-interest in the table). I need to think on this...it doesn't make sense to me...but I'm doing something wrong with my "where" clause I think...

The only function it has is to identify the portfolios of interest (one for each column in the desired output row) - which is, as you will recall:
startdate, enddate, P2, P11, P67, P90

I'm sorry, I didn't explain that one very well. The problem that you are hitting now is that in a FULL OUTER JOIN, if the join condition fails then all of the columns for that alias are NULL. Because NULL doesn't equal anything (even another NULL), the original WHERE condition wwould eliminate the candidate row from the result set. You need to use something that will tolerate the NULL values, like:

I think you can do what you want by using a crosstab query, without any self-joins at all:

SELECT
BegDate as StartDate,
EndDate as EndDate,
sum(CASE when PortfolioID = 2 then WklyPerPriceChg end) as SandP,
sum(CASE when PortfolioID = 67 then WklyPerPriceChg end) as WeekRvw,
sum(CASE when PortfolioID = 57 then WklyPerPriceChg end) as NewAm,
sum(CASE when PortfolioID = 90 then WklyPerPriceChg end) as IBD100
FROM dbo.WeeklyPortfolioIndex
GROUP BY BegDate, EndDate
ORDER BY begdate desc

You need some sort of aggregate function for the CASE statements. You could use MAX() instead, if you prefer.

Why...if'n I wasn't already married...
(well, and there's that whole outie-outie thing that probably is best discussed on a different forum )

Whew! Glad I didn't solve that one

Now that I look at it, I understand the problem, which lies in the ON conditions (due to multiple FULL OUTER JOIN operations, not all of the values used in the ON clauses will be non-NULL). Blindman's cross-tab query solves the problem neatly by doing the crosstab instead!