CODEWITHSUNDEEP
sqloracle-database
pizzafeet
pizzafeet

Reputation: 1

Find date of first occurrence at the group level in SQL

I'm looking to build a query that retrieves the date field for the first time a particular occurrence is recorded, at the ID level. Below's 'FIRST_DATE' field is what I'm trying to achieve.

In this example, October 2020 is the first date that ID_FIELD 1 has a non-zero value, so Oct 2020 is applied as the FIRST_DATE for all ID_FIELD 1 rows. The intention is to be able to calculate the amount of time passed since an ID's value has changed from 0 to 1. Using Oracle SQL Developer.

ID_FIELD DATE_FIELD VALUE FIRST_DATE
1 01-JUL-20 0 01-OCT-20
1 01-AUG-20 0 01-OCT-20
1 01-SEP-20 0 01-OCT-20
1 01-OCT-20 1 01-OCT-20
1 01-NOV-20 1 01-OCT-20
1 01-DEC-20 1 01-OCT-20
2 01-JUL-20 0 01-AUG-20
2 01-AUG-20 1 01-AUG-20
2 01-SEP-20 1 01-AUG-20

Thank you!

Upvotes: 0

Views: 690

Answers (2)

Alex Poole
Alex Poole

Reputation: 191520

You can use the analytic version of min, combined with a case expression:

select id_field, date_field, value,
  min(case when value != 0 then date_field end) over (partition by id_field) as first_date
from your_table;

db<>fiddle, including a couple of versions that calculates the elapsed days, depending on eactly what you want to do.

Upvotes: 1

Del
Del

Reputation: 1599

SELECT id_field, MIN(DECODE(VALUE, 0, NULL, DATE_FIELD))
FROM table_name
GROUP BY id_field

You can then use that as part of your update statement.

Upvotes: 0

Related Questions