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.
3
Answers
To solve this problem, it is necessary to:
use an INNER JOIN to compare the common identifier between the two tables, in this case request_date and log_date
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:
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.
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.
Please note that you may also find this tutorial on case expressions helpful.
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):Result
DDL & DML
see: http://sqlfiddle.com/#!15/63a4b1/1
Usage of:
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