0

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.

FanoFN
  • 6,815
  • 2
  • 13
  • 33
Jewel John
  • 1
  • 1
  • What is your MySQL version? And what is the datatype for `Expirydate` column? Additionally, where exactly the ordering issue, MySQL or you end output, web app perhaps? – FanoFN Oct 18 '22 at 06:25
  • 'I want to display one paid status(1) column and then 3 unpaid(0) status column respectively.' - I don't get this is there some relationship between the rows you haven't told us? – P.Salmon Oct 18 '22 at 07:37
  • Mysql version: 10.4.20-MariaDB. We are working in Mysql and Laravel 8. Regarding the relationship, there is no relationship with the rows. Our clients would like to display 1 paid row and then 3 unpaid row then the display repeats. – Jewel John Oct 19 '22 at 12:12
  • _"Our clients would like to display 1 paid row and then 3 unpaid row"_ .. that might be achievable but I don't understand how to determine what is the next value in the order after a 1 paid row? I mean, look at your expected output.. The first `EntryDate='2022-10-11'` is with `paidstatus=1` then the next 3 values ordered are still logical somehow but after the next `paidstatus=1` it becomes .... – FanoFN Oct 20 '22 at 01:33

1 Answers1

0

You can do this to solve your problem.

SELECT *, ( table.Expirydate > NOW() ) as `Paid_Status` FROM table ORDER BY `Paid_Status` DESC;
Mr.Lister
  • 422
  • 3
  • 11