Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    New York/New Zealand
    Posts
    8

    Unanswered: First of each month

    Hi I have a query kinda like this one

    SELECT MAX(MyDate)
    FROM MyTable
    WHERE ThisDate > MyDate

    What I really want is this query to run for a list of ThisDate...

    My list would be the first of each month from Date1 to Date2 which could span multiple years. How do I do this? So if I ran this query for a full year I would want 12 MAX dates returned 1 for each ThisDate which would be the first of each month for a year.

    Thanks in advance for any help.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    post examples of what you have and what you want.

    do you want the 1st date of each month that is in your TABLE?
    or do you just want the first date of each month regardless of what is in your table?

    first suggestion would be Analytics.
    I am currently studying this stuff so I am no master, but that doesn't mean I couldn't write it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: First of each month

    Originally posted by TenKracer
    Hi I have a query kinda like this one

    SELECT MAX(MyDate)
    FROM MyTable
    WHERE ThisDate > MyDate

    What I really want is this query to run for a list of ThisDate...

    My list would be the first of each month from Date1 to Date2 which could span multiple years. How do I do this? So if I ran this query for a full year I would want 12 MAX dates returned 1 for each ThisDate which would be the first of each month for a year.

    Thanks in advance for any help.
    select * from (
    select (:date1 + rownum) d from all_objects
    where rownum < :date2 - :date1 )
    where to_char(d,'DD') = '01'

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    this works.
    Analytics is fun.
    PHP Code:
      1  select to_char(earliest_date,'Mon DD YYYY HH24:MI:SS'"Earliest Date by Month",
      
    2  month from (
      
    3  SELECT DISTINCT MIN(rdng_dtover (PARTITION BY TO_CHAR(rdng_dt,'Mon')) Earliest_date,
      
    4  TO_CHAR(rdng_dt,'Mon'MONTH
      5  FROM METER_READING
      6
    WHERE rdng_dt BETWEEN SYSDATE-180 AND SYSDATE)
    15:37:06 platform@kod1col month format a7
    15
    :37:25 platform@kod1> /

    Earliest by Month  MONTH
    -------------------- -------
    Aug 23 2003 19:04:52 Aug
    Dec 01 2003 00
    :00:00 Dec
    Feb 01 2004 00
    :00:00 Feb
    Jan 01 2004 00
    :00:05 Jan
    Nov 01 2003 00
    :00:00 Nov
    Oct 01 2003 00
    :00:00 Oct
    Sep 01 2003 00
    :00:10 Sep 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Oct 2003
    Location
    New York/New Zealand
    Posts
    8
    OK I will build a sample table...


    DateField AnotherField AndOneMoreField
    1-Jan-2001 blah blahblah
    4-Mar-2001 stuff Dog
    12-Dec-2002 words waste
    22-Jan-2004 more waste


    So that would be the table the I would have a user enter 2 dates say something like

    Date1=1-Jan-2000
    Date2=1-Feb-2004

    And I would want to return a row for each month in that range so for the first 12 months I would return blank rows basically and then for the next 3 months I would return the data in 1-Jan-2001 and then for the next year and a bit I would return the row 4-Mar-2001 and so on until I got to the end of Date2.

    Does that make sense?

    Our problem is we are trying to reproduce a report that used to query a DB that would take a snap shot of the data at the first of each month but now has been designed to be kind of a rolling DB where all we do is keep track of changes. In my mind it makes more sense but does make writing this report difficult.

    Thanks for getting back so fast

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    see my last post.
    that should be good enough.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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