CODEWITHSUNDEEP
sqldatejoinsubquerymax
user2630322
user2630322

Reputation: 51

Get MAX Date while using SQL subquery with join

I have two tables Deal and SCHNAV with a common field 'security'. Deal contains details of all securities purchased and sold till date and schnav contains closing security holding for each date. I want an sql to fetch the latest (max) date for all trades done in securities held as on a particular date only upto that date from deal table.

I used the following query to get all the deals and then from pivot got the latest value. But i need an sql so that I dont have to do mnipulation in Excel.

select scheme, security, asset_type, tran_type 
from deal 
where security in (select security from schnav where nav_date = '31 Mar 2013') 
  and d.value_date < '01 Apr 2013';

Please help. and Thanks in Advance

Upvotes: 0

Views: 1687

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need to join the deal and security tables together. In addition to the condition on the security field, you also have conditions on the date.

Finally, you need to find the last deal on or before the date. Most databases support the row_number() function for this purpose.

The following query combines these together:

select scheme, security, asset_type, tran_type
from (select d.scheme, d.security, d.asset_type, d.tran_type,
             row_number() over (partition by d.security order by d.value_date desc) as seqnum 
      from deal d join
           schnav s
           on d.security = s.security and
              d.value_date <= s.nav_date and
              s.nav_date = '31 Mar 2013'
    ) d
where seqnum = 1;

EDIT:

To get only one tran_type, use a where clause in the subquery:

select scheme, security, asset_type, tran_type
from (select d.scheme, d.security, d.asset_type, d.tran_type,
             row_number() over (partition by d.security order by d.value_date desc) as seqnum 
      from deal d join
           schnav s
           on d.security = s.security and
              d.value_date <= s.nav_date and
              s.nav_date = '31 Mar 2013'
     where d.tran_type = 'P'
    ) d
where seqnum = 1;

Upvotes: 1

Related Questions