Home » SQL & PL/SQL » SQL & PL/SQL » extracting information from another table based on the data in the current row
extracting information from another table based on the data in the current row [message #230273] Wed, 11 April 2007 06:55 Go to next message
bekim
Messages: 2
Registered: April 2007
Location: Kosova
Junior Member
The problem is as follows:

In the table CREDIT, I would like to perform a query that gets all transactions performed by users such as date, user_id, credit and location (from pos_staf_his tbl) where transaction has been made. However since the staff have moved from one location to another I am having difficult in tracing them in which location they were assigned at a particular date.


CREDIT
data	                user_id	credit
19/03/2007 00:00	FBAKO	3
20/03/2007 00:00	FBAKO	5
20/03/2007 00:00	FBAKO	5
21/03/2007 00:00	BEKI	5
25/03/2007 00:00	BEKI	10
30/03/2007 00:00	BEKI	5



I have a table that records staff history POS_STAF_HIS and this records the date and location every time the user have been transferred.
POS_STAF_HIS
transfer_date	        user_id	location
19/03/2007 01:00	FBAKO	PZ 01
20/03/2007 01:00	FBAKO	PZ 02
21/03/2007 00:00	BEKI	PR 1
26/03/2007 00:00	BEKI	PR2
19/03/2007 01:00	BEKI	PR 03



Result table should be like this:

EXPECTED QUERY RESULT	
data	                user_id	credit  location 
19/03/2007 00:00	FBAKO	3	PZ 1
20/03/2007 00:00	FBAKO	5	PZ 2
20/03/2007 00:00	FBAKO	5	PZ 2
21/03/2007 00:00	BEKI	5	PR 1
25/03/2007 00:00	BEKI	10	PR 1
30/03/2007 00:00	BEKI	5	PR 2



For example I am able to know which location was assigned the user ‘BEKI’ on the date say ‘25/03/2007’ by performing this query:

select pos_id from (
select pos_id from pos_staf_his WHERE USER_ID = 'BEKI' 
and to_date(transfer_date, 'dd/mm/yyyy' ) <= to_date('25/03/2007', 'dd/mm/yyyy') 
order by transfer_date DESC
)where rownum = 1

POS_ID
--------
PR 2

As we can see from POS_STAF_HIS , the user ‘BEKI’ was transferred to PR 1 on 21/03/2007 and on 26/03/2007 was transferred to PR 2, so when we looked for the date ‘25/03/2007’ we know that he was assigned to PR 1

Now for every row in the table CREDIT, I need to append an other extra column information about LOCATION

Intentions are as follow
select date, user_id, date, location ( how can I extract this information from tbl POS_STAF_HISTORI, based on the date and user_id from the current row) to achive the expected query result as in the figure above.
Re: extracting information from another table based on the data in the current row [message #230347 is a reply to message #230273] Wed, 11 April 2007 11:14 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks for the detailed description and the table creation scripts !

Does this give the required result ? :

SELECT * FROM (
    select cr.user_id, 
           cr.data,
           Nvl(his.POS_ID,'none'),
           cr.transaction,
           --his.transfer_date,
           Rank() over (PARTITION BY cr.user_id, cr.data ORDER BY his.transfer_date desc) rnk
      from CREDIT cr, POS_STAF_HIS his
     WHERE his.USER_ID(+) = cr.user_id
       AND Trunc(his.transfer_date(+)) <= Trunc(cr.data)
    )
WHERE rnk = 1


Re: extracting information from another table based on the data in the current row [message #230535 is a reply to message #230273] Thu, 12 April 2007 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I second that - thanks for the create and insert statements. Makes a real change.

This is another query that should give the required results
select cr.user_id, 
       cr.data,
       (select distinct first_value(pos_id) over (order by transfer_date desc) 
        from   pos_staf_his h 
        where  h.user_id = cr.user_id
        and    trunc(h.transfer_date) <= trunc(cr.data)),
       cr.transaction
from CREDIT cr
Re: extracting information from another table based on the data in the current row [message #230611 is a reply to message #230347] Thu, 12 April 2007 05:46 Go to previous message
bekim
Messages: 2
Registered: April 2007
Location: Kosova
Junior Member
Thank you all for the time taken to resolve the query problem. Proposed solutions wroked for me Razz
Previous Topic: unable to retrieve information from...plz help
Next Topic: Better solution for "merge into .."?
Goto Forum:
  


Current Time: Sat Dec 10 12:52:51 CST 2016

Total time taken to generate the page: 0.09373 seconds