how to join two tables with generate series

user_11740030 0
Viewed 185

I have two tables user and attendance. I want to get attendance for all users within specific time interval even though users doesn't have attendance on some dates like public holidays, ...

I used generate_series to get all dates but when joining and grouping i get only single value for dates with no attendance.

User table:
    id | name | phone
    1  | arun | 123456
    2  | jack | 098765 



Attendance table:
    id | user_id | ckeck_in            | check_out
    11 | 2       | 2021-12-30 07:30:00 | 2021-12-30 16:00:00
    21 | 1       | 2021-12-28 09:18:00 | 2021-12-28 17:45:00

so if i need to get all user attendance for the month of dec-2021, i want it like this

final_result:
user_id | name | date       | check_in            | check_out
1       | arun | 2021-12-01 | null                | null
2       | jack | 2021-12-01 | null                | null
1       | arun | 2021-12-02 | null                | null
2       | jack | 2021-12-02 | null                | null
...
1       | arun | 2021-12-28 | 2021-12-28 09:18:00 | 2021-12-28 17:45:00
2       | jack | 2021-12-28 | null                | null
...
1       | arun | 2021-12-30 | null                | null
2       | jack | 2021-12-30 | 2021-12-30 07:30:00 | 2021-12-30 16:00:00

PS: One user can have multiple check_in and check_out in a day.

Thanks in advance!

generate-series postgresql sql
2 Answers

You can use a cross join with a left join:

select t.*, a.check_in, a.check_out from 
  (select u.*, v from users u 
   cross join generate_series('2021-12-01', '2021-12-31', interval '1 day') v) t
left join attendance a on date(a.check_in)= date(t.v) and t.id = a.user_id

See demo.

user_73267380

First generate series for the required month. Then you can use left join.

select distinct user_id, name, md.date,  case when date::date=check_in::date then
check_in else null end as check_in, case when date::date=check_out::date then 
check_out else null end as check_out from month_data md, attendance_table a 
left join user_table u on u.id=a.user_id  order by date, user_id;

Fiddle Here

user_81937060
Related