Question 89521 in PostgreSQL Question posted in
The official documentation can be found here.

SQL – Identify a record's status based on dates from another table (postgresql)

I have the following table called worker_log:

log_date total_workers store
2021-01-01 2 Ralphs
2021-02-01 0 Ralphs
2021-03-01 3 Ralphs
2021-01-01 3 Benny’s
2021-02-01 0 Benny’s
2021-03-01 1 Benny’s

For Ralphs, from 1/1/21 to 1/31/21, there were 2 total workers. From 2/1 to 2/28/21, there were 0 total workers. So on 2/1/21, the number of workers changed from 2 to 0. From 3/1/21 to the present, there were 3 total workers. So on 3/1/21, the number of workers changed from 0 to 3.

I have the following TEMPORARY table called request_log:

ticket_no request_date store
10000 2021-01-03 Ralphs
10001 2021-01-08 Ralphs
10002 2021-02-05 Ralphs
10003 2021-04-03 Ralphs
10004 2021-01-21 Benny’s
10005 2021-02-23 Benny’s
10006 2021-04-03 Benny’s

I need to create another table, called requests that shows if a request ticket was requested on a certain date, if there were workers available based on the log_date column worker_log table. The end result of the requests table should look like this:

ticket_no request_date store workers_available
10000 2021-01-03 Ralphs true
10001 2021-01-08 Ralphs true
10002 2021-02-05 Ralphs false
10003 2021-04-03 Ralphs true
10004 2021-01-21 Benny’s true
10005 2021-02-23 Benny’s false
10006 2021-04-03 Benny’s true

How would I go about this with a SQL script?

I don’t know how to implement it with LATERAL joins.

Tags:

3

Answers


  1. 0

    To solve this problem, it is necessary to:

    1. use an INNER JOIN to compare the common identifier between the two tables, in this case request_date and log_date

    2. use a CASE expression to create a new workers_available column which sets to True if the total_workers variable is greater than 0, and false if otherwise.

    As such, this can be accomplished as follows:

    select t1.ticket_no, t1.request_date, t1.store, case when t2.total_workers>0 then 'true' else 'false' end as workers_available from request_log as t1 inner join worker_log as t2 on t1.request_date=t2.log_date;

    To test this query, I created two rows of hypothetical data from the above tables – one where the total_workers was greater than 0 and one where it was not.

    select * from request_log;
 ticket_no |    request_date     | store  
-----------+---------------------+--------
     10000 | 2021-01-03 00:00:00 | Ralphs
     10001 | 2021-01-08 00:00:00 | Ralphs
(2 rows)

select * from worker_log;
      log_date       | total_workers | store  
---------------------+---------------+--------
 2021-01-03 00:00:00 |             2 | Ralphs
 2021-01-08 00:00:00 |             0 | Ralphs
(2 rows)

    Upon running the above query, we can see that the entry where total_workers=2 has an entry of True, while the entry where total_workers=0 has an entry of False.

    select t1.ticket_no, t1.request_date, t1.store, case when t2.total_workers>0 then 'true' else 'false' end as workers_available from request_log as t1 inner join worker_log as t2 on t1.request_date=t2.log_date;
 ticket_no |    request_date     | store  | workers_available 
-----------+---------------------+--------+-------------------
     10000 | 2021-01-03 00:00:00 | Ralphs | true
     10001 | 2021-01-08 00:00:00 | Ralphs | false
(2 rows)

    Please note that you may also find this tutorial on case expressions helpful.

    Login or Signup to reply.
  2. 0

    Using a left join lateral(...) on true you can run a "correlated subquery" that locates the most recent worker_log entry at or prior to the request. From that you get the number of workers available at that time and you can use the case expression as requested (other columns for reference only):

    select
      r.ticket_no   
    , r.request_date    
    , r.store   
    , case when w.total_workers > 0 then 'true' else 'false' end as workers_available
    , w.total_workers
    , w.log_date
from request_log as r
left join lateral (
  select total_workers, log_date
  from worker_log as wl
  where r.store = wl.store
  and r.request_date >= wl.log_date 
  order by 
       wl.log_date DESC
  limit 1
  ) as w on true
;

    Result

    ticket_no   request_date    store   workers_available   total_workers   log_date
10000       2021-01-03      Ralphs  true                2               2021-01-01
10001       2021-01-08      Ralphs  true                2               2021-01-01
10002       2021-02-05      Ralphs  false               0               2021-02-01
10003       2021-04-03      Ralphs  true                3               2021-03-01
10004       2021-01-21      Benny's true                3               2021-01-01
10005       2021-02-23      Benny's false               0               2021-02-01
10006       2021-04-03      Benny's true                1               2021-03-01

    DDL & DML

    CREATE TABLE worker_log
    (log_date timestamp, total_workers int, store varchar(8));

CREATE TABLE request_log
    (ticket_no int, request_date timestamp, store varchar(8));
    

INSERT INTO worker_log
    (log_date, total_workers,store)
VALUES
    ('2021-01-01 00:00:00', 2, 'Ralphs'),
    ('2021-02-01 00:00:00', 0, 'Ralphs'),
    ('2021-03-01 00:00:00', 3, 'Ralphs'),
    ('2021-01-01 00:00:00', 3, 'Benny''s'),
    ('2021-02-01 00:00:00', 0, 'Benny''s'),
    ('2021-03-01 00:00:00', 1, 'Benny''s')
;
INSERT INTO request_log
    (ticket_no, request_date, store)
VALUES
    (10000, '2021-01-03 00:00:00', 'Ralphs'),
    (10001, '2021-01-08 00:00:00', 'Ralphs'),
    (10002, '2021-02-05 00:00:00', 'Ralphs'),
    (10003, '2021-04-03 00:00:00', 'Ralphs'),
    (10004, '2021-01-21 00:00:00', 'Benny''s'),
    (10005, '2021-02-23 00:00:00', 'Benny''s'),
    (10006, '2021-04-03 00:00:00', 'Benny''s')
;

    see: http://sqlfiddle.com/#!15/63a4b1/1

    Login or Signup to reply.
  3. 0

    Usage of:

    • Left join – as described in below link.[1]

    Request_log table(left one):

    Checks all records for requested_date.
    If date is covered by example of second table and log_date matches.
    Then are applied at the beginning two cases from case expression.
    Last one is to cover records which might exists in request_log table but not in worker_log table and covers possible null records.

    [1] Why use left join at all

    • Case expression includes exact criteria of your need,presented by 3 options:
    1. Applies to false condition
    2. Applies to true condition
    3. Not applies to our previous conditions
    select  
r.ticket_no,
r.request_date, 
r.store, 
case  
when
   w.total_workers = 0 
     then 'false' 
when 
   w.total_workers > 0 
     then 'true'
else 'N/A' 
  end as workers_available 
from 
  request_log r 
left join 
  worker_log w on  r.request_date=w.log_date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search