MAX Date range in SQL

Question

MAX Date range from multiple records

I have a table like below.

Customer Publication Start Date   End Date   
 1 S0048    DLD         01-JAN-2013   15-NOV-2013
 2 S0048    DLD         03-MAR-2013   31-DEC-2013
 3 S0048    SLD         01-FEB-2013   31-DEC-2013
 4 S0048    SLD         01-FEB-2013   30-NOV-2013
 5 S0145    DLD         01-JAN-2013   01-MAR-2013
 6 S0145    DLD         02-FEB-2013   28-NOV-2013

I need to get the result by giving end date range. Ex: If input : end date 01-NOV-2013 to 30-NOV-2013 (Searching who are expiring with in NOV month)

Results should be

S0145    DLD         02-FEB-2013   28-NOV-2013

Please note that 1st and 4th records should not be there as they have renew their publication subscription period.

How can I get these result. please help.

Dear All, I can get the data for a given date range. But it returns 1 , 4 ,6 . I need only record 6 to be return. Cause i need the latest date range for a specified customer for a publication.(Highest date range of each customer each publication). As to my input (searching the November month end dates). 1 is not necessary as that customer had extended the 'DLD' period in 2. it is same in 4th record. 4th do not need as it extended (renew) in record 3

Answer 1

You could probably use STR_TO_DATE for this purpose:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

For instance:

SELECT MAX(STR_TO_DATE(`Start Date`, '%d-%b-%Y')) FROM customers

The format specifiers (%d, etc.) are listed here:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Answer 2

In Tsql you can write a query as:

SELECT T2.customer, 
       T2.publication, 
       T2.startdate, 
       T2.enddate 
FROM   table1 T2 
       INNER JOIN (SELECT t1.customer, 
                          Max(t1.enddate) AS MaxEnddate 
                   FROM   table1 T1 
                   GROUP  BY t1.customer, 
                             T1.publication) T 
               ON T.customer = T2.customer 
                  AND T.maxenddate = T2.enddate 
WHERE  T2.enddate BETWEEN '01-NOV-2013' AND '30-NOV-2013' 

Update:

select hca_agent.account_number agency_code,
       hca_sub.account_number subscriber_code,
       hp_sub.party_name subscriber_name,
       sum(xssl.quantity) qty,
       msi.segment1 Publication_name,
       xssl.start_date Period_from,
       xssl.end_date Period_to-------- I need to get the MAX end date----with the relevant start date 
       from xxwnl_st_subscr_line xssl
       inner join 
       (
        select xssl_inner.subscriber_cust_account_id,
                MAX(xssl_inner.end_date) as MaxEnddate
        from xxwnl_st_subscr_line xssl_inner
        group by xssl_inner.subscriber_cust_account_id) T on
       T.subscriber_cust_account_id = xssl.subscriber_cust_account_id 
       and T.MaxEnddate =  xssl.end_date,
       xxwnl_supp_temp_line xsl,    ----others 
       xxwnl_supp_temp_header xsh,-----others 
       hz_cust_accounts hca_sub,----for customer----- 
       hz_parties hp_sub,----others 
       mtl_system_items_b msi,----for publication----- 
       hz_cust_accounts hca_agent,----others 
       hz_parties hp_agent----others 
       where xssl.supply_line_id = xsl.supply_line_id 
       and xsl.header_id = xsh.header_id 
       and hca_sub.cust_account_id = xssl.subscriber_cust_account_id 
       and hp_sub.party_id = hca_sub.party_id 
       and msi.inventory_item_id = xsl.inventory_item_id 
       and msi.organization_id = oe_sys_parameters.value('MASTER_ORGANIZATION_ID', fnd_profile.value('ORG_ID')) 
       and hca_agent.cust_account_id = xsh.cust_account_id 
       and hp_agent.party_id = hca_agent.party_id 
       and hca_agent.customer_class_code = 'SAGENT' and hca_agent.account_number like '95%' 
       and xssl.end_date between TO_DATE('&FROM_DATE','DD/MON/RRRR')
       AND TO_DATE('&TO_DATE','DD/MON/RRRR') 
       group by hca_agent.account_number , hca_sub.account_number , hp_sub.party_name , msi.segment1 , xssl.start_date , xssl.end_date 
       order by 1,2,3