Results 1 to 12 of 12

Thread: Query Logic

  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Query Logic

    I have a Table that has colums like ID, DATE.

    My requirement is like this.
    -------------------------------

    I need to display 6 records at a time.
    this table can have any number of Records for a particular ID.

    The logic is like this.

    the first record that is retrieved is always the Minimum date
    the last record that is retrieved is always the Latest date

    The remaining 4 records is the trick.

    I need to find the Maximum Date in a particular month and pick that record. Now as i do this, if i find that the number of records are less than 4 then i need to pick up the records from the current month and make the count as 4.

    The Constraint that i have
    ------------------------------

    I dont want to use a Stored Proc to get this done.
    I can have multiple SQL's to get this and i'll do a UNION ALL to display the 6 records.

    Can anyone help me out in this

    Thanks
    Shankar

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    analytics baby!

    this would be an example of how to get the count and
    the dates you want. Put that into a from clause and go from there
    PHP Code:
    SELECT DISTINCT
    total
    ,
    cust_id,
    rdng_dt
    FROM 
    (
    SELECT 
           COUNT
    (*) over () total,
           
    MAX(rdng_dtover (PARTITION BY org_idending,
           
    MIN(rdng_dtover (PARTITION BY org_idbeginning,
           
    u.*
    FROM
           METER_READING u
    WHERE
           org_id 
    'VER' AND
           
    rdng_dt BETWEEN TO_DATE('02012004 0143','MMDDYYYY HH24MI') AND 
           
    TO_DATE('02292004 2359','MMDDYYYY HH24MI')   
           )
    WHERE rdng_dt ending OR rdng_dt beginning 

     TOTAL CUST_ID         RDNG_DT
    ------ --------------- ---------
     
    41423 6042            01-FEB-04
     41423 6042            29
    -FEB-04 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    67

    query logic

    the query that u gave doesnt work for the below condition

    01 Jan 2004
    02 Jan 2004
    05 Feb 2004
    07 Feb 2004
    04 Mar 2004
    06 Mar 2004
    19 Apr 2004
    22 May 2004
    01 Jun 2004
    07 Jun 2004
    19 Jul 2004

    Can u help me out

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    POst a copy of your code and the output you got.

    I thought we got this far:

    PHP Code:
    SELECT DISTINCT 

    FROM 
    SELECT 
    COUNT
    (*) over () plan_id
    MAX(DATE_OF_RUNover (PARTITION BY PLAN_IDending
    MIN(DATE_OF_RUNover (PARTITION BY PLAN_IDbeginning
    a.* 
    FROM 
    web_monitor_progress a 
    WHERE
    a
    .PLAN_ID 'x' /* <-- Your plan id here */
    a.DATE_OF_RUN BETWEEN 
    TO_DATE
    ('02012004 0000','MMDDYYYY HH24MI') AND 
    TO_DATE('02292004 2359','MMDDYYYY HH24MI'

    WHERE DATE_OF_RUN ending OR DATE_OF_RUN beginning 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Posts
    67

    query logic

    here u go

    the code
    -----------

    SELECT
    *
    FROM (
    SELECT
    MAX(DATE_OF_RUN) over (PARTITION BY PLAN_ID) ending,
    MIN(DATE_OF_RUN) over (PARTITION BY PLAN_ID) beginning,
    a.*
    FROM
    web_monitor_progress a
    WHERE
    a.PLAN_ID = 65404 AND
    a.DATE_OF_RUN BETWEEN
    TO_DATE('02012004 0000','MMDDYYYY HH24MI') AND
    TO_DATE('03292100 2359','MMDDYYYY HH24MI')
    )
    WHERE DATE_OF_RUN = ending OR DATE_OF_RUN = beginning


    output
    -------

    ending beginning date_of_run
    -------- ---------- --------------
    4/5/2004 2/4/2004 2/4/2004
    4/5/2004 2/4/2004 4/5/2004


    this is what i got

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    ah!
    You do not know ahead of time what month you will be searching
    for correct?? Basically you have a date range of 10 years and need
    the beginning moth date and end month date for each month in that
    span?

    How about this:
    PHP Code:
    SELECT 
    total_each_month
    ,
    to_char(DATE_OF_RUN'mm/dd/yyyy HH24:MI'rundate,
    plan_id
    FROM 

    SELECT 
    COUNT
    (*) over (PARTITION BY PLAN_IDTO_CHAR(rdng_dt,'MMYYYY')) total_each_month,
    MAX(DATE_OF_RUNover (PARTITION BY PLAN_IDTO_CHAR(rdng_dt,'MMYYYY')) ending
    MIN(DATE_OF_RUNover (PARTITION BY PLAN_IDTO_CHAR(rdng_dt,'MMYYYY')) beginning
    a.* 
    FROM 
    web_monitor_progress a 
    WHERE
    a
    .PLAN_ID 65404 AND 
    a.DATE_OF_RUN BETWEEN 
    TO_DATE
    ('02012004 0000','MMDDYYYY HH24MI') AND 
    TO_DATE('03292100 2359','MMDDYYYY HH24MI'

    WHERE DATE_OF_RUN ending OR DATE_OF_RUN beginning
    ORDER BY DATE_OF_RUN DESC

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jan 2004
    Posts
    67

    query logic

    Hey thanks a lot.

    But i have a small twist in my requirement

    My first record that is displayed is always the Min record thats available
    for that plan.

    the rest 5 records are fine.

    Can u help me out in getting this done.

    Currently there are 6 records that are retrieved. Last 5 records that are
    retrieved are fine. Instead of the first record that gets displayed
    I need the MIN date for that plan

    Hope this is clear

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    post an example of what the current output is and
    then what you want to see.

    So far it sounded like the first row you want to diplay the VERY FIRST
    entry ever for that plan regardless of the date range you originally specify?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jan 2004
    Posts
    67
    the data that i have in the table is like this

    01 Jan 2004
    02 Jan 2004
    07 Jan 2004
    04 Feb 2004
    06 Feb 2004
    19 Feb 2004
    04 Mar 2004
    28 Mar 2004
    03 Apr 2004
    05 Apr 2004

    I need the output something like this

    01 Jan 2004
    07 Jan 2004
    19 Feb 2004
    28 Mar 2004
    03 Apr 2004
    05 Apr 2004

    The output that i get is

    04 Feb 2004
    19 Feb 2004
    04 Mar 2004
    28 Mar 2004
    03 Apr 2004
    05 Apr 2004

    Thanks
    Shankar

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    look at your BETWEEN dates.
    the first date is February, change that to whatever you want.

    or a better way is (am I getting paid for this?):
    PHP Code:
    SELECT 
    total_each_month
    ,
    to_char(DATE_OF_RUN'mm/dd/yyyy HH24:MI'rundate,
    plan_id
    FROM 

    SELECT 
    COUNT
    (*) over (PARTITION BY PLAN_IDTO_CHAR(rdng_dt,'MMYYYY')) total_each_month,
    MAX(DATE_OF_RUNover (PARTITION BY PLAN_IDTO_CHAR(rdng_dt,'MMYYYY')) ending
    MIN(DATE_OF_RUNover (PARTITION BY PLAN_IDTO_CHAR(rdng_dt,'MMYYYY')) beginning
    a.* 
    FROM 
    web_monitor_progress a 
    WHERE
    a
    .PLAN_ID 65404 AND 
    a.DATE_OF_RUN BETWEEN (select min(DATE_OF_RUNfrom web_monitor_progress where plan_id 65404) AND 
    TO_DATE('03292100 2359','MMDDYYYY HH24MI'

    WHERE DATE_OF_RUN ending OR DATE_OF_RUN beginning
    ORDER BY DATE_OF_RUN DESC

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jan 2004
    Posts
    67
    hey cool man...

    thats it.. thanks for your help
    I know you have really taken your time out in getting this done for me...

    Unfortunately, i am not in a position to pay u back...

    thanks a lot dude
    Shankar

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by shankar

    Unfortunately, i am not in a position to pay u back...

    thanks a lot dude
    Shankar
    Just joking. All I need is Duck food actually.
    Besides, working with analytics is fun.
    - 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
  •