1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Generate sql weekend rows oracle

Discussion in 'SQL PL/SQL' started by karthik.rajashekaran, Aug 10, 2015.

  1. karthik.rajashekaran

    karthik.rajashekaran Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I am trying to write query in such a way i can insert weekend dates too

    Source query data is as below which doesn't have weekend dates
    Bond1.PNG


    Need output as below which shows in highlighted blue
    Bond2.PNG

    Any help is appreciated
     

    Attached Files:

  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Since you haven't provided the SQL you used to generate the first query, or any indication of the data that will be used to generate the weekend data, there is not much anyone can do to assist.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This appears to work:

    Code (SQL):
    SQL> SELECT *
      2  FROM securities
      3  UNION
      4  SELECT securityid, date_value+1, to_char(date_value+1, 'Day'), bondinterest
      5  FROM securities
      6  WHERE days = 'Friday'
      7  UNION
      8  SELECT securityid, date_value+2, to_char(date_value+2, 'Day'), bondinterest
      9  FROM securities
    10  WHERE days = 'Friday'
    11  ORDER BY 2 DESC
    12  /

    SECURITYID DATE_VALU DAYS         BONDINTEREST
    ---------- --------- ------------ ------------
           207 10-AUG-15 Monday          1.3333333
           207 09-AUG-15 Sunday          5.3333333
           207 08-AUG-15 Saturday        5.3333333
           207 07-AUG-15 Friday          5.3333333
           207 06-AUG-15 Thursday        6.6666667
           207 05-AUG-15 Wednesday               8
           207 04-AUG-15 Tuesday         9.3333333
           207 03-AUG-15 Monday         10.6666667
           207 02-AUG-15 Sunday         14.6666667
           207 01-AUG-15 Saturday       14.6666667
           207 31-JUL-15 Friday         14.6666667

    SECURITYID DATE_VALU DAYS         BONDINTEREST
    ---------- --------- ------------ ------------
           207 30-JUL-15 Thursday               16
           207 29-JUL-15 Wednesday      17.3333333
           207 28-JUL-15 Tuesday        18.6666667
           207 27-JUL-15 Monday                 20
           207 26-JUL-15 Sunday                 24
           207 25-JUL-15 Saturday               24
           207 24-JUL-15 Friday                 24
           207 23-JUL-15 Thursday       25.3333333
           207 22-JUL-15 Wednesday      26.6666667

    20 ROWS selected.

    SQL>