Help me to query this. I have a Laravel app where have a table that has 3 columns (quartal, year, value).

For example, I want to get the value from quartal 3 years 2019 to quartal 2 years 2020 how to make the query for this case? for the year I can use between but for the quartal, It depends on the year.

here is my current query. but it does not work really well

DB::table($table)
->whereBetween('year',[$startYear, $endYear])
->whereBetween('quartal',[$startQuartal, $endQuartal])
->get();

CodePudding user response：

How about adding the quartal to the year?

DB::table($table)
    ->whereBetween(
        DB::raw('year   (quartal - 1) / 4'),
        [
            $startYear   ($startQuartal - 1) / 4,
            $endYear   ($endQuartal - 1) / 4
        ]
    )
    ->get();

CodePudding user response：

What you need to do is you need to create a compound column from quartal and year and then query that.

DB::table($table)
->whereRaw("concat(year,quartal) >= ?", "$startYear$startQuartal")
->whereRaw("concat(year,quartal) <= ?", "$endYear$endQuartal")
->get();

you can refer this for the sql quey https://www.db-fiddle.com/f/4GRH5RKqQi2Fc6wVvxK8C2/0

Hope this helps!