Level 55
@dikurr11 you have just 1 column in db - CheckinTime. Do you want us to imagine what are all others - Breakout;Breakin;Checkout ?
Do you want to get it from Checkin date? Or add new columns to db? Or?
Try to be more descriptive
Apr 6, 2022
3
Level 1
Query to show data checkin , checkout, breakin breakout in 1 row
Hello, i need help. i already try many query using case, etc. but still not solved. i have structure data like this :
ID;User_ID;CheckinTime;Date;
1|110|"2022-03-25 06:30:29"|"2022-03-25"|
2|110|"2022-03-25 12:12:31"|"2022-03-25"|
3110|"2022-03-25 13:02:11"|"2022-03-25"|
4|110|"2022-03-25 17:17:21"|"2022-03-25"|
5|111|"2022-03-25 06:32:21"|"2022-03-25"|
6|111|"2022-03-25 12:05:11"|"2022-03-25"|
7|111|"2022-03-25 13:01:32"|"2022-03-25"|
8|111|"2022-03-25 17:12:51"|"2022-03-25"|
And I want to show data like this:
ID;User_ID;Date;Checkin;Breakout;Breakin;Checkout
1|110|"2022-03-25"|"2022-03-25 06:30:29"|"2022-03-25 12:12:31"|"2022-03-25 13:02:11"|"2022-03-25 17:17:21"
2|111|"2022-03-25"|"2022-03-25 06:32:21"|"2022-03-25 12:05:11"|"2022-03-25 13:01:32"|"2022-03-25 17:12:51"
Anyone please help how to make this query.
Level 1
@SilenceBringer yes only have 1 column, and i want to get them in 1 row per date.
Level 55
@dikurr11 ugly, but should works
Model::goupBy('User_ID', 'Date')
->orderBy('id')
->select([
'User_ID',
'Date',
\DB::raw('(select CheckinTime from <tablename> t where <tablename>.User_ID = t.User_ID order by id asc limit 1) as Checkin'),
\DB::raw('(select CheckinTime from <tablename> t where <tablename>.User_ID = t.User_ID order by id asc limit 1 offset 1) as Breakout'),
\DB::raw('(select CheckinTime from <tablename> t where <tablename>.User_ID = t.User_ID order by id asc limit 1 offset 2) as Breakin'),
\DB::raw('(select CheckinTime from <tablename> t where <tablename>.User_ID = t.User_ID order by id asc limit 1 offset 3) as Checkout'),
])
->get();
Please or to participate in this conversation.