CODEWITHSUNDEEP
phpsqldatabaselaravel
shree
shree

Reputation: 7

I have StartDate and EndDate of current month. I want to compare database records of every date which are in between those two dates

I want to get everydate records of current month and compare every date with created_at column. If records available for particular date then display those records. I have StartDate and EndDate and I want to display records of every day compare with date of each day and then if records available then display. How to do that.I tried with some query. But, it is not working.. 

//Display TAT Reports
public function reports() {

//Each date from current month     
for ($i = 1; $i <= date('t'); $i++) 
{
 $dates[] = date('Y') . "-" . date('m') . "-" . str_pad($i, 2, '0',STR_PAD_LEFT);
 }
$now = Carbon::now();
$monthStartDate = $now->startOfMonth()->format('Y-m-d'); //start date of month
$monthendDate = $now->endOfMonth()->format('Y-m-d'); //enddate of month

$recordsOnMonthlyBasis = DB::table('pickups')->select('tat', 'completed_at')
                            ->whereNotNull('tat')
                            ->whereDate('completed_at', '>=', "$monthStartDate")
                            ->whereDate('completed_at', '<=', "$monthendDate")->get()->toArray();
}

if I print $recordsOnMonthlyBasis then it will display all records in between Start And End Date. but, I want records of each day to compare with date in DB.

Any help Appreciated.

Upvotes: 0

Views: 243

Answers (3)

Maulik Shah
Maulik Shah

Reputation: 1050

Try with this, 

$fromDate1 = \Carbon\Carbon::parse($fromDate);
$toDate1 = \Carbon\Carbon::parse($toDate);

for ($fromDate1; $fromDate1 <= $toDate1; $fromDate1->addDays(1)) {
    // Your code 
}

Take your from date and loop till end date and add one day on each iteration.

Hope it helps :)

Upvotes: 0

PHP Geek
PHP Geek

Reputation: 4033

You can addon this query for get daily records.

 DB::table('pickups')->where(DB::raw("(DATE_FORMAT(completed_at,'%Y-%m-%d'))"), '=', Carbon::today()->format('Y-m-d'));

Upvotes: 0

Jithesh Jose
Jithesh Jose

Reputation: 1814

Change the query.

$now = Carbon::now();
$monthStartDate = $now->startOfMonth()->format('Y-m-d'); //start date of month
$monthendDate = $now->endOfMonth()->format('Y-m-d'); //enddate of month

$recordsOnMonthlyBasis = DB::table('pickups')->select('tat', 'completed_at')
                        ->whereNotNull('tat')
                        ->whereBetween('completed_at',[$monthStartDate,$monthendDate])
                        ->get()->toArray();

Upvotes: 0

Related Questions