Home » SQL & PL/SQL » SQL & PL/SQL » Problem with query
Problem with query [message #184170] Tue, 25 July 2006 10:53 Go to next message
tomk
Messages: 17
Registered: March 2006
Junior Member
Hi all

here are my tables and example data:
create table temp(
 num number,
 product_id_old number,
 product_id_new number,
 date_from date,
 date_to date
);

create table temp_2(
num number,
acct_date date
);

INSERT ALL
  INTO temp_2 VALUES(1, to_date('2005-12-23','YYYY-MM-DD'))
  INTO temp_2 VALUES(2, to_date('2006-07-23','YYYY-MM-DD'))
SELECT* FROM DUAL;

INSERT ALL 
  INTO temp VALUES(1, 101,103,to_date('2001-01-05','YYYY-MM-DD'),to_date('2002-10-02','YYYY-MM-DD'))
  INTO temp VALUES(1, 203,103,to_date('2002-10-02','YYYY-MM-DD'),to_date('2002-11-03','YYYY-MM-DD'))
  INTO temp VALUES(1, 105,203,to_date('2005-11-03','YYYY-MM-DD'),to_date('2004-01-07','YYYY-MM-DD'))
  INTO temp VALUES(1, 809,303,to_date('2004-01-07','YYYY-MM-DD'),to_date('2005-02-03','YYYY-MM-DD'))
  INTO temp VALUES(1, 203,603,to_date('2005-02-03','YYYY-MM-DD'),to_date('2005-09-01','YYYY-MM-DD'))
  INTO temp VALUES(1, 502,203,to_date('2005-09-01','YYYY-MM-DD'),to_date('2006-05-03','YYYY-MM-DD'))
  INTO temp VALUES(2, 219,313,to_date('2004-04-07','YYYY-MM-DD'),to_date('2005-09-03','YYYY-MM-DD'))
  INTO temp VALUES(2, 323,633,to_date('2005-09-03','YYYY-MM-DD'),to_date('2006-02-09','YYYY-MM-DD'))
  INTO temp VALUES(2, 542,253,to_date('2006-02-09','YYYY-MM-DD'),to_date('2006-07-18','YYYY-MM-DD'))         
SELECT * FROM DUAL;


tables are joined with `num`.
I would like to get data like this:
1.if temp_v2.acct_date is (for the same `num` in `temp`)between any of the date_from date_to, result should look like this:
num product
1  502

it means that `product` has value of the `product_id_old `
2. if temp_v2.acct_date is not between any of the date_from-date_to and is greater than the newest `date_to` , result should like like this:
num product
2   253

it means, that `product` has value of the `product_id_new` of the newest record

I tried like this
select num, max(product)from(
select
	  temp.num, temp.DATE_FROM, temp.DATE_TO,temp_2.ACCT_DATE,
	  (CASE WHEN temp_2.ACCT_DATE BETWEEN temp.DATE_FROM AND temp.DATE_TO THEN product_id_old else 0 end) product
from temp, temp_2
where temp.NUM = temp_2.NUM
)group by num


but it gives
num product
1   502
2   0

instead of
num product
1   502
2   253


does anyone have an idea how to solve it?
Thanks in advance
Re: Problem with query [message #184192 is a reply to message #184170] Tue, 25 July 2006 12:28 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Not exactly sure what you are asking, but let's just look at the inline query part for now. Here it is. Maybe you could replace the ? with the value that you want and explain clearly why that is the value. (I don't see how you get 253 from anything.)

SQL> SELECT temp.num
  2        ,temp.date_from
  3        ,temp.date_to
  4        ,temp_2.acct_date
  5        ,product_id_old
  6        ,'?' product
  7  FROM temp
  8      ,temp_2
  9  WHERE temp.num = temp_2.num;

       NUM DATE_FROM   DATE_TO     ACCT_DATE   PRODUCT_ID_OLD PRODUCT
---------- ----------- ----------- ----------- -------------- -------
         1 1/5/2001    10/2/2002   12/23/2005             101 ?
         1 10/2/2002   11/3/2002   12/23/2005             203 ?
         1 11/3/2005   1/7/2004    12/23/2005             105 ?
         1 1/7/2004    2/3/2005    12/23/2005             809 ?
         1 2/3/2005    9/1/2005    12/23/2005             203 ?
         1 9/1/2005    5/3/2006    12/23/2005             502 ?
         2 4/7/2004    9/3/2005    7/23/2006              219 ?
         2 9/3/2005    2/9/2006    7/23/2006              323 ?
         2 2/9/2006    7/18/2006   7/23/2006              542 ?

9 rows selected
Then talk a bit how which of those values you just put in should be the max(product) you want and why.
Re: Problem with query [message #184229 is a reply to message #184192] Tue, 25 July 2006 15:59 Go to previous messageGo to next message
tomk
Messages: 17
Registered: March 2006
Junior Member
ok, I will try to explain it more clearly.
Data in table temp:
       NUM PRODUCT_ID_OLD PRODUCT_ID_NEW DATE_FRO DATE_TO
---------- -------------- -------------- -------- --------
         1            101            103 01/01/05 02/10/02
         1            203            103 02/10/02 02/11/03
         1            105            203 05/11/03 04/01/07
         1            809            303 04/01/07 05/02/03
         1            203            603 05/02/03 05/09/01
         1            502            203 05/09/01 06/05/03
         2            219            313 04/04/07 05/09/03
         2            323            633 05/09/03 06/02/09
         2            542            253 06/02/09 06/07/18

and data from table temp_2:
       NUM ACCT_DAT
---------- --------
         1 05/12/23
         2 06/07/23

So lets study num =1.
1. I get acct_dat from temp_2 for num=1 - it is ACCT_DAT=05/12/23.
Now I check in table temp if there is any row with num=1 in which the date ACCT_DAT=05/12/23 is between Date_from and date_to. I see, that it is for this line:
     NUM PRODUCT_ID_OLD PRODUCT_ID_NEW DATE_FRO DATE_TO
---------- -------------- -------------- -------- --------
         1            502            203 05/09/01 06/05/03

So I take PRODUCT_ID_OLD as Product(new column in result)

2. I get acct_dat from temp_2 for num=2 - it is ACCT_DAT=06/07/23.
Now I check in table temp if there is any row with num=2 in which the date ACCT_DAT=06/07/23 is between Date_from and date_to. I see, that there isn't, because the ACCT_DAT=06/07/23 is greater than the last date_to:
       NUM PRODUCT_ID_OLD PRODUCT_ID_NEW DATE_FRO DATE_TO
---------- -------------- -------------- -------- --------
         2            542            253 06/02/09 06/07/18
If there is situation like this, a have to take PRODUCT_ID_NEW from the record in which DATE_TO is the newest as the Product. That is why I have to take PRODUCT_ID_NEW=253 as Product(new column in result)

Re: Problem with query [message #184236 is a reply to message #184170] Tue, 25 July 2006 17:55 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SQL> SELECT num
  2        ,CASE MAX(product) WHEN 0 THEN MAX(last_id) ELSE MAX(product) END
  3  FROM (SELECT temp.num
  4              ,temp.date_from
  5              ,temp.date_to
  6              ,temp_2.acct_date
  7              ,temp.product_id_old
  8              ,temp.product_id_new
  9              ,(CASE
 10                   WHEN temp_2.acct_date BETWEEN temp.date_from AND temp.date_to THEN
 11                    product_id_old
 12                   ELSE
 13                    0
 14               END) product
 15              ,CASE temp.rn WHEN 1 THEN temp.product_id_new ELSE 0 END last_id
 16        FROM (SELECT t.*
 17                    ,row_number() over(PARTITION BY t.num ORDER BY date_from DESC) rn
 18              FROM temp t) temp
 19            ,temp_2
 20        WHERE temp.num = temp_2.num)
 21  GROUP BY num;

       NUM CASEMAX(PRODUCT)WHEN0THENMAX(L
---------- ------------------------------
         1                            502
         2                            253
Re: Problem with query [message #184315 is a reply to message #184236] Wed, 26 July 2006 02:29 Go to previous message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Another solution:

SELECT a.num
     , a.product_id_old
  FROM temp a
     , temp_2 b
 WHERE a.num = b.num
   AND b.acct_date BETWEEN a.date_from AND a.date_to
UNION ALL
SELECT a.num
     , a.product_id_new
  FROM (SELECT temp.*
             , ROW_NUMBER () OVER (PARTITION BY num 
			           ORDER BY date_to DESC) rnum
          FROM temp) a
     , temp_2 b
 WHERE a.num = b.num
   AND b.acct_date > a.date_to
   AND a.rnum = 1

Query Your Dream & Future at SoQooL
http://www.soqool.com
Previous Topic: TO_DATE Problem
Next Topic: Find values in other records of a table
Goto Forum:
  


Current Time: Fri Dec 09 17:28:53 CST 2016

Total time taken to generate the page: 0.23255 seconds