CODEWITHSUNDEEP
sqloracle-database
karthik
karthik

Reputation: 15

How to change status of a record depending upon the conditions for a particular number

I have below data in a table called t1,

number  status  date
100718  A       01-DEC-08
100718  A       14-NOV-16
10110   I       14-NOV-16
10110   A       19-SEP-18
10110   A       27-NOV-18
102965  I       01-DEC-08
102965  I       01-DEC-09

Now, if any status is A(Active) for a particular number then i need to set status as A for that number and date as latest active date and if all status I(inactive) for a particular number then i need to set status as I for that particular number and latest inactive date.

I want data like shown below,

number  status  date
100718  A       14-NOV-16
100718  A       14-NOV-16
10110   A       27-NOV-18
10110   A       27-NOV-18
10110   A       27-NOV-18
102965  I       01-DEC-09
102965  I       01-DEC-09

Please help me to complete my job. Thanks :)

Upvotes: 0

Views: 272

Answers (2)

Robert Kock
Robert Kock

Reputation: 6028

Maybe a bit old fashioned, but this should work (I didn't try it though):

SELECT   `number`,
         `status`,
         MAX(`date`) AS `date`
FROM     t1
WHERE    `status` = 'A'
GROUP BY `number`,
         `status`
UNION ALL
SELECT   `number`,
         `status`,
         MAX(`date`) AS `date`
FROM     t1 t1i
WHERE    `status` = 'I'
  AND    NOT EXISTS (SELECT * from t1 t1a WHERE t1i.`number` = t1a.`number` AND t1a.`status` = 'A')
GROUP BY `number`,
         `status`;

This returns for each number a single row.
Your example shows duplicated rows. I'm not sure if that's what you want.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

This can be achieved using MIN and LAST_VALUE analytic functions.

SELECT c_number,
       status,
       LAST_VALUE(c_date) OVER(
            PARTITION BY c_number,status ORDER BY c_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS latest_dt
FROM (
     SELECT c_number,
            CASE WHEN MIN (status) OVER( PARTITION BY c_number ) = 'A' THEN 'A'
                 ELSE status END --NVL(status,'I') or default for other than A & I
       AS status,
            c_date
     FROM t1
) s
ORDER BY status,
         latest_dt;

Basically, the Sub-query checks if at least one 'A' exists in the group and relies on the fact that status 'A' is the least status alphabetically. If you have other statuses in lower case, you may change the CASE condition by adding an UPPER()on the status.

The LAST_VALUE function with the windowing clause gets the latest dates for each combination of c_number and status derived from the sub-query among all the dates ( UNBOUNDED PRECEDING AND UNB... ) .

You have not mentioned what happens if there are 2 other statuses in a group apart from 'A', say 'I' and 'E' or NULL. If that's the case, slight tweaking may be required to get the correct value for LAST_VALUE.

Note: I used c_number and c_date for column names as date and number are reserved keywords and shouldn't be used for columns.

Demo

Upvotes: 1

Related Questions