Hi David and Mayank,
Thanks for your help. I am really sorry for misquoting my requirements.
On Tue, Jul 14, 2009 at 6:08 PM, ddf <orat...@msn.com> wrote:
>
>
>
> On Jul 14, 12:51 am, Roopesh S <sro...@gmail.com> wrote:
> > Hi David,
> >
> > Still doesnt work...!!
> >
> > Let me give some more details...
> >
> > Your query first takes the record which has max(effv_date) date and if
> > max(effv_date) > SYSDATE then it omits the product. This shouldn't
> happen.
> >
> > Example:
> >
> > * * *Product_id * * effv_date* * zone* 868735 1-Jul-09 2 868735
> > 4-Aug-09 2 868735 15-Jul-09 2 868735 26-Aug-09 2
> >
> > For the above given product: 868735 in a zone 2 has max(effv_date) =
> > 26-Aug-09. Which is more than SYSDATE so your query drops the product..
> >
> > *Desired out:*
> >
> > *Product_ID effv_date zone*
> > 868735 1-Jul-09 2
> >
> > *In short*: First, we have to select the record which has effv_date <
> > SYSDATE group by product_id, zone. Among the resultant records we need to
> > select max(effv_date) records.
>
> This is not what you stated in your original post:
>
> "I want to select a product_id for a zone which has a max effv_date
> which is
> less then sysdate."
>
> My query does just that, and, yes, if the max(eff_date) is greater
> than SYSDATE for all entries then no records are returned, because the
> original condition as stated in your original post is not met.
>
> NOW you state thatyou only want to consider records having an eff_date
> < sysdate, whether or not a record with a date greater than the
> current date exists. In that case the original query posted is what
> you want, even though it is not what you asked for to begin with. It
> helps tremendously when those who ask for something specify the exact
> conditions required.
>
> >
> >
> >
> >
> >
> > On Fri, Jul 10, 2009 at 7:00 PM, ddf <orat...@msn.com> wrote:
> >
> > > On Jul 10, 6:03 am, Mayank <mayank.del...@gmail.com> wrote:
> > > > hi,
> >
> > > > hope u'll come out from ur problem..
> >
> > > > SELECT PRODUCT_ID,ZONE,MAX(EFFV_DATE)
> > > > FROM PL_GRP
> > > > WHERE EFFV_DATE < TO_DATE('13-JUL-09','DD-MON-YY') -- 13-JUL-09
> TAKEN
> > > > AS SYSDATE
> > > > GROUP BY PRODUCT_ID,ZONE
> >
> > > > Mayank
> >
> > > > On Jul 10, 3:43 pm, Roopesh S <sro...@gmail.com> wrote:
> >
> > > > > Hi There,
> >
> > > > > I want to select a product_id for a zone which has a max effv_date
> > > which is
> > > > > less then sysdate. Please consider that sysdate is '13-July-2009'
> >
> > > > > PS: There can be many products which in the same zone. As given in
> the
> > > > > example below.
> >
> > > > > *Example data:*
> >
> > > > > * * *product_id* * zone* * effv_date *
> 123
> > > 1
> > > > > 10-Jul-09 123 1 11-Jul-09 123 1 12-Jul-09 234 1 9-Jul-09 234 1
> 8-Jul-09
> > > 456
> > > > > 2 5-Jul-09 456 2 6-Jul-09 * *
> > > > > *Output expected*
> > > > > * *
> > > > > *product_id* *zone* *effv_date* 123 1 12-Jul-09 234 1 9-Jul-09
> 456 2
> > > > > 6-Jul-09
> >
> > > > > Please let me know your inputs..
> >
> > > > > Thanx'N'Regards
> > > > > Roopesh- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > SQL> --
> > > SQL> -- Create table
> > > SQL> --
> > > SQL>
> > > SQL> create table proddt(
> > > 2 product_id number,
> > > 3 zone number,
> > > 4 eff_date date
> > > 5 );
> >
> > > Table created.
> >
> > > SQL>
> > > SQL> --
> > > SQL> -- Populate table
> > > SQL> --
> > > SQL>
> > > SQL> insert all
> > > 2 into proddt
> > > 3 values(123, 1, to_date('10-Jul-09','dd-mon-rr'))
> > > 4 into proddt
> > > 5 values(123, 1, to_date('11-Jul-09','dd-mon-rr'))
> > > 6 into proddt
> > > 7 values(123, 1, to_date('12-Jul-09','dd-mon-rr'))
> > > 8 into proddt
> > > 9 values(234, 1, to_date('9-Jul-09','dd-mon-rr'))
> > > 10 into proddt
> > > 11 values(234, 1, to_date('8-Jul-09','dd-mon-rr'))
> > > 12 into proddt
> > > 13 values(456, 2, to_date('5-Jul-09','dd-mon-rr'))
> > > 14 into proddt
> > > 15 values(456, 2, to_date('6-Jul-09','dd-mon-rr'))
> > > 16 select * from dual;
> >
> > > 7 rows created.
> >
> > > SQL>
> > > SQL> commit;
> >
> > > Commit complete.
> >
> > > SQL>
> > > SQL> --
> > > SQL> -- Doesn't give correct results
> > > SQL> -- but does provide desired results for
> > > SQL> -- sample data provided
> > > SQL> --
> > > SQL> -- Query ignores all data with an EFF_DATE
> > > SQL> -- greater than SYSDATE
> > > SQL> --
> > > SQL>
> > > SQL> SELECT PRODUCT_ID,ZONE,MAX(EFF_DATE)
> > > 2 FROM PRODDT
> > > 3 WHERE EFF_DATE < TO_DATE('13-JUL-09','DD-MON-YY') -- 13-JUL-09
> > > TAKEN AS SYSDATE
> > > 4 GROUP BY PRODUCT_ID,ZONE ;
> >
> > > PRODUCT_ID ZONE MAX(EFF_DATE)
> > > ---------- ---------- --------------------
> > > 123 1 12-JUL-2009 00:00:00
> > > 234 1 09-JUL-2009 00:00:00
> > > 456 2 06-JUL-2009 00:00:00
> >
> > > SQL>
> > > SQL> --
> > > SQL> -- Provides desired data
> > > SQL> -- and provides correct results regardless
> > > SQL> -- of value for SYSDATE
> > > SQL> --
> > > SQL>
> > > SQL> select product_id, zone, max(eff_date) eff_date
> > > 2 from proddt
> > > 3 group by product_id, zone
> > > 4 having max(eff_date) < to_date('13-jul-09','dd-mon-rr')
> > > 5 /
> >
> > > PRODUCT_ID ZONE EFF_DATE
> > > ---------- ---------- --------------------
> > > 123 1 12-JUL-2009 00:00:00
> > > 234 1 09-JUL-2009 00:00:00
> > > 456 2 06-JUL-2009 00:00:00
> >
> > > SQL>
> > > SQL> --
> > > SQL> -- Insert more data
> > > SQL> --
> > > SQL>
> > > SQL> insert all
> > > 2 into proddt
> > > 3 values(123, 1, to_date('15-Jul-09','dd-mon-rr'))
> > > 4 into proddt
> > > 5 values(123, 1, to_date('14-Jul-09','dd-mon-rr'))
> > > 6 into proddt
> > > 7 values(123, 1, to_date('16-Jul-09','dd-mon-rr'))
> > > 8 into proddt
> > > 9 values(234, 1, to_date('19-Jul-09','dd-mon-rr'))
> > > 10 into proddt
> > > 11 values(234, 1, to_date('18-Jul-09','dd-mon-rr'))
> > > 12 into proddt
> > > 13 values(456, 2, to_date('15-Jul-09','dd-mon-rr'))
> > > 14 into proddt
> > > 15 values(456, 2, to_date('16-Jul-09','dd-mon-rr'))
> > > 16 select * from dual;
> >
> > > 7 rows created.
> >
> > > SQL>
> > > SQL> commit;
> >
> > > Commit complete.
> >
> > > SQL>
> > > SQL> --
> > > SQL> -- Run queries again
> > > SQL> --
> > > SQL> -- No records should be returned according to
> > > SQL> -- specification by OP that max(eff_date) must be
> > > SQL> -- less than SYSDATE
> > > SQL> --
> > > SQL>
> > > SQL> --
> > > SQL> -- Gives same results as before
> > > SQL> -- even though max(eff_date) is greater than
> > > SQL> -- SYSDATE
> > > SQL> --
> > > SQL>
> > > SQL> SELECT PRODUCT_ID,ZONE,MAX(EFF_DATE)
> > > 2 FROM PRODDT
> > > 3 WHERE EFF_DATE < TO_DATE('13-JUL-09','DD-MON-YY') -- 13-JUL-09
> > > TAKEN AS SYSDATE
> > > 4 GROUP BY PRODUCT_ID,ZONE ;
> >
> > > PRODUCT_ID ZONE MAX(EFF_DATE)
> > > ---------- ---------- --------------------
> > > 123 1 12-JUL-2009 00:00:00
> > > 234 1 09-JUL-2009 00:00:00
> > > 456 2 06-JUL-2009 00:00:00
> >
> > > SQL>
> > > SQL> --
> > > SQL> -- Gives correct results
> > > SQL> --
> > > SQL>
> > > SQL> select product_id, zone, max(eff_date) eff_date
> > > 2 from proddt
> > > 3 group by product_id, zone
> > > 4 having max(eff_date) < to_date('13-jul-09','dd-mon-rr')
> > > 5 /
> >
> > > no rows selected
> >
> > > SQL>
> >
> > > David Fitzjarrell
> >
> > --
> > Thanx'N'Regards
> > Roopesh- Hide quoted text -
> >
> > - Show quoted text -
>
>
> David Fitzjarrell
> >
>
--
Thanx'N'Regards
Roopesh
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
