Matching on dates by month and year across tables where end of month is different SQL oracle

I have a few tables that I am selecting data from and I want to select only the rows in each table for the latest day in the relevant month. This is what I have done so far:

select 
    crd.indexname,
    sum(crd.pct_weight),
    extract(year from crd.datadate) as yr, 
    extract(month from crd.datadate) as mth
from 
    ref_own.v_msci_issuer_id_map maps, 
    pm_own.esg_credit_factors msci,
    pm_own.pma_sec_attributes_hv sec,
    crd_own.ml_corp_index_data_monthly crd
where 
    maps.msci_issuer_id=msci.issuer_id
    and sec.issuer_id=maps.pimco_issuer_id
    and extract(year from msci.as_of_date)=extract(year from crd.datadate)
    and extract(year from sec.asof_date)=extract(year from crd.datadate)
    and extract(month from msci.as_of_date)=extract(month from crd.datadate)
    and extract(month from sec.asof_date)=extract(month from crd.datadate)
    and crd.isin=sec.isin
    and maps.active=1
group by 
    crd.indexname,
    extract(year from crd.datadate) ,
    extract(month from crd.datadate) 
order by 
    crd.indexname, 
    extract(year from crd.datadate) desc , 
    extract(month from crd.datadate) desc

The problem with my script is that there are multiple intra month days and so I end up double, triple, etc counting.

728x90

4 Answers Matching on dates by month and year across tables where end of month is different SQL oracle

Jumm, maybe this way it's faster. If you use group by for date instead extract and use inner join I think your query be faster and more optimise. To get the last day of a month you can use the function LAST_DAY (https://www.techonthenet.com/oracle/functions/last_day.php) combined with subtracting dates to obtain the "latest day of month", and include it in where clause.

select crd.indexname as indexName, count(crd.ISIN) as countISIN, YEAR(crd.datadate) + '-' + MONTH(crd.datadate) as monthYear
from 
ref_own.v_msci_issuer_id_map maps INNER JOIN pm_own.esg_credit_factors msci 
ON maps.msci_issuer_id=msci.issuer_id
INNER JOIN pm_own.pma_sec_attributes_hv sec 
ON sec.issuer_id=maps.pimco_issuer_id
INNER JOIN crd_own.ml_corp_index_data_monthly crd
ON crd.isin=sec.isin
where msci.as_of_date = (SELECT MAX(as_of_date) FROM  msci group by (msci.as_of_date - LAST_DAY(crd.datadate)) LIMIT 1) 
and sec.as_of_date = (SELECT MAX(as_of_date) FROM  sec group by (msci.as_of_date - LAST_DAY(crd.datadate)) LIMIT 1) 
and maps.active=1
group by crd.indexname,crd.datadate
order by crd.indexname, crd.datadate desc

3 months ago

You can use LAST_DAY() to calculate the end date of each month, and I strongly suggest you stop using commas between table names, over 25 years ago "explicit join syntax" was formalized.

SELECT
    crd.indexname
  , extract(year from crd.datadate) as yr
  , extract(month from crd.datadate) as mth
  , COUNT( crd.ISIN )
FROM ref_own.v_msci_issuer_id_map maps
    INNER JOIN pm_own.esg_credit_factors msci ON maps.msci_issuer_id = msci.issuer_id
    INNER JOIN pm_own.pma_sec_attributes_hv sec ON sec.issuer_id = maps.pimco_issuer_id AND sec.asof_date = msci.as_of_date
    INNER JOIN crd_own.ml_corp_index_data_monthly crd ON crd.isin = sec.isin
WHERE maps.active = 1
    AND msci.as_of_date = last_day(msci.as_of_date)
GROUP BY
    crd.indexname
  , extract(year from crd.datadate)
  , extract(month from crd.datadate)
ORDER BY
    crd.indexname
  , extract(year from crd.datadate) DESC
  , extract(month from crd.datadate) DESC

3 months ago

This query will join the latest available record for each month in your 3 tables. It should not generate duplicates, unless you have several records for the same as_of_date in one of the 3 tables.

It is using JOINs to generate table relationships, and correlated suqueries to dynamically find the last record of each month in each table.

I added a condition to filter the last record from crd, which was not part of your original post, in case you have multiple records from one month in that table ; remove that if you don't need it.

select 
    crd.indexname,
    count(crd.ISIN),
    extract(year from crd.datadate) as yr,
    extract(month from crd.datadate) as mth
from 
    ref_own.v_msci_issuer_id_map       maps  
    join pm_own.esg_credit_factors     msci on maps.msci_issuer_id = msci.issuer_id
    join pm_own.pma_sec_attributes_hv  sec on sec.issuer_id = maps.pimco_issuer_id
    join crd_own.ml_corp_index_data_monthly crd on crd.isin = sec.isin
where 
    maps.active = 1
    and msci.as_of_date = (
        select max(as_of_date)
        from pm_own.esg_credit_factors 
        where issuer_id = msci.issuer_id and trunc(as_of_date, 'month') = trunc(crd.datadate, 'month')
    )
    and sec.as_of_date  = (
        select max(as_of_date) 
        from pm_own._own.pma_sec_attributes_hv 
        where issuer_id = sec.issuer_id and trunc(as_of_date, 'month') = trunc(crd.datadate, 'month')
    )
    and crd.as_of_date  = (
        select max(as_of_date) 
        from pm_own._own.pma_sec_attributes_hv 
        where isin = crd.isin and trunc(as_of_date, 'month') = trunc(crd.datadate, 'month')
    )
group by 
    crd.indexname,
    extract(year from crd.datadate) ,
    extract(month from crd.datadate) 
order by 
    crd.indexname, 
    extract(year from crd.datadate) desc , 
    extract(month from crd.datadate) desc

3 months ago

Use the RANK analytic function and you do not need to use any correlated sub-queries:

SELECT indexname,
       SUM(pct_weight),
       EXTRACT(year  FROM datadate) AS yr, 
       EXTRACT(month FROM datadate) AS mth
FROM   (
  SELECT crd.indexname,
         crd.pct_weight,
         TRUNC( crd.datadate, 'MM' ) AS datadate,
         RANK() OVER (
           PARTITION BY crd.indexname, TRUNC( crd.datadate, 'MM' )
           ORDER BY TRUNC( crd.datadate, 'DD' ) DESC
         ) AS rnk
  FROM   ref_own.v_msci_issuer_id_map maps
         INNER JOIN pm_own.esg_credit_factors msci
         ON ( maps.msci_issuer_id=msci.issuer_id )
         INNER JOIN pm_own.pma_sec_attributes_hv sec
         ON ( sec.issuer_id=maps.pimco_issuer_id )
         INNER JOIN crd_own.ml_corp_index_data_monthly crd
         ON ( TRUNC( msci.as_of_date, 'MM' ) = TRUNC( crd.datadate )
              AND TRUNC( sec.asof_date, 'MM' ) = TRUNC( crd.datadate ) 
              AND crd.isin=sec.isin )
  WHERE  maps.active=1
)
WHERE  rnk = 1
GROUP BY
    indexname,
    EXTRACT(year  FROM datadate) ,
    EXTRACT(month FROM datadate) 
order by 
    indexname, 
    EXTRACT(year  FROM datadate) DESC,
    EXTRACT(month FROM datadate) DESC

3 months ago