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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
