Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unhappy Unanswered: This IS "self-join" Friday, isn't it?

    Hi all...I'm still trying to figure out a query related to the topic I posted yesterday about the table scan...got past that...it was smoke and mirrors...

    Now...I have a table that looks like (essentially) this:
    BegDate (smalldatetime)
    EndDate (smalldatetime)
    PortfolioID (int)
    WklyPerPriceChg (float)

    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

    Here is my select:
    Code:
    SELECT TOP 100 PERCENT 
    			T.BegDate as StartDate,
    			T.EndDate as EndDate,
    			SP.WklyPerPriceChg as Sandp,
    			WR.WklyPerPriceChg as WeekRvw,
    			NA.WklyPerPriceChg as NewAm,
    			T.WklyPerPriceChg as T100
    FROM	dbo.WeeklyPortfolioIndex T (nolock) INNER JOIN
    	dbo.WeeklyPortfolioIndex WR (nolock) ON T.BegDate = WR.BegDate INNER JOIN
    	dbo.WeeklyPortfolioIndex NA (nolock) ON T.BegDate = NA.BegDate INNER JOIN
    	dbo.WeeklyPortfolioIndex SP (nolock) ON T.BegDate = SP.BegDate
    WHERE	((T.BegDate >= @FirstBegDate) AND
    	(T.EndDate <= @LastEndDate)) AND
    	SP.PortfolioID = 2  AND
    	WR.PortfolioID = 67 AND
    	NA.PortfolioID = 11 AND
    	T.PortfolioID = 90
    ORDER BY T.BegDate DESC
    I think you can disregard the date stuff that seems to be working.

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    OK...here is my count of portfolios in the table.

    Code:
    P_ID	Count
    2	3
    11	200
    67	187
    90	58
    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

    Here is my latest select, that returns the 58 rows...
    Code:
     SELECT 		T.BegDate as StartDate,
    			T.EndDate as EndDate,
    			--SP.WklyPerPriceChg as Sandp,
    			WR.WklyPerPriceChg as WeekRvw,
    			NA.WklyPerPriceChg as NewAm,
    			T.WklyPerPriceChg as IBD100
    FROM	dbo.WeeklyPortfolioIndex T (nolock) FULL OUTER JOIN
    	dbo.WeeklyPortfolioIndex WR (nolock) ON T.BegDate = WR.BegDate FULL OUTER JOIN
    	dbo.WeeklyPortfolioIndex NA (nolock) ON WR.BegDate = NA.BegDate --JOIN
    	--dbo.WeeklyPortfolioIndex SP (nolock) ON NA.BegDate = SP.BegDate
    WHERE T.PortfolioID = 90 AND
    	--SP.PortfolioID = 2 AND
    	WR.PortfolioID = 67 AND
    	NA.PortfolioID = 11
    order by T.begdate desc
    Last edited by TallCowboy0614; 06-11-04 at 19:13. Reason: had wrong joins copied from wrong window...
    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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    SELECT
       IBD.BegDate as StartDate
    ,  IBD.EndDate as EndDate
    ,  SP.WklyPerPriceChg as Sandp
    ,  WR.WklyPerPriceChg as WeekRvw
    ,  NA.WklyPerPriceChg as NewAm
    ,  IBD.WklyPerPriceChg as IBD100
       FROM dbo.WeeklyPortfolioIndex IBD (nolock)
       FULL OUTER JOIN dbo.WeeklyPortfolioIndex WR (nolock)
          ON IBD.BegDate = WR.BegDate
       FULL OUTER JOIN dbo.WeeklyPortfolioIndex NA (nolock)
          ON WR.BegDate = NA.BegDate
       FULL OUTER JOIN dbo.WeeklyPortfolioIndex SP (nolock)
          ON NA.BegDate = SP.BegDate
       WHERE (IBD.PortfolioID IS NULL OR IBD.PortfolioID = 90)
          AND (SP.PortfolioID IS NULL OR SP.PortfolioID = 2)
          AND (WR.PortfolioID IS NULL OR WR.PortfolioID = 67)
          AND (NA.PortfolioID IS NULL OR NA.PortfolioID = 11)
       ORDER BY ibd.begdate desc
    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Alas...still just returns my minimum 3 rows...something is amiss here...I think it's the devil at work Time to retrace my steps and start out simple from square one, I think.

    Thanks for the brain cells, Pat!
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Thumbs up I Love You, Man! ;)

    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 )

    That does EXACTLY what I need to do...

    Now I am gonna go do some reading and find out WHY...and add it to my arsenal...

    Still would like to know why my @#$&$(*#$! quad-inner-join didn't work...but I'll wrestle with that another day...now it's just a "principle" thing.

    I thank you guys sincerely (!!) for reading and responding...I look forward to the time I know enough that I can contribute more than a giggle or two!

    Thanks to you, and thanks to Pat for the time and effort!
    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

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TallCowboy0614
    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!

    Glad that it is working now anyway!

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, really, Pat. You had the right idea. I defer to you completely. Go on and collect your reward.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya know....I always wonder about California....

    I'll be bock!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Ya know....I always wonder about California....

    I'll be bock!
    No, no, no! It is a music fetish/psychosis... I'll be Bach!

    -PatP

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ahhhh..Bach...

    Name the Show and character?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Terminator, as the original (Mark I) Terminator.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •