How to find the records of previous months in Yii 2

Yii 2.0 General Discussions
1

Hi,

I am using Yii2 basic. I have a module called groupsavingdetails where each month the user records the Group Saving. I have a form where user selects the Group, there is input box for entering year and dropdown to select month.

For eg: For Group 1 the date for formation is 2017-08-08. Here user should enter year greater than 2017 and select month greater than August. For Group 1 the year is 2017 and month is August, the user enters all other details. The controller checks whether there exists group saving records which are less than entered year and selected month. If not exists then four fields will be set to 0, otherwise previous months data will be considered for calculation.

How should I get the previous months record.

I have a below SQL which returns the previous months data but when the year is changed i.e 2018 and the first month is January then how should I retrieve 2017 December data.

$group = Yii::$app->db->createCommand('SELECT *
 FROM groupsavingdetails
 where groupsavingdetails.GroupId=:id
	 and groupsavingdetails.Year<=:year
 and groupsavingdetails.Month=:month-1'
	 )->bindValues([':id'=>$model->GroupId,':year'=>$model->Year,':month'=>$model->Month])
	 
 ->queryAll();
3

@SoftwareAccount

For eg: For Group 1 the date for formation is 2017-08-08. Here user should enter year greater than 2017 and select month greater than August. For Group 1 the year is 2017 and month is August, the user enters all other details. The controller checks whether there exists group saving records which are less than entered year and selected month. If not exists then four fields will be set to 0, otherwise previous months data will be considered for calculation.

I think your query is not achieving your requirement. Previous months will be

  • for the current year, it should be less than current month.
  • and all entries from previous years

if you want to find the previous months, you can simply change your code to this,

$group = Yii::$app->db->createCommand('SELECT *
          FROM groupsavingdetails
          where groupsavingdetails.GroupId=:id 
          and ((groupsavingdetails.Year = :year and groupsavingdetails.Month=:month) or
          (groupsavingdetails.Year < :year))'
          )->bindValues([':id'=>$model->GroupId,':year'=>$model->Year,':month'=>($model->Month - 1)])
          ->queryAll();