Home » SQL & PL/SQL » SQL & PL/SQL » Rows to column conversion, based on date column
Rows to column conversion, based on date column [message #246506] Thu, 21 June 2007 03:07 Go to next message
orcacle
Messages: 6
Registered: November 2006
Junior Member
Hello,

Can someone please help with following:

I have a table (see below) containing promotion details. I need to create view having only specific promotion type details (Merit) and it's preceding occurrence only, based on date.
I successfully made two Embarassed 'views' with sub queries ( I'll post them if really needed), they work but it takes about 300 seconds to get my data (table has about 12000 lines, for some 500 employees), Oracle version 9i.
Now, I'd like to learn about more performing solution.

Thx in advance,

Promotions table

Personal Nbr      Promotion date      Promotion Type   
00001                 01/01/2000            Other1
00001                 01/01/2002            Other2
00001                 01/04/2004            Merit
00001                 01/06/2007            Other1
00002                 ...                   ...
etc.


My view should look like:

Pers Nbr  Prom date  Promotion Type  Date-1     Type-1
00001     01/04/2004 Merit           01/01/2002 Other2
etc. 

[Updated on: Thu, 21 June 2007 03:10]

Report message to a moderator

Re: Rows to column conversion, based on date column [message #246522 is a reply to message #246506] Thu, 21 June 2007 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many threads on this topic "Rows to column conversion".
Please check them, try to adapt them and come back with what you tried.

Regards
Michel
Re: Rows to column conversion, based on date column [message #246553 is a reply to message #246522] Thu, 21 June 2007 05:21 Go to previous message
orcacle
Messages: 6
Registered: November 2006
Junior Member
Thanks for your suggestion, of course I already did it...
This is one of the views I did, as said it works but it takes too much time. I'd like to know if it can be improved somehow(point is, there can be several 'Merit' promotions for the same employee during the time and I need them together with occurrences just before).

SELECT   a.pers_id
        ,a.promo_date
        ,a.promo_type
        ,b.promo_date
        ,b.promo_type
FROM 
 (SELECT  a.*
         ,(SELECT MAX(promo_date) 
           FROM
            promotions x
           WHERE a.pers_id = x.pers_id 
           AND   x.promo_date < a.promo_date
          ) prev_promo_date 
  FROM  
   promotions a
  WHERE   a.promo_date IN (SELECT promo_date 
                            FROM promotions x
                            WHERE a.pers_id = x.pers_id 
                            AND x.motif LIKE '%Merit%')
  ) a
,promotions b
WHERE   a.pers_id = b.pers_id
AND     a.prev_promo_date = b.promo_date

[Updated on: Thu, 21 June 2007 07:22]

Report message to a moderator

Previous Topic: What way Procedure are better then Function for Select statement
Next Topic: PL SQL Doubts
Goto Forum:
  


Current Time: Sat Dec 03 10:11:04 CST 2016

Total time taken to generate the page: 0.12639 seconds