Home Forums SQL Server 2005 T-SQL (SS2K5) TSQL Return start/end price and start/end datetime by product name. RE: TSQL Return start/end price and start/end datetime by product name.

  • June 1, 2010 at 9:26 am

    #1174847

    After looking into use of CROSS & OUTER APPLY (my curiosity was influenced by Paul White NZ who suggested more efficient solution for one of the last topics...)

    I can suggest the following query (it will return record for every price change occurance):

    select bs.Name, bs.Price as StartPrice, bs.Date as StartDT, etp.NextPrice, etp.EndDT

    from #Temp bs

    outer apply (select top 1 et.Name

    ,et.Price as NextPrice

    ,et.Date as EndDt

    from #Temp et where et.Name = bs.Name and et.Date > bs.Date

    ) etp

    where bs.Price != etp.NextPrice or etp.NextPrice is null

    order by bs.Name, bs.Date

    I've started to like APPLY thing, thanks to Paul White NZ!

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]