Results 1 to 5 of 5
  1. #1
    Join Date
    May 2014
    Posts
    1

    Unanswered: Help with looping

    Hi All,
    I have basic idea of writing sql's.My database reords looks as below

    PRICE STARTDATE ENDDATE

    3 2/04/2012 4/04/2012
    2 5/04/2012 8/04/2012

    I want an out as below

    DATE PRICE
    2/04/2012 3
    3/04/2012 3
    4/04/2012 3
    5/04/2012 2
    6/04/2012 2
    7/04/2012 2
    8/04/2012 2

    Basically I want to find out the price on a daily basic.In ORACLE we have the option of using rownum to achive this.Can anyone help me in achieving this.
    Any leads will be highly appreaciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The simplest and most portable way to do this is to use a calendar or date table so that the solution becomes a simple join operation on any version of SQL. There are also ways to do this using a cte (a Common Table Expression), but compared to a calendar table these work-arounds use a lot of CPU and memory so they don't scale well.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    rownum is also available in db2 but has to be activated
    ROWNUM pseudocolumn
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    with t (price, startdate, enddate) as (
    select price, startdate, enddate
    from mytable
      union all
    select price, startdate + 1 day, enddate
    from t
    where startdate < enddate
    )
    select startdate, price
    from t
    order by startdate
    Regards,
    Mark.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you can access the employee table:

    Code:
    SELECT /*REPLACE(CHAR(*/ startdate + n DAYs /*, EUR), '.' , '/')*/ AS date
         , price
     FROM  mytable
     INNER JOIN
           (SELECT ROW_NUMBER() OVER() - 1 AS n
             FROM  employee
           )
      ON   n <= DAYS(enddate) - DAYS(startdate)
     ORDER BY
           date
    ;
    or replace the employee table with other table having enough number of rows.

Posting Permissions

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