Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Question Unanswered: Rank/order derivation question

    OOPS Sudden inspiration - got it now


    Guys
    Spent all morning on this - hope someone can help
    I've got some select code :


    declare @BusinessDate DATETIME
    set @BusinessDate = '28 Feb 2005'

    select
    --?? as period
    tdbe.eventid , tdbe.tradeid ,tdbe.cashflowstartdate
    from HDD_t_HA HA
    join hdd_t_Hedge_Instrument HI on HA.HAId = HI.HAId
    join hdd_t_TDB_Event TDBE on HI.EventId = TDBE.EventId
    join hdd_t_TDB TDB on TDBE.TradeId = TDB.TradeId
    where ha.haid = 29 and
    ha.validfrom <= getdate() and ha.validto > getdate()
    and tdbe.effectivefrom <= @BusinessDate and tdbe.effectiveto > @BusinessDate
    and tdb.effectivefrom <= @BusinessDate and tdb.effectiveto > @BusinessDate
    --and tdbe.tradeid = 117
    order by tdbe.tradeid , cashflowstartdate


    Which produces :
    eventid tradeid cashflowstartdate
    121 10 2004-04-19 00:00:00.000
    122 10 2004-07-19 00:00:00.000
    123 10 2004-10-19 00:00:00.000
    124 10 2005-01-19 00:00:00.000
    125 10 2005-04-19 00:00:00.000
    126 10 2005-07-19 00:00:00.000
    127 10 2005-10-19 00:00:00.000
    128 10 2006-01-19 00:00:00.000
    6810 117 2004-11-16 00:00:00.000
    6811 117 2005-05-16 00:00:00.000
    6812 117 2005-11-16 00:00:00.000
    6813 117 2006-05-16 00:00:00.000
    6814 117 2006-11-16 00:00:00.000
    6815 117 2007-05-16 00:00:00.000
    6816 117 2007-11-16 00:00:00.000
    6817 117 2008-05-16 00:00:00.000
    6818 117 2008-11-16 00:00:00.000
    6819 117 2009-05-16 00:00:00.000
    6820 117 2009-11-16 00:00:00.000
    6821 117 2010-05-16 00:00:00.000
    6822 117 2010-11-16 00:00:00.000
    6823 117 2011-05-16 00:00:00.000

    I'm trying to derive a 'period' value which is an integer based on the ordering of cashflowstartdatefor each tradeid group. The desired effect is as below

    Period eventid tradeid cashflowstartdate
    1 121 10 2004-04-19 00:00:00.000
    2 122 10 2004-07-19 00:00:00.000
    3 123 10 2004-10-19 00:00:00.000
    4 124 10 2005-01-19 00:00:00.000
    5 125 10 2005-04-19 00:00:00.000
    6 126 10 2005-07-19 00:00:00.000
    7 127 10 2005-10-19 00:00:00.000
    8 128 10 2006-01-19 00:00:00.000
    1 6810 117 2004-11-16 00:00:00.000
    2 6811 117 2005-05-16 00:00:00.000
    3 6812 117 2005-11-16 00:00:00.000
    4 6813 117 2006-05-16 00:00:00.000
    5 6814 117 2006-11-16 00:00:00.000
    6 6815 117 2007-05-16 00:00:00.000
    7 6816 117 2007-11-16 00:00:00.000
    8 6817 117 2008-05-16 00:00:00.000
    9 6818 117 2008-11-16 00:00:00.000
    10 6819 117 2009-05-16 00:00:00.000
    11 6820 117 2009-11-16 00:00:00.000
    12 6821 117 2010-05-16 00:00:00.000
    13 6822 117 2010-11-16 00:00:00.000
    14 6823 117 2011-05-16 00:00:00.000

    Hope this makes and thanks in advance
    Last edited by simonjw83; 12-02-05 at 09:08.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    well, I couldn't test it, as I don't have your tables and data. try this select and post issues have (if some):
    Code:
    select 
    	order_no = count(*),
    	s1.eventid , 
    	s1.tradeid , 
    	s1.cashflowstartdate
    from (
    	select
    	tdbe.eventid , tdbe.tradeid ,tdbe.cashflowstartdate
    	from HDD_t_HA HA
    	join hdd_t_Hedge_Instrument HI on HA.HAId = HI.HAId
    	join hdd_t_TDB_Event TDBE on HI.EventId = TDBE.EventId
    	join hdd_t_TDB TDB on TDBE.TradeId = TDB.TradeId
    	where ha.haid = 29 and 
    	ha.validfrom <= getdate() and ha.validto > getdate()
    	and tdbe.effectivefrom <= @BusinessDate and tdbe.effectiveto > @BusinessDate
    	and tdb.effectivefrom <= @BusinessDate and tdb.effectiveto > @BusinessDate
    	order by tdbe.tradeid , cashflowstartdate
         ) s1,
         (
    	select
    	tdbe.eventid , tdbe.tradeid ,tdbe.cashflowstartdate
    	from HDD_t_HA HA
    	join hdd_t_Hedge_Instrument HI on HA.HAId = HI.HAId
    	join hdd_t_TDB_Event TDBE on HI.EventId = TDBE.EventId
    	join hdd_t_TDB TDB on TDBE.TradeId = TDB.TradeId
    	where ha.haid = 29 and 
    	ha.validfrom <= getdate() and ha.validto > getdate()
    	and tdbe.effectivefrom <= @BusinessDate and tdbe.effectiveto > @BusinessDate
    	and tdb.effectivefrom <= @BusinessDate and tdb.effectiveto > @BusinessDate
    	order by tdbe.tradeid , cashflowstartdate
         ) s2
    where s1.eventid >= s2.eventid
    group by s1.eventid , s1.tradeid , s1.cashflowstartdate
    order by 1

Posting Permissions

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