I want to display one paid status(1) column and then 3 unpaid(0) status column respectively.
Paid status is taken based on Expirydate, the date greater than now is considered paid status and date which is less than expiry date is considered unpaid status. Output can be based on Entry date.
My Table:
| Id | Expirydate | Entrydate |
|---|---|---|
| 1 | 2022-12-10 | 2022-10-11 |
| 2 | 2022-12-09 | 2022-09-01 |
| 3 | 2022-10-10 | 2022-10-18 |
| 4 | 0000-00-00 | 2022-10-17 |
| 5 | 2022-09-08 | 2022-10-01 |
| 6 | 0000-00-00 | 2022-10-15 |
| 7 | 0000-00-00 | 2022-09-09 |
| 8 | 2022-11-30 | 2022-09-10 |
Output would be:
| Id | Expirydate | Entrydate | Status |
|---|---|---|---|
| 1 | 2022-12-10 | 2022-10-11 | 1 |
| 3 | 2022-10-10 | 2022-10-18 | 0 |
| 4 | 0000-00-00 | 2022-10-17 | 0 |
| 5 | 2022-09-08 | 2022-10-01 | 0 |
| 2 | 2022-12-09 | 2022-09-01 | 1 |
| 6 | 0000-00-00 | 2022-10-15 | 0 |
| 7 | 0000-00-00 | 2022-09-09 | 0 |
| 8 | 2022-11-30 | 2022-09-10 | 1 |
NOTE: Considering now() as 2022-10-18. Also last there was only 2 unpaid status so the rest paid status would be shown.
1st attempt to calculate status:
SELECT table.* , CASE WHEN `expirydate` >= NOW() THEN "1" ELSE "0" END AS paidstatus
FROM table ORDER BY paidstatus DESC
Thanks.
Expirydatecolumn? Additionally, where exactly the ordering issue, MySQL or you end output, web app perhaps?EntryDate='2022-10-11'is withpaidstatus=1then the next 3 values ordered are still logical somehow but after the nextpaidstatus=1it becomes ....