Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Posts
    34

    Unanswered: S/b Easy: Previous Quarter

    I'm realtively new to DB2 although experienced in SQL Server.
    I'm trying to write a query to retrieve records for the previous quarter but am having trouble.

    I can do it for yesterday:
    where Date(timestampfield)=current date-1 Day

    Or last month:
    where Month(timestampfield)=Month(current date - 1 month) AND Year(timestampfield)=Year(Current date -1 month)

    or last year:
    where year(timestampfield)=Year(current date - 1 year)

    but when I follow that idea through:
    where quarter(timestampfield)=quarter(current date - 1 quarter)

    I get:An unexpected token "QUARTER" was found following "". Expected tokens may include: ") , ". SQLSTATE=42601

    So I look in my ref, looks like I cannot subtract quarters. How would any of you write that statement?
    TIA

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'd use a calendar table -- seriously -- unless you are certain your query is always going to be based on a calendar quarter consisting of three calendar months, and never have to work for a "fiscal quarter" as is found in many businesses, based on a 4-4-5 or 4-5-4 grouping of whole weeks, roughly aligned with the months, but where the odd calendar day like the 1st or 2nd or 30th or 31st sometimes is found in the adjacent week, to say nothing of the adjacent quarter

    and don't even get me started on week 54, an abominous accounting invention

    okay, if you want just calendar quarters, the next question is: do you mean whole quarters or just the previous three months? i mean, these two interpretations today (february 7th) are either nov-dec-jan (previous 3 months) or oct-nov-dec (last complete whole quarter, since we are currently in the jan-feb-mar quarter)

    so, which is it?


    rudy

  3. #3
    Join Date
    Oct 2002
    Posts
    34
    Calendar quarters as defined as three month periods
    1st - Jan, Feb,Mar
    2nd -Apr, May, Jun
    etc.

    <okay, if you want just calendar quarters, the next question is: do you mean whole quarters or just the previous three months? i <mean, these two interpretations today (february 7th) are either nov-dec-jan ( previous 3 months) or oct-nov-dec (last complete <whole quarter, since we are currently in the jan-feb-mar quarter)

    I guess that would be whole quarters. If the person wanted to run the query for the current quarter, s/b anything in JFM. If they wanted last quarter, s/b Oct,Nov,Dec of 02.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, the basic formula for a quarter is to take the month number, subtract 1, divide by 3 and throw away the remainder, then add 1

    so the current quarter is

    FLOOR((MONTH(CURRENT DATE)-1)/3)+1

    now, this gives you a number between 1 and 4

    we still have to work out some way of getting over year boundaries

    the technique we shall use is to multiply the year number by 4 and add the quarter

    thus, 2002 quarter 4, which is the most recent whole quarter, will get the number 2002*4+4 = 8012

    now, when we take the current quarter, 2003 quarter 1, we get 2003*4+1 = 8013

    this confirms the formula

    therefore to find rows which have a date in the "somedate" field that is within the last whole quarter, we have

    where
    year(CURRENT DATE) * 4
    + FLOOR((MONTH(CURRENT DATE)-1)/3)+1
    - year(somedate) * 4
    + FLOOR((MONTH(somedate)-1)/3)+1
    = 1

    simple, no?


    rudy

  5. #5
    Join Date
    Oct 2002
    Posts
    34
    Wow. That's some very good stuff. I'm trying it out and having weirdness implementing it. When I first ran the query that way, I got 0 rows although I knew there are records from Q4 2002.

    So I tried Selecting the values & subtracting to see what was up like this:

    select year(CURRENT DATE) * 4+ FLOOR((MONTH(CURRENT DATE)-1)/3)+1,
    year(row_added_dttm) * 4 + FLOOR((MONTH(row_added_dttm)-1)/3)+1,
    year(CURRENT DATE) * 4+ FLOOR((MONTH(CURRENT DATE)-1)/3)+1-year(row_added_dttm) * 4 + FLOOR((MONTH(row_added_dttm)-1)/3)+1

    and the results were like this:

    8013 | 8012 | 9
    8012 | 8012 | 2

    So instead of getting a difference of 1, I'm getting a difference of 9. I'm willing to live with that, since it's consistently 9. I just wonder why the subtraction is doing that.
    I'm using DB2 v6 on OS390, if that makes a difference.

    Either way, thank you, thank you thank you!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is my fault, i forgot parentheses!

    incorrect:

    select x1+x2 , y1+y2 , x1+x2 - y1+y2

    correct:

    select x1+x2 , y1+y2 , x1+x2 - ( y1+y2 )

    also correct:

    select x1+x2 , y1+y2 , x1+x2 - y1 - y2


    make sense?


    rudy

  7. #7
    Join Date
    Oct 2002
    Posts
    34
    Ok, great. Thanks for the follow-up and the help.

  8. #8
    Join Date
    Aug 2003
    Posts
    11

    Alternate approach

    Try this out:


    select *
    from TABLE1 A
    where datediff(qq, '01/01/1753', A.EFFDT) = datediff(qq, '01/01/1753', getdate()) - 1.

    This pulls all records from TABLE1 where the effdt falls within the previous qtr.

Posting Permissions

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