my brain can't resolve a simple (i think) SQL problem and i need your help. I searched a lot of Stackoverflow but can't find the right answer for my case.
What i have :
table : expense_budgets
id
per_member
maximum
from_date
organization_id
organization_type_id
created_at
updated_at
CREATE TABLE IF NOT EXISTS `expense_budgets` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`per_member` double NOT NULL,
`maximum` double NOT NULL,
`from_date` date DEFAULT NULL,
`organization_id` bigint(20) unsigned DEFAULT NULL,
`organization_type_id` bigint(20) unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (1, 10, 10000, NULL, NULL, 2, '2020-09-14 16:43:52', NULL);
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (2, 5, 5000, NULL, NULL, 3, '2020-09-14 16:43:52', NULL);
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (3, 10, 40, NULL, NULL, 4, '2020-09-14 16:43:52', NULL);
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (4, 5, 5000, NULL, NULL, 5, '2020-09-14 16:43:52', NULL);
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (6, 15, 15000, '2020-09-25', NULL, 2, '2020-09-14 16:43:52', NULL);
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (7, 15, 15000, '2020-09-29', NULL, 2, '2020-09-14 16:43:52', NULL);
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (8, 15, 15000, '2020-09-26', NULL, 2, '2020-09-14 16:43:52', NULL);
INSERT INTO `expense_budgets` (`id`, `per_member`, `maximum`, `from_date`, `organization_id`, `organization_type_id`, `created_at`, `updated_at`) VALUES (9, 15, 15000, '2020-09-27', NULL, 2, '2020-09-14 16:43:52', NULL);
What i want : I have to get the closest expense_budget record for each of my organization types for a given date. So i apply a weight custom column to order them. If i have only one record with a null from_date that means that is the default value, if i have a date i want the closest lower from_date from now.
SELECT id, organization_type_id, case when from_date IS null then 1 when CURDATE() >= from_date THEN 3 ELSE 2 END AS weight
FROM expense_budgets
ORDER BY weight desc,from_date ASC;
But i can't find out how to integrate this in a subquery or a join to get a result to get this (if the current date is 2020-09-28) :
id;per_member;maximum;from_date;organization_id;organization_type_id;created_at;updated_at
2;5;5000;NULL;NULL;3;2020-09-14 16:43:52;NULL
3;10;40;NULL;NULL;4;2020-09-14 16:43:52;NULL
4;5;5000;NULL;NULL;5;2020-09-14 16:43:52;2020-09-28 09:46:24
9;15;15000;2020-09-27;NULL;2;2020-09-14 16:43:52;NULL
You got it ? only one record for each organization_type_id with the selected value based on the order made fron the case subquery.
In advance, thank you for your answer
Assuming the combination organization_type_id + from_date is unique:
with lastbudgets as (select eb2.organization_type_id, max(eb2.from_date) as from_date
from expense_budgets eb2
where eb2.from_date is null or eb2.from_date <= now()
group by eb2.organization_type_id)
select eb.*
from expense_budgets eb
inner join lastbudgets lb
on lb.organization_type_id = eb.organization_type_id
and ((lb.from_date is null and eb.from_date is null) or lb.from_date = eb.from_date)
UPDATED: extra logic on null case