Good day! I tried searching this problem all over stack overflow but I cant find the right keyword to search, I guess its better to ask you guys for help.
I am trying to create an attendance history that is base on two tables:
- attendance table
- calendar_dimension table
attendance table has data like below
| idnumber | punchin | punhout | date_created |
------------- ------------- ------------- ----------------
| 0002 | 7:30:00 | 16:00:00 | 2021-11-27 |
| 0003 | 7:30:00 | 16:00:00 | 2021-11-27 |
| 0004 | 7:30:00 | 16:00:00 | 2021-11-27 |
| 0002 | 7:30:00 | 16:00:00 | 2021-11-28 |
| 0003 | 7:30:00 | 16:00:00 | 2021-11-28 |
| 0004 | 7:30:00 | 16:00:00 | 2021-11-28 |
| 0001 | 7:30:00 | 16:00:00 | 2021-11-29 |
| 0002 | 7:30:00 | 16:00:00 | 2021-11-29 |
| 0003 | 7:30:00 | 16:00:00 | 2021-11-29 |
| 0004 | 7:30:00 | 16:00:00 | 2021-11-29 |
and calendar_dimension is just a basic table with dates, like so:
Please note that is a full month calendar:
| date | datestatus |
------------- ---------------
| 2021-11- 1 to 2021-11-25 |
| 2021-11-26 | Holiday |
| 2021-11-27 | |
| 2021-11-28 | |
| 2021-11-29 | |
| 2021-11-30 | |
What I have done so far is by joing calendar_dimension and attendance table.
Now, I have something like this: e.g. for 00001, which is working on my end:
| idnumber | punchin | punhout | date_created |
------------- ------------- ------------- ----------------
| 0001 | 7:30:00 | 16:00:00 | 2021-11-29 |
And for 0002:
| idnumber | punchin | punhout | date_created | Status |
------------- ------------- ------------- ---------------- ------------
| 0001 | 7:30:00 | 16:00:00 | 2021-11-27 | Present |
| 0001 | 7:30:00 | 16:00:00 | 2021-11-28 | Present |
| 0001 | 7:30:00 | 16:00:00 | 2021-11-29 | Present |
I want my history table looks like this by employee:
| idnumber | punchin | punhout | date_created | Status |
------------- ------------- ------------- ---------------- ------------
| 2021-11-1 to 2021-11-25 |
| 0001 | 7:30:00 | 16:00:00 | 2021-11-26 | Holiday |
| 0001 | 7:30:00 | 16:00:00 | 2021-11-27 | Absent |
| 0001 | 7:30:00 | 16:00:00 | 2021-11-28 | Absent |
| 0001 | 7:30:00 | 16:00:00 | 2021-11-29 | Present |
the 30th of November will be displayed tomorrow.
is there other way to make this possible? Thanks
CodePudding user response：
I'm assuming you're trying to compute the status column, correct?
Something along the lines of IF(datestatus = 'Holiday', 'Holiday', IFNULL(punchin, 'Absent', 'Present')) AS status should work, albeit it is a little inelegant.
You'll have to ensure you're not doing an inner join to get a row where there is no attendance for that id, ie, calendar_dimension c LEFT JOIN attendance a ON ...