I need to write an sql query where i can get the exact effective date for each id & date.
main_data_table
| uuid | Date |
|---|---|
| 121 | 2023-02-25 |
| 121 | 2023-03-17 |
| 121 | 2023-07-22 |
| 121 | 2023-10-30 |
image_table
| uuid | effective_Date | Img |
|---|---|---|
| 121 | 2023-01-01 | img_1 |
| 121 | 2023-06-01 | img_2 |
| 121 | 2024-01-01 | img_3 |
| 121 | 2024-09-01 | img_4 |
Expected_Output :
| uuid | Date | effective_Date | Img |
| ——– | ——– |————- |_____|
| 121 | 2023-02-25| 2023-01-01 | img_1 |
| 121 | 2023-03-17| 2023-01-01 | img_1 |
| 121 | 2023-07-22| 2023-06-01 | img_2 |
| 121 | 2023-10-30| 2023-06-01 | img_2 |
What i tried was :
SELECT mdt.uuid, mdt.Date, img.effective_Date, img.Img
FROM main_data_table mdt
LEFT JOIN image_table img ON mdt.uuid = img.uuid
WHERE mdt.Date >= img.effective_Date;
But this effective date logic fails, i am confused as what should be the correct logic, how do i use previous or next date to get img’s expiry date, like for img1 effective date is 2023-01-01, so how should i get next date and subtract 1 day from it and get img_1’s expiry.
And what should i do when there is no next date, in this case i don’t have any expiry, i am stuck.
If there’s any different logic you can help me with, please do.