Level 39
You could adapt this query
Share your code if any difficulty
1 like
Jun 2, 2022
6
Level 1
How to get last record every month for a year
i have a lot of data in one month during this one year, i am confused how can i retrieve data on the last date of every month for one year in laravel.?
tried to use this code but it's not what I want
$dateS = Carbon::now()->startOfMonth()->subMonth(12);
$dateE = Carbon::now()->startOfMonth();
$q = DB::table('rep_daily')
->select('rdn_val','created_at','eks_id')
->whereBetween('created_at',[$dateS,$dateE])
->get();
Level 1
@sr57 sorry, this is not working for me, i still get all the month data. I just want the last record of every month. do you have another solution.?
SELECT r2.created_at, r1.rdn_val, r1.eksekutor_id FROM trade_daily r1 INNER JOIN ( SELECT MAX(created_at) AS last,rdn_val, created_at FROM trade_daily r2 GROUP BY date(created_at),rdn_val ) r2 ON r1.created_at=r2.last AND r2.rdn_val=r1.rdn_val;
Level 39
If you want to group by month, your inner sql should be
SELECT month(created_at) AS month,max(created_at) AS created_at FROM trade_daily GROU BY month(created_at);
1 like
Level 1
@sr57 this works, but when i add the rdn_val column, its contents are different from the date, it still contains the first date.
occur: [date = 31-05-2022], [rdn_val = 50], [month = 5], [date = 02-06-2022], [rdn_val = 150], [month = 6],
should: [date = 31-05-2022], [rdn_val = 100], [month = 5], [date = 02-06-2022], [rdn_val = 150], [month = 6]
how can that happen.? why the contents don't follow the date
Level 39
this works
Good
but when i add the rdn_val column
How ?
Probably same reason as this post
https://laracasts.com/discuss/channels/eloquent/completely-stuck-with-groupby-and-orderby
1 like
Level 1
@sr57 thanks, this is perfect... I hope you have a nice day sir..
Please or to participate in this conversation.