Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2014
    Posts
    6

    Unanswered: Different results in SQL query for different for different accounting periods

    Hi,
    I have a very simple query:
    SELECT count(*)
    FROM table1
    where table1.val1 >=1
    and table1.date1
    between :d_start and :d_end
    but What is wrong??
    If I count for e.g. 3 months I get a different result than for the entire period. I determined that the result of the query does not include calculations for the last days of components Months.
    How can i fix this problem???

    Thank you for your trouble

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you explain why you think you ought to get different values?

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

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by kolipl View Post
    Hi,
    I have a very simple query:
    SELECT count(*)
    FROM table1
    where table1.val1 >=1
    and table1.date1
    between :d_start and :d_end
    but What is wrong??
    If I count for e.g. 3 months I get a different result than for the entire period. I determined that the result of the query does not include calculations for the last days of components Months.
    How can i fix this problem???

    Thank you for your trouble
    1) You should use the "code" tags to format your query:
    Code:
    SELECT COUNT ( *)
      FROM Table1
     WHERE Table1.Val1 >= 1
       AND Table1.Date1 BETWEEN :d_Start AND :d_End
    2) Post create table and insert statements with sample data.
    3) Post the expected result based on the data you supply.
    4) Post how/where defined and value of your bind variables :d_Start and :d_End
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Apr 2014
    Posts
    6
    I got different values, e.g. for January (from 01 to 31) the result is 92, for February (from 01 to 28)- 103, for March (from 01 to 31)- 58. But result for the entire period-3 months (from 01.01 to 31.03) is not match to 92+103+58=253. The end result is 226, less than 253. What is bad, boundary conditions are the same??
    This select is defined in Oracle Report, Bind variables :d_Start and :d_End are defined as variables. Now I'm after work and I can't check how they are defined

  5. #5
    Join Date
    Apr 2014
    Posts
    6

    RE differences

    Hi,
    1) I'm sorry, I will do better,

    2) Now I haven't access to database and application, I'm just after work
    ... but (in relation to query) there is nothig special in table definition: val1 is NUMBER(2) NOT NULL column, Date1 is DATE column,

    3) For January [01-31] result is e.g. 108, for February [01-28]- 56, for March [01-31]- 75, SUMMA is 239. But the result for the entire period [from 01.01.2014 to 31.03.2014] is 217.I determined that the cumulative report doesn't count for the last day of each month.

    4) bind variables :d_Start and :d_End are defined as values entered by the user in Oracle Report 6i. DB is 10.2.0.2.0

  6. #6
    Join Date
    Dec 2013
    Posts
    14
    HI,

    To remove last day of the month use the in built function LAST_DAY(Start_Date). But this has to be repeated for each month in your date range.

  7. #7
    Join Date
    Apr 2014
    Posts
    6

    differences- data and report

    Hi,
    my select for whole period:
    SELECT id,
    numer_aktualizacji val1,
    data_zatwierdzenia
    FROM zgloszenia
    WHERE data_zatwierdzenia BETWEEN TO_DATE('2014/01/01', 'YYYY/MM/DD') AND TO_DATE('2014/04/05', 'YYYY/MM/DD')
    ORDER BY numer_aktualizacji

    and the result /collected data/ in 7z file.

    Here You are real results:
    for
    SELECT COUNT(*)
    FROM zgloszenia
    WHERE data_zatwierdzenia BETWEEN TO_DATE('2014/01/01', 'YYYY/MM/DD') AND TO_DATE('2014/03/31', 'YYYY/MM/DD')
    AND numer_aktualizacji >1
    ORDER BY numer_aktualizacji
    ===> result is 328 ===> whole period

    for January
    ....
    WHERE data_zatwierdzenia BETWEEN TO_DATE('2014/01/01', 'YYYY/MM/DD') AND TO_DATE('2014/01/31', 'YYYY/MM/DD')
    ===> result is 75

    for February
    ... WHERE data_zatwierdzenia BETWEEN TO_DATE('2014/02/01', 'YYYY/MM/DD') AND TO_DATE('2014/02/28', 'YYYY/MM/DD')
    ===> result is 112

    for March
    WHERE data_zatwierdzenia BETWEEN TO_DATE('2014/03/01', 'YYYY/MM/DD') AND TO_DATE('2014/03/31', 'YYYY/MM/DD')
    ===> result is 132

    Partial results 75+112+132 = 319
    Result for whole period = 328

    As a result ===> lost data for 9 records in summary report!!
    http://www.dbforums.com/db_images_v3...es/shocked.gif

    Where is the problem, how how could fix it??


    RE Abhinand
    "To remove last day of the month use the in built function LAST_DAY(Start_Date). But this has to be repeated for each month in your date range."
    I don't want remove last day of the month from count, I want add last day(s) to report /for whole period/ because I lost data/record approved on last day of the month.

    Thank You for Your attention,
    Gregory

  8. #8
    Join Date
    Apr 2014
    Posts
    6

    RE same not the same

    Quote Originally Posted by Pat Phelan View Post
    Can you explain why you think you ought to get different values?

    -PatP
    I want to receive THE SAME values!!! But I receive different http://www.dbforums.com/db_images_v3.../confused3.gif

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kolipl View Post
    I don't want remove last day of the month from count, I want add last day(s) to report /for whole period/ because I lost data/record approved on last day of the month.
    So include it by correctly specifying the upper limit value. As Oracle DATE data type contains a time part, the queries you posted do not count rows which are after last date's midnight.
    For including the whole last day (and not its midnight only - which may happen when time part is not stored at all), you may change the conditions to either
    Code:
    where data_whatever between :d_start and :d_end + 1 - 1/(24*60*60)
    (add one day and subtract one second - this is the last second of D_END) or maybe better
    Code:
    where data_whatever >= :d_start
      and data_whatever < :d_end + 1
    (dates before the next day excluding next day's midnight)

  10. #10
    Join Date
    Apr 2014
    Posts
    6

    differences- data and report

    Hi,
    I thank all the people who, on were involved in this project, especially for flayboy, and http://www.dbforums.com/db_images_v3...ilies/beer.gif for all.
    Of course, You are right flayboy, the problem was to determine the upper limit for the DATE data. Valid conditions are:
    SELECT COUNT(*)
    FROM zgloszenia
    WHERE d_start >= TO_DATE('2014/01/01', 'YYYY/MM/DD')
    AND d_end < (TO_DATE('2014/03/31', 'YYYY/MM/DD')+1)
    AND val1 >1
    Raport works correctly and and there is no differences in the results.
    Let this be a warning to others that will be determine the boundary conditions for the DATE data
    http://www.dbforums.com/db_images_v3...s/rolleyes.gif

    Sorry for my English, it's not my mother tounge and for Your forbearance.

    Sincerely,
    Kolipl

Tags for this Thread

Posting Permissions

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