I am trying to run a query between 2 tables where I need the
Start Date
Final Date BEFORE the Milestone 1
Final Date BEFORE the Milestone 2
Finish Date
| ID | Value | DATE |
|---|---|---|
| A | 12 | 1/1/2024 |
| A | 15 | 1/2/2024 |
| A | 20 | 1/3/2024 |
| A | 22 | 1/4/2024 |
| A | 17 | 1/5/2024 |
| A | 19 | 1/6/2024 |
| B | 7 | 2/1/2024 |
| B | 9 | 2/2/2024 |
| B | 5 | 2/3/2024 |
| B | 8 | 2/4/2024 |
| B | 2 | 2/5/2024 |
| ID | MileStone | DATE |
|---|---|---|
| A | Start | 1/1/2024 |
| A | 1 | 1/3/2024 |
| A | 2 | 1/5/2024 |
| A | Finish | 1/6/2024 |
| B | Start | 2/1/2024 |
| B | 1 | 2/4/2024 |
| B | Finish | 2/5/2024 |
What I want the data to look like
| ID | Value | Milestone |
|---|---|---|
| A | 12 | Start |
| A | 15 | 1 |
| A | 22 | 2 |
| A | 19 | Finish |
| B | 7 | Start |
| B | 5 | 1 |
| B | 2 | Finish |
I tried a min value won’t always work between 2 dates because I don’t want the minimum value, I want the value with the minimum date
3
Calculate date before Milestone
(select max(t1.date) maxdt from table1 t1 where t1.id=t2.id
and t1.date<t2.date)
There t2.date – Milestone date. Maximum date before Milestone – maximum t1.date where t1.date<Milestone date.
Then take value at this date
(select max(value) from table1 t1_2 where t1_2.id=t2.id
and t1_2.date=(select max(t1.date) maxdt from table1 t1 where t1.id=t2.id
and t1.date<t2.date)
Select value or max(value), if may be more then one row with this date.
See example
select ID,MileStone
,case when Milestone in ('Start','Finish') then -- take value for current date
(select max(value) from table1 t1_1 where t1_1.id=t2.id
and t1_1.date=t2.date
)
else
(select max(value) from table1 t1_2 where t1_2.id=t2.id
and t1_2.date=(select max(t1.date) maxdt from table1 t1 where t1.id=t2.id
and t1.date<t2.date)
)
end Value
-- ,date
from table2 t2