Clorae Clorae - 1 month ago 5
MySQL Question

How to select records based on 2 dates in mysql

i have a table tbl_remit.

|RemitNo|id|emp|emp_loc| emp_cat |ap_from| ap_to |amount|
| 1 |1 | a | PAL | PRIVATE |12/2015|12/2015| 50.00|
| 2 |1 | a | PAL | PRIVATE |01/2016|01/2016|100.00|
| 3 |1 | a | PAL | PRIVATE |02/2016|02/2016|100.00|
| 4 |2 | b | BTG |GOVERNMENT|01/2016|01/2016|200.00|
| 5 |2 | b | BTG |GOVERNMENT|02/2016|02/2016|200.00|
| 6 |3 | c | MAR | PRIVATE |12/2015|12/2015| 50.00|
| 7 |3 | c | MAR | PRIVATE |01/2016|01/2016|100.00|
| 8 |3 | c | MAR | PRIVATE |02/2016|02/2016|100.00|


I need to create a view_ap2015below and select all remittance from the year 2015 and below and where emp_cat is private and group it by emp_loc.

I need to get this table.

|emp_loc| emp_cat |amount|
| PAL | PRIVATE | 50.00|
| MAR | PRIVATE | 50.00|


I used this statement but the year 2016 remittance is still there.

SELECT Emp_loc, Emp_Cat, FORMAT(SUM(Amount),2) As Amount
FROM tbl_remit
WHERE AP_from <= '01/01/2015' AND AP_To <= '01/01/2015' AND Emp_Cat = 'PRIVATE'
GROUP BY Emp_Loc


Is it because the AP_From and AP_To Format of the date is only Month and Year?

Thank you.

Answer

As @Giorgos pointed out, you appear to be storing your dates using a text type rather than a date type. Never do this. But, since you have, you can use STR_TO_DATE to convert the text into date when doing comparisons.

Another problem you had is that you were selecting non-aggregate columns in your GROUP BY query.

SELECT Emp_loc, Emp_Cat, FORMAT(MIN(Amount),2) As Amount
FROM tbl_remit
WHERE STR_TO_DATE(AP_from, '%m/%Y') <= '2015-01-01' AND
      STR_TO_DATE(AP_to, '%m/%Y')   <= '2015-01-01' AND
      Emp_Cat = 'PRIVATE'
GROUP BY Emp_Loc, Emp_Cat
Comments