CODEWITHSUNDEEP
sqlplsqloracle11g
zundarz
zundarz

Reputation: 1594

How to get summary query using arbitrary expiration dates in second table?

Using PL/SQL on Oracle 11g How can I write query to return desired result? I want sum of detail using the expiry_date of Licenses table. All transactions in each group by must occur before the expiry_date.

Licenses

license_type vendor_code  funding_code  license_expiry_date 
---------------------------------------------------------------------------
CAB           1001        XR1           07/09/2011 4:23:18 pm
CAB           1002        XR2           07/06/2012 10:22:16 am

License_detail
register_period  license_type   active_date  fee  
---------------------------------------------------------------------
2012A            CAB             06/01/2011  25.00
2012A            CAB             07/01/2011  25.00
2012A            CAB             08/01/2011  30.00
2012A            CAB             09/01/2011  30.00
2012A            CAB             07/10/2012  40.00
2012A            CAB             07/11/2012  40.00

Desired Result

Period    Type   Funding   Vendor  TotalFees Expiry_Date
---------------------------------------------------------
2012A      CAB   XR1       1001     50.00    07/09/2011 4:23:18 pm
2012A      CAB   XR2       1002     60.00    07/06/2012 10:22:16 am
2012A      CAB   <null>    <null>   80.00    <null>

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

What you need is an effective date and an end date for the licenses. You can then join this into the license detail, with something like this:

select ld.period, ld.type, l.funding_code, l.vendor_code, sum(fee) as fee,
       l.license_expiry_date
from license_detail ld left outer join
     (select l.*,
             lead(license_expiry_date, 1) over (partition by license_type
                                                order by license_expiry_date)
                                               ) as nextdate
      from licenses l
     ) l
     on ld.license_type = l.license_type and
        ld.active_date >= coalesce(license_expiry_date, ld.active_date) and
        ld.active_date < coalesce(nextdate, to_date('9999-01-01', 'yyyy-mm-dd'))
group by ld.period, ld.type, l.funding_code, l.vendor_code, l.license_expiry_date

Upvotes: 3

Related Questions