Post reply

want to remove subquery from join?

  • September 27, 2011 at 7:18 am

    #248779

    Hi this is my query which is giving me result.

    But i need to format my query i want to remove sub query from JOIN statement as my 2 select statements are selecting data from same table

    How can i use that alias ?please help

    declare @StartDate datetime= '8/01/2011'

    declare @EndDate datetime = '9/20/2011'

    declare @PortfolioId int = 6

    SELECT

    Previous.PreviousNAV,

    Todays.TodaysNAV,

    ISNULL((Todays.TodaysNAV-Previous.PreviousNAV),0) NetChange,

    ISNULL((Todays.TodaysNAV-Previous.PreviousNAV) / Todays.TodaysNAV,0) BAMLIndex

    FROM

    (

    SELECT PortfolioId,ISNULL(NAV,0) PreviousNAV

    FROM Fireball..NAV

    WHERE Date BETWEEN @StartDate AND @EndDate and PortfolioId = @PortfolioId

    ) Previous

    JOIN

    (

    SELECT PortfolioId,ISNULL(NAV,0) TodaysNAV

    FROM Fireball..NAV

    WHERE Date = @EndDate and PortfolioId = @PortfolioId

    ) Todays

    ON Previous.PortfolioId = Todays.PortfolioId

  • September 27, 2011 at 7:32 am

    #1387146

    Since you're looking for two different sets of data from that table, you will need to have it in the query twice. Whether as subqueries or in a join won't make much (if any) difference.

    Why are you looking to change this.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply