CODEWITHSUNDEEP
datetimemariadbcase
James Risner
James Risner

Reputation: 6075

MariaDB: Match requests with work performed where total > 0 using a case

I have a request table and a work table. For request type 1, 2, 4, or 5 I need to sum the work performed of type 6 or 7 where 6 represents effectively +1 and 7 represents -1. Exclude any requests where the request's work sum is <=0 or work were done before the most recent request.

The query details are:

The sample tables:

create table request
(
    Id             bigint not null,
    userId         bigint,
    type           bigint not null,
    creationDate   timestamp not null
);
create table work
(
    Id             bigint not null,
    type           bigint not null,
    creationDate   timestamp not null
);

The sample data:

insert into request (Id, userId, type, creationDate)
values  (4, 45, 2, '2022-12-12 11:02:17'),
        (9, 64, 2, '2022-12-12 01:01:18'),
        (2, 92, 2, '2022-12-11 21:36:36'),
        (2, 21, 2, '2022-12-11 21:25:54'),
        (1, 3, 2, '2022-12-11 21:13:58'),
        (7, 243, 2, '2022-12-11 21:04:05'),
        (8, 24, 2, '2022-12-11 21:01:23');
insert into work (Id, type, creationDate)
values  (3, 7, '2022-12-11 00:00:00'),
        (6, 7, '2022-12-11 00:00:00'),
        (11, 7, '2022-12-11 00:00:00'),
        (6, 7, '2022-12-11 00:00:00'),
        (1, 6, '2022-12-11 00:00:00'),
        (2, 6, '2022-12-11 00:00:00'),
        (11, 7, '2022-12-11 00:00:00'),
        (5, 7, '2022-12-11 00:00:00'),
        (1, 6, '2022-12-11 00:00:00'),
        (11, 7, '2022-12-12 00:00:00'),
        (4, 6, '2022-12-12 00:00:00'),
        (8, 7, '2022-12-12 00:00:00');

The attempted query:

select id, sum(total), type, creationDate from (
  select id, 0 as total, type, creationDate from request
  union
  select id, case type when 6 then 1 when 7 then -1 end as total, type, creationDate from work
) a where total > 0 group by id

This takes too long on live data, but works on small sets like this fiddle.

There is a challenge in the data, the timestamp for requests includes the time, but the work only has date with no timestamp.

The fiddle reports:

id sum(total) type creationDate
1 1 6 2022-12-11 00:00:00
2 1 6 2022-12-11 00:00:00
4 1 6 2022-12-13 00:00:00

However both 1 and 2 are invalidated because the timestamp of the request is technically greater than the work. The expected output should be:

id sum(total) type creationDate
4 1 6 2022-12-13 00:00:00

For id = 4, the work had the date of 2022-12-13 00:00:00 and the request was timestamped 2022-12-12 11:02:17.

Upvotes: 1

Views: 73

Answers (1)

Jonxag
Jonxag

Reputation: 787

One way to accomplish this is to use a subquery to join the two tables together and then group the results

Here's an example:

SELECT r.Id, SUM(CASE w.type WHEN 6 THEN 1 WHEN 7 THEN -1 END) as total, r.type, r.creationDate
FROM request r
JOIN (
  SELECT Id, type, creationDate
  FROM work
  WHERE type IN (6,7)
) w ON w.creationDate >= r.creationDate
WHERE r.type IN (1,2,4,5)
GROUP BY r.Id, r.type, r.creationDate
HAVING total > 0

Upvotes: 1

Related Questions