Repeat rows on calendar where joined table's start and end date are valid

• THE_BOZ

  SSC Journeyman

  Points: 95

  More actions

  September 11, 2014 at 9:19 am

  #290714

  Hello,

  I have a period table (month end calendar). What I would like to do is join another table and have its rows repeated for each period month end it was active for using its start and end date.

  SO period table is like:

  Period Date

  1 2014-01-01

  2 2014-02-01

  3 2014-03-01

  4 2014-04-01

  5 2014-05-01

  Information table is like:

  Issue Expiry Name Type Description

  2014-02-01 2014-04-01 Test FRN Investment

  The result I would like is

  Period Issue Expiry Name Type Description

  2014-02-01 2014-02-01 2014-04-01 Test FRN Investment

  2014-03-01 2014-02-01 2014-04-01 Test FRN Investment

  2014-04-01 2014-02-01 2014-04-01 Test FRN Investment

  The date is the only thing I have to join on.

  Many thanks!

• THE_BOZ

  SSC Journeyman

  Points: 95

  More actions

  September 12, 2014 at 3:51 am

  #1745013

  hi ya ,

  i worked out how to do it.

  on the join, i used 'between'

  ie. on period between issue date and expiry date.