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