CODEWITHSUNDEEP
sqloracle-databaseplsql
Amber M
Amber M

Reputation: 177

Using nvl and max to find unique values

I want to select only the row where the input date is between the beg_date and end_date, but as there are multiple rows where this can be true, I want to choose the maximum end_date if there are multiple rows. With the following info I would want the first row to be selected only even though the date is between the 2nd row's beg_date and end_date.

Additionally when I use the max function I can end up selecting the null value and a grp_id is not returned as the end_date is null. I will probably have to use the nvl function somewhere, but not sure where.

Customer_Nbr: 20080909

Date: 25-Jun-95

Customer_Nbr Grp_Id  Beg_Date   End_Date
12345689     9       21-MAY-95  24-FEB-97
12345689     9       21-MAY-95  24-FEB-96
12345689     9       27-JUN-04  null

So far this works to get a non-null end_date value.

SELECT grp_id
  FROM table1 
  WHERE customer_nbr = '12345689'
  AND to_date('06/27/2004','MM/DD/YYYY')
  BETWEEN beg_date AND nvl(end_date,to_date('12/31/9999','MM/DD/YYYY'));

Upvotes: 2

Views: 4470

Answers (2)

Jeff
Jeff

Reputation: 719

You could use this:

Where date between start_date and nvl(end_date,date)

Upvotes: 0

Michal Hože
Michal Hože

Reputation: 116

SELECT grp_id
  FROM (SELECT grp_id, MAX(end_date) over(PARTITION BY 1) AS max_end_date,
                end_date
           FROM table1
          WHERE customer_nbr = '12345689'
            AND to_date('06/27/2004', 'MM/DD/YYYY') BETWEEN beg_date AND
                nvl(end_date, to_date('06/27/2004', 'MM/DD/YYYY')))
 WHERE end_date = max_end_date

You need know max_end on every row of select, if u use analytic function over(partition..), u have max() function with same result on overy row and then only filter via end_date = max_end_date

(and input date 06/27/2004 must by same in nvl function)

Upvotes: 2

Related Questions