Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Unanswered: How To Apply Dynamic Window Between Dates

    Hi,

    How do I write an SQL that will give results based on a moving window based on a date range?
    For e.g. in the following table
    Code:
    KeyCol AmtCol DateCol
    1            10      2012-01-01
    2            20      2013-02-01
    3            30      2013-03-01
    4            40      2013-04-01
    5            10      2013-05-24
    6            20      2013-05-27
    7            10      2013-06-14
    8            12      2013-06-19
    for a given I want to produce the sum of AmtCol between the last month and the next following month.

    Expected Result
    Code:
    KeyCol AmtCol    DateCol          Ans
    1            10      2012-01-01     10
    2            20      2013-02-01     50
    3            30      2013-03-01     90
    4            40      2013-04-01     80
    5            10      2013-05-24     70
    6            20      2013-05-27     52
    7            10      2013-06-14     52
    8            12      2013-06-19     52
    Last edited by jerome.r; 06-06-13 at 06:41. Reason: Updated with more information and sample result

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    More informations are desirable.

    (1) DB2 version/release/fixpack and plaform OS.

    (2) More sample data,
    considering ...
    (2-1) More rows
    (2-2) are all DateCol first day of a month?
    (2-3) Are there more than a year data(rows)?
    (2-4) Are there more than one rows in a month?
    (2-5) Are there some months in that no data exists.

    (3) Expected results from the sample data in (2).
    Last edited by tonkuma; 06-06-13 at 06:39. Reason: Add (2-4), (2-5)

  3. #3
    Join Date
    Apr 2013
    Posts
    19
    Updated post. Hope this will suffice.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    for a given I want to produce the sum of AmtCol between the last month and the next following month.
    Expected Result

    Code:
    KeyCol AmtCol    DateCol          Ans
    1            10      2012-01-01     10  -- 10(1)
                                              /* number in parentheses is KeyCol */
    2            20      2013-02-01     50  -- 20(2) + 30(3)
    3            30      2013-03-01     90  -- 20(2) + 30(3) + 40(4)
    4            40      2013-04-01     80  -- 30(3) + 40(4) + 10(5)
                            /* Why not 100? -- 30(3) + 40(4) + 10(5) + 20(6) */
    5            10      2013-05-24     70  -- 40(4) + 10(5) + 20(6)
                            /* Why not  92? -- 40(4) + 10(5) + 20(6) + 10(7) + 12(8) */
    6            20      2013-05-27     52  -- 10(5) + 20(6) + 10(7) + 12(8)
                            /* Why not  92? -- 40(4) + 10(5) + 20(6) + 10(7) + 12(8) */
    7            10      2013-06-14     52  -- 10(5) + 20(6) + 10(7) + 12(8)
    8            12      2013-06-19     52  -- 10(5) + 20(6) + 10(7) + 12(8)

  5. #5
    Join Date
    Apr 2013
    Posts
    19
    I'm sorry. It is exactly as you have comprehended. What you have posted is the expected output. Thanks

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An idea is
    Code:
         , SUM(AmtCol)
              OVER( ORDER BY YEAR(DateCol) * 12 + MONTH(DateCol)
                    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
                  ) AS ans

  7. #7
    Join Date
    Apr 2013
    Posts
    19
    Can you please explain the SQL? I'm new to OLAP. Why have you used 'RANGE' and not "ROWS'?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

    ...
    ...

    window-aggregation-group-clause

    ...

    ROWS
    Indicates the aggregation group is defined by counting rows.

    RANGE
    Indicates the aggregation group is defined by an offset from a sort key.
    ...

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I was a little disappointed for her(him?) to post in Oracle forum too.
    http://www.dbforums.com/oracle/16935...ml#post6594021

    If (s?)he want to learn SQL more generally, (s)he may want to ask in
    ANSI SQL - dBforums

Posting Permissions

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