CODEWITHSUNDEEP
mysql
toooouai
toooouai

Reputation: 3

How do I extract the start_date and end_date columns as ranges for each user?

target_user

| user_id | start_date   | end_date   |
|   1     |  2020-01-01  | 2020-01-10 |
|   2     |  2020-01-02  | 2020-01-14 |
|   3     |  2020-01-01  | 2020-01-16 |

amount_table

| user_id | amount | date       |
|    1    |  100   | 2020-01-01 |
|    2    |   50   | 2020-01-15 | 
|    1    |   50   | 2020-01-02 | 
|    3    |  100   | 2020-01-04 | 
|    3    |   50   | 2020-01-14 | 
|    1    |   50   | 2020-01-11 |

my idea

select
    target_user.user_id,
    sum(amount) as amount
from
    target_user
join
    amount_table
on
    target_user.user_id = amount_table.user_id
where
    amount_table.date between target_user.start_date and target_user.end_date
group by 1

result

○...summed ×...not summed

| user_id | amount | date       |
|    1    |  100   | 2020-01-01 | ○
|    2    |   50   | 2020-01-15 | ○
|    1    |   50   | 2020-01-02 | ○
|    3    |  100   | 2020-01-04 | ○
|    3    |   50   | 2020-01-14 | ○
|    1    |   50   | 2020-01-11 | ○

Perhaps this extracts the period from 2020-01-01 to 2020-01-16 for all users?

ideal

| user_id | amount | date       |
|    1    |  100   | 2020-01-01 | ○
|    2    |   50   | 2020-01-15 | ×
|    1    |   50   | 2020-01-02 | ○
|    3    |  100   | 2020-01-04 | ○
|    3    |   50   | 2020-01-14 | ○
|    1    |   50   | 2020-01-11 | ×

How do I extract the start_date and end_date columns as ranges for each user?

Upvotes: 0

Views: 35

Answers (1)

Akina
Akina

Reputation: 42728

ideal

| user_id | amount | date       |
|    1    |  100   | 2020-01-01 | ○
|    2    |   50   | 2020-01-15 | ×
|    1    |   50   | 2020-01-02 | ○
|    3    |  100   | 2020-01-04 | ○
|    3    |   50   | 2020-01-14 | ○
|    1    |   50   | 2020-01-11 | ×

SELECT a.user_id, a.amount, a.`date`, CASE WHEN t.user_id IS NULL
                                           THEN '×'
                                           ELSE '○'
                                           END ` `
FROM ( SELECT user_id, amount, `date`, ROW_NUMBER() OVER () rn
       FROM amount_table ) a
LEFT JOIN target_user t ON a.user_id = t.user_id
                       AND a.`date` BETWEEN t.start_date AND t.end_date
ORDER BY a.rn

user_id | amount | date       |    
------: | -----: | :--------- | :--
      1 |    100 | 2020-01-01 | ○
      2 |     50 | 2020-01-15 | × 
      1 |     50 | 2020-01-02 | ○
      3 |    100 | 2020-01-04 | ○
      3 |     50 | 2020-01-14 | ○
      1 |     50 | 2020-01-11 | ×

db<>fiddle here

Upvotes: 1

Related Questions