Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Thumbs up Unanswered: Query Sql - Day Count

    I HAVE A TABLE WITH, AMONG OTHERS, THE FIELDS DATE1 AND DATE2 IN WHICH
    DATA ARE IN THE FORMAT (20070417, ....) (THEY ARE NUMBERS AND NOT DATES!!!!!).
    I WANT TO CALCULATE THE DIFFERENCE BETWEEN DATE2 AND DATE1 ACCORDING
    TO THE 30/360 DAY COUNT (EVERY MONTH HAS THIRTY DAYS). HOW CAN I
    TRANSFORM THE NUMBERS INTO DATES AND THEN CALCULATE THE DIFFERENCE?
    PLEASE ANSWER TO ME AS SOON AS POSSIBLE.
    THANK YOU IN ADVANCE
    ANNA from Verona (Italy)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database? date functions vary wildly from one to the next
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    51

    date count

    Quote Originally Posted by r937
    which database? date functions vary wildly from one to the next
    DB2. I use QMF for Windows.

    Please help me doing my query.

    Anna - Verona Italy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thread moved to DB2 forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2006
    Posts
    471
    you have to convert integer to date to get yyyy-mm-dd
    eg
    select date(
    substr(char(col1),1,4) ||'-'||substr(char(col1),5,2) ||'-'||substr(char(col1),7,2) ) from tt;
    for both columns. dates can be compared/substracted... any operation. see online info center and date comparison
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    To get the year out of the numeric value you might use the formula
    TRUNC(DATE1/10000,0)

    the month you get with
    TRUNC(MOD(DATE1,10000)/100,0)

    and the day you get with
    MOD(DATE1,100)


    the difference of two dates can be calculated by

    difference = ( TRUNC(DATE2/10000,0) - TRUNC(DATE1/10000,0) ) *360 + ( TRUNC(MOD(DATE2,10000)/100,0) - TRUNC(MOD(DATE1,10000)/100,0) ) *30 + ( MOD(DATE2,100) - MOD(DATE1,100) )

  7. #7
    Join Date
    Apr 2007
    Posts
    51

    Thumbs up

    Quote Originally Posted by umayer
    To get the year out of the numeric value you might use the formula
    TRUNC(DATE1/10000,0)
    the month you get with
    TRUNC(MOD(DATE1,10000)/100,0)
    and the day you get with
    MOD(DATE1,100)
    the difference of two dates can be calculated by
    difference = ( TRUNC(DATE2/10000,0) - TRUNC(DATE1/10000,0) ) *360 + ( TRUNC(MOD(DATE2,10000)/100,0) - TRUNC(MOD(DATE1,10000)/100,0) ) *30 + ( MOD(DATE2,100) - MOD(DATE1,100) )
    Thank you for your reply. I like this formula because it's very short but
    unfortunately it doesnt' work. Everything is okay with years and months but
    days are not counted properly.
    I'd better explain to you how days are counted with 30/360 day count:
    each month you count each day as 1 but only up to 30
    in february you count each day as 1 and on the 28th (if it's a normal year - 365 days) you count 30; if it's a leap year (366 days) you count 28 on the
    28th and you count 30 on the 29th.
    Your formula doesn't take into account dates with day 31 or 28 or 29.
    (end date day 31 - beg.date day 30) = 1 which is wrong, it should be = zero
    (end date day 5 - beg. date day 31) = -26 but it should be -25
    I tried to imagine some selections or 'when' clauses that change 31, 29 and
    28 (the latter only if it's not a leap year - maybe you can say if 29/2/year doesn't exist) into 30, but I' not very expert at SQL and I'm not able to write
    it.
    Before knowing that there are differences among SQL's languages depending on the databases used, I asked SQLTEAM.COM for help and someone sent
    me this function:
    CREATE FUNCTION dbo.fnGiorno360
    (
    @Date1 INT,
    @Date2 INT
    )
    RETURNS INT
    AS
    BEGIN
    DECLARE @Months INT,
    @Days INT,
    @dt1 DATETIME,
    @dt2 DATETIME

    IF @Date1 > @Date2
    SELECT @Days = @Date1,
    @Date1 = @Date2,
    @Date2 = @Days

    SELECT @dt1 = CAST(@Date1 AS VARCHAR),
    @dt2 = CAST(@Date2 AS VARCHAR),
    @Months = DATEDIFF(MONTH, @dt1, @dt2)

    IF @Months > 0
    SET @Months = @Months - 1

    SELECT @Days = 30 - CASE WHEN DATEPART(DAY, @dt1) > 30 THEN 30 ELSE DATEPART(DAY, @dt1) END,
    @Days = @Days + CASE WHEN DATEPART(DAY, @dt2) > 30 THEN 30 ELSE DATEPART(DAY, @dt2) END

    RETURN @Days + 30 * @Months
    END
    Then use it with
    select dbo.fnGiorno360(20070331, 20070815)

    I think he didnt' think about february.
    Of course this function wouldn't work with my database because it's for
    SQL Server. Moreover, I myself can't insert functions in my queries because
    only programming staff can do that and I'm an accountant.

    Is there a hope to solve this problem?

    I have another question to make: once I have created a new column by
    making operations among db2 columns, how can I select my new column to
    make other operations? (When I try the answer is that column xxx is not present in my table).

    I hope to hear from you soon and thank you in advance.

    Anna - from Verona (Italy)

  8. #8
    Join Date
    Dec 2005
    Posts
    273
    try this to get the desired value for day:

    CASE
    WHEN MOD(DATE1,1000)= 229 THEN 30
    WHEN MOD(DATE1,1000)= 228 AND MOD(TRUNC(DATE1/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(DATE1,100),30)
    END

  9. #9
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by annamaria

    I have another question to make: once I have created a new column by
    making operations among db2 columns, how can I select my new column to
    make other operations? (When I try the answer is that column xxx is not present in my table).

    You can use a "nested table" expression.
    e.g.:

    SELECT (year2 - year1)*360 + (month2 - month1)*30 + (day2 - day1) as difference
    FROM
    (
    SELECT
    TRUNC(DATE1/10000,0) as year1 ,
    TRUNC(MOD(DATE1,10000)/100,0) as month1 ,
    CASE
    WHEN MOD(DATE1,1000)= 229 THEN 30
    WHEN MOD(DATE1,1000)= 228 AND MOD(TRUNC(DATE1/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(DATE1,100),30)
    END as day1,
    TRUNC(DATE2/10000,0) as year2 ,
    TRUNC(MOD(DATE2,10000)/100,0) as month2 ,
    CASE
    WHEN MOD(DATE2,1000)= 229 THEN 30
    WHEN MOD(DATE2,1000)= 228 AND MOD(TRUNC(DATE2/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(DATE2,100),30)
    END as day2
    FROM your.table
    ) correl

  10. #10
    Join Date
    Apr 2007
    Posts
    51

    date count

    Quote Originally Posted by umayer
    You can use a "nested table" expression.
    e.g.:
    SELECT (year2 - year1)*360 + (month2 - month1)*30 + (day2 - day1) as difference
    FROM
    (
    SELECT
    TRUNC(DATE1/10000,0) as year1 ,
    TRUNC(MOD(DATE1,10000)/100,0) as month1 ,
    CASE
    WHEN MOD(DATE1,1000)= 229 THEN 30
    WHEN MOD(DATE1,1000)= 228 AND MOD(TRUNC(DATE1/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(DATE1,100),30)
    END as day1,
    TRUNC(DATE2/10000,0) as year2 ,
    TRUNC(MOD(DATE2,10000)/100,0) as month2 ,
    CASE
    WHEN MOD(DATE2,1000)= 229 THEN 30
    WHEN MOD(DATE2,1000)= 228 AND MOD(TRUNC(DATE2/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(DATE2,100),30)
    END as day2
    FROM your.table
    ) correl
    Thak you so much. The formula works perfectly, even in leap years!
    With the query I'm making (that would be of no use without this formula) I will turn a three-days-per-month work into a five-minutes-per-month work.

    Could you explain to me what calculation is made in the expression
    MOD(TRUNC(DATE2/10000,0),4)? It seem to consider leap years but how does it do it? I couldn't find anything in my handbook.

    Another question:
    Can I only use one nested table or can I nest more tables one into another?
    i.e. select from xxxxxx
    from ( select xxxxx
    from (select xxxx
    from (select xxxxx
    If that's possible, how should I do it? I tried to but unsuccessfully.

    Thank you again and congratulations.
    Ciao. Anna

  11. #11
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by annamaria
    Could you explain to me what calculation is made in the expression
    MOD(TRUNC(DATE2/10000,0),4)? It seem to consider leap years but how does it do it? I couldn't find anything in my handbook.

    If the year is divisible by four without remainder, it is a leap year.

    The formula to get the year is:
    TRUNC(DATE2/10000,0)

    if the remainder of the division by four is not zero
    MOD(year,4) > 0
    then it is no leap year.

    now simply combine the two formulas:
    if MOD(TRUNC(DATE2/10000,0),4) > 0
    then it is no leap year.


    Quote Originally Posted by annamaria
    Another question:
    Can I only use one nested table or can I nest more tables one into another?
    There are up to 16 levels possible
    eg:
    SELECT * FROM
    ( SELECT * FROM
    ( SELECT * FROM
    ( SELECT * FROM SYSIBM.SYSDUMMY1 ) A
    ) B
    ) C

    you have to use a correlation character after each nested table expression
    Last edited by umayer; 04-20-07 at 03:48.

  12. #12
    Join Date
    Apr 2007
    Posts
    51

    Mysterious behaviour

    I'm having problems with some calculations:

    Here is the query:

    SELECT

    SEC, VER, DES, CURC, CALC, B_DATE, E_DATE, RATE, PER,
    GG_CED_C, GG_MAT_C,
    CASE WHEN (CALC = 801 OR CALC = 306 OR CALC = 307 OR CALC = 811
    OR CALC = 871) THEN (RATE/NR_CED)/GG_CED_S* GG_MAT_S
    WHEN CALC = 315 THEN RATE/GG_CED_S* GG_MAT_S
    WHEN CALC = 300 OR CALC = 301 OR CALC = 834 OR CALC = 822
    THEN RATE / 360 * GG_MAT_C

    WHEN CALC = 303 OR CALC = 304 OR CALC = 835
    THEN RATE/ 360 * GG_MAT_S
    WHEN CALC = 876 THEN ROUND((RATE/ 360 * GG_MAT_S),2)
    WHEN CALC = 877 OR CALC = 325 THEN
    ROUND(( RATE/ 360 * GG_MAT_S),3)
    WHEN CALC = 878 THEN ROUND((RATE/ 360 * GG_MAT_S),5)
    END AS RATEO

    FROM (
    SELECT SEC, VER, DES, CURC, CALC, B_DATE, E_DATE, RATE, PER,
    GG_CED_S, GG_MAT_S,NR_CED

    (YEAR2 - YEAR1)*360 + (MONTH2 - MONTH1)*30 + (DAY2 - DAY1)AS GG_CED_C,
    (YEARRIL - YEAR1)*360 + (MONTHRIL - MONTH1)*30 + (DAYRIL - DAY1)AS GG_MAT_C,

    FROM (
    SELECT
    A.SEC, A.VER, A.DES, CURC, CALC, B_DATE, E_DATE, RATE,
    PER,
    TRUNC(B_DATE/10000,0) AS YEAR1 ,
    TRUNC(MOD(B_DATE,10000)/100,0) AS MONTH1 ,
    CASE
    WHEN MOD(B_DATE,1000)= 229 THEN 30
    WHEN MOD(B_DATE,1000)= 228
    AND MOD(TRUNC(B_DATE/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(B_DATE,100),30)
    END AS DAY1,
    TRUNC(E_DATE/10000,0) AS YEAR2 ,
    TRUNC(MOD(E_DATE,10000)/100,0) AS MONTH2 ,
    CASE
    WHEN MOD(E_DATE,1000)= 229 THEN 30
    WHEN MOD(E_DATE,1000)= 228
    AND MOD(TRUNC(E_DATE/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(E_DATE,100),30)
    END AS DAY2,
    TRUNC(&DRIL/10000,0) AS YEARRIL ,
    TRUNC(MOD(&DRIL,10000)/100,0) AS MONTHRIL ,
    CASE
    WHEN MOD(&DRIL,1000)= 229 THEN 30
    WHEN MOD(&DRIL,1000)= 228
    AND MOD(TRUNC(&DRIL/10000,0),4) > 0 THEN 30
    ELSE MIN(MOD(&DRIL,100),30)
    END AS DAYRIL,
    DAYS(CHAR((SUBSTR(CHAR(E_DATE),3,4)) !! '-' !! (SUBSTR(CHAR(E_DATE),7,2)) !! '-' !!
    (SUBSTR(CHAR(E_DATE),9,2)))) -
    DAYS(CHAR((SUBSTR(CHAR(B_DATE),3,4)) !! '-' !! (SUBSTR(CHAR(B_DATE),7,2)) !! '-' !!
    (SUBSTR(CHAR(B_DATE),9,2)))) AS GG_CED_S,

    DAYS(CHAR(&DATA)) -
    DAYS(CHAR((SUBSTR(CHAR(B_DATE),3,4)) !! '-' !! (SUBSTR(CHAR(B_DATE),7,2)) !! '-' !!
    (SUBSTR(CHAR(B_DATE),9,2)))) AS GG_MAT_S,

    CASE WHEN PER = 'T' THEN 4 WHEN PER = 'S' THEN 2 WHEN PER =
    'A' THEN 1 END AS NR_CED

    FROM TABLEONE A, TABLETWO C


    the formulas that create columns with date differences are perfect. The day
    count is always correct.
    Problems rise when I make the calculation written in blue: if I calculate

    RATE/ 360 * GG_MAT_S
    or RATE/ GG_CED_C*GG_MAT_C
    everything works perfectly.

    But when I calculate RATE/360*GG_CED_C it sometimes calculate it and
    sometimes the related columns are returned empty.
    I can't find anything that distinguish records that are calculated from records
    that are not. GG_MAT_C with low values seem to be always calculated but
    for examples there are two records with the same dates and in one case the calculation is made and in the other it isn't.
    What's the problem? 360 and GG_CED_C doesn't get along together!
    Now that I'm writing I'm wondering if maybe I could solve the problem by
    creating a new column calculating RATEO/360 AS RAT_360 and then
    calculate RAT_360 * GG_MAT_C_. What do you think?

    In any case now that I've seen what's happened with this query, I wonder
    if SQL IS APT TO MAKE CALCULATIONS (???) After all its duty is to query data and not to make calculations among them.
    I'd like to know from you experts if I can consider SQL reliable when it comes
    to calculations, because in this case it was easy to spot the failure, but
    it might not be so in other cases.

    Thank you for your attention.

    Anna - Verona (Italy)

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by annamaria
    (YEAR2 - YEAR1)*360 + (MONTH2 - MONTH1)*30 + (DAY2 - DAY1)AS GG_CED_C,
    (YEARRIL - YEAR1)*360 + (MONTHRIL - MONTH1)*30 + (DAYRIL - DAY1)AS GG_MAT_C,
    I don't know the semantics of your data and the query, but I'd say that the calculations have a serious problem: If MONTH2 is smaller than MONTH1 (say January 2007 vs. April 2006), you will get a negative month count. Same applies do days.

    Btw, could you do regular date arithmetics or are you fixed on the 30 days/month rule? Regular date arithmetics would be much, much simpler to write down. For your stuff, I'd also recommend that you write some SQL-bodied functions that encapsulate something like calculating the difference between two dates.

    But when I calculate RATE/360*GG_CED_C it sometimes calculate it and
    sometimes the related columns are returned empty.
    What do you mean with "empty"? You are calculating with numbers, so there is no empty possible. Do you mean NULL? Or is it 0 (zero)?

    I can't find anything that distinguish records that are calculated from records
    that are not.
    What do you mean?

    GG_MAT_C with low values seem to be always calculated but
    for examples there are two records with the same dates and in one case the calculation is made and in the other it isn't.
    What's the problem? 360 and GG_CED_C doesn't get along together!
    Have you veryfied that the GG_CED_C values are correct? Just take the respective subquery and look at its intermediate table.

    Also, you should really try to reduce your query to the bare minimum that the problem still occurs but all unnecessary stuff is removed. (Something like 5 lines of SQL code should be sufficient in this case.) Then dig further into the details to understand why things don't work as you hope.

    In any case now that I've seen what's happened with this query, I wonder if SQL IS APT TO MAKE CALCULATIONS (???)
    Sure. SQL is a full programming language.

    I'd like to know from you experts if I can consider SQL reliable when it comes
    to calculations, because in this case it was easy to spot the failure, but
    it might not be so in other cases.
    I'm confused. You know what the error is in your query? I thought you don't know what's going on. I think you have a problem in the query itself and that's why the mathematical expressions don't result in what you want to have.
    (Your call to decide whether I fall in the "expect category.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by stolze
    I don't know the semantics of your data and the query, but I'd say that the calculations have a serious problem: If MONTH2 is smaller than MONTH1 (say January 2007 vs. April 2006), you will get a negative month count. Same applies do days.

    Btw, could you do regular date arithmetics or are you fixed on the 30 days/month rule? Regular date arithmetics would be much, much simpler to write down. For your stuff, I'd also recommend that you write some SQL-bodied functions that encapsulate something like calculating the difference between two dates.



    What do you mean with "empty"? You are calculating with numbers, so there is no empty possible. Do you mean NULL? Or is it 0 (zero)?



    What do you mean?



    Have you veryfied that the GG_CED_C values are correct? Just take the respective subquery and look at its intermediate table.

    Also, you should really try to reduce your query to the bare minimum that the problem still occurs but all unnecessary stuff is removed. (Something like 5 lines of SQL code should be sufficient in this case.) Then dig further into the details to understand why things don't work as you hope.



    Sure. SQL is a full programming language.



    I'm confused. You know what the error is in your query? I thought you don't know what's going on. I think you have a problem in the query itself and that's why the mathematical expressions don't result in what you want to have.
    (Your call to decide whether I fall in the "expect category.)

    The calculations of date differences are correct. Of course I can have
    get a negative month or days count, but it's okay. For example:
    01/05/2007 - (minus) 01/03/2008: it's true that I have a negative month
    count. But before counting the month I've made the difference between
    years, which is (2008 - 2007) * 360: I have counted a whole year from
    which I have to subtract two months (the difference between the two date
    is 10 months i.e. less than one year.
    Day difference columns are perfect! And they were created by making the
    calculations you find in the query.
    So, now I have column GG_CED_C that was created by using the formula in
    red. There are numbers in it, and they are successfully used to made
    calculations, in other cases. But when I use 360 (a number I type) in the
    calculation with GG_CED_C it can give the right result or it can give no result (if I use 360 with GG_CED_S everything is okay).
    Take this example:

    20061229 20070630 3,995 180 90 0,99875
    20061229 20070630 4,185 180 90
    The dates are identical, day differences are identical, but in the first record
    I have my count (RATEO), in the second there is nothing.

    I reduce my query. For example I took off the first (from the beginning of
    the query) 'when clause' and simply told SQL to calculate every record with
    /360*GG_CED_C and the result was the same: some records are calculated
    and when they are, they are calculated correctly, and some don't.
    Now what I think is that since GG_CED_C is created using 360 in the formula,
    then the programm gets crazy when it finds another 360. But what I don't
    understand is why it behaves in different ways. With some records it makes
    the calculation, for others it doesn't.

    With 'spotting the failure' I meant that I could realize that there was
    something wrong. Not that I understood WHAT was wrong.

    Of course I think that you are an expert. The fact is that in my post I put
    a very long query and I knew that it would be difficult to find the patience
    to read it carefully.
    As for Valentino Rossi, of course he had problems with his tyres, but I was
    referring to the mistake he made in the bend a few minutes after starting.
    I don't think it was due to tyres. It was his fault.
    By the way, who's your favourite rider? And what's your favourite motorcycle
    brand?
    Could you recommend me some good SQL book? Not in German!
    Thank you again.

    Anna

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by annamaria
    The calculations of date differences are correct. Of course I can have get a negative month or days count, but it's okay. For example:
    01/05/2007 - (minus) 01/03/2008: it's true that I have a negative month
    count. But before counting the month I've made the difference between
    years, which is (2008 - 2007) * 360: I have counted a whole year from
    which I have to subtract two months (the difference between the two date
    is 10 months i.e. less than one year.
    Yes, you're right there.

    So, now I have column GG_CED_C that was created by using the formula in
    red. There are numbers in it, and they are successfully used to made
    calculations, in other cases. But when I use 360 (a number I type) in the
    calculation with GG_CED_C it can give the right result or it can give no result (if I use 360 with GG_CED_S everything is okay).
    Take this example:

    20061229 20070630 3,995 180 90 0,99875
    20061229 20070630 4,185 180 90
    The dates are identical, day differences are identical, but in the first record
    I have my count (RATEO), in the second there is nothing.
    What's your environment that executes the query and shows the result? Again, there are no "empty" values for numbers in DB2. It is probably a NULL or 0 (zero) and your application represent it as this empty thing. You should execute the query on the command line (or in the Command Editor). That way you could more easily drill down into the query.

    I reduce my query. For example I took off the first (from the beginning of
    the query) 'when clause' and simply told SQL to calculate every record with
    /360*GG_CED_C and the result was the same: some records are calculated
    and when they are, they are calculated correctly, and some don't.
    Now what I think is that since GG_CED_C is created using 360 in the formula,
    then the programm gets crazy when it finds another 360. But what I don't
    understand is why it behaves in different ways. With some records it makes
    the calculation, for others it doesn't.
    You should identify 1 record where it fails, 1 where it works, and 1 where it fails partly. Then do through the expressions and figure out what the results are at each step for all of the 3 records.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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