Atanas Nedyalkov Atanas Nedyalkov - 1 year ago 41
SQL Question

end of month data with inner join

I am trying got get last day of month price based on daily data. ISIN is in the mapping table and date etc is the content table. How should I construct the query so it works?

select b.isin, last_Day( End_date, a.PRICE
from MAPPING b,
where b.ISIN in ('xxx')
and >= '31Jul2010' AND <= '31Aug2010'
and a.instid = b.instid

I should get 1 result, not the daily results but with the last day of the particular month

b.isin last_day a.price
xxx 31/08/2010 p1
xxx 31/08/2010 p2
xxx 31/08/2010 p3
xxx 31/08/2010 p4
xxx 31/08/2010 p5
xxx 31/08/2010 p6
xxx 31/08/2010 p7
xxx 31/08/2010 p8
xxx 31/08/2010 p9
xxx 31/08/2010 p10
xxx 31/08/2010 p11
xxx 31/08/2010 p12
xxx 31/08/2010 p13
xxx 31/08/2010 p14
xxx 31/08/2010 p15
xxx 31/08/2010 p16
xxx 31/08/2010 p17
xxx 31/08/2010 p18
xxx 31/08/2010 p19
xxx 31/08/2010 p20
xxx 31/08/2010 p21
xxx 31/08/2010 p22

I kept the isin and price hidden on purpose(these are not the actual results).
But the logic is the same. Should be xxx 31/08.2010 a.price and like this for every month in a range of choice.

Answer Source

This will give you the last day of each month, and the price on the last day you have data for (which may not be the last day of the month, if you have gaps in your data):

-- CTEs to generate fake data
with content (instid, a_date, price) as (
  select 42, date '2010-08-09' + level, 'p'||level
  from dual connect by level <= 22
mapping (instid, isin) as (
  select 42, 'xxx' from dual
select b.isin,
  last_day(a.a_date) as last_day,
  max(a.price) keep (dense_rank last order by a.a_date) as price
from mapping b
join content a
on a.instid = b.instid
where b.isin in ('xxx')
and a.a_date >= date '2010-07-31' AND a.a_date <= date '2010-08-31'
group by b.isin, last_day(a.a_date);

ISI LAST_DAY   PRICE                                   
--- ---------- -----------------------------------------
xxx 2010-08-31 p22                                      

With data for July and August generated in a similar way, you get:

ISI LAST_DAY   PRICE                                   
--- ---------- -----------------------------------------
xxx 2010-07-31 p22                                      
xxx 2010-08-31 p52                                      

This uses the keep dense_rank analytic method to find the latest price within the group.