1 Topic by sonixax (edited by sonixax 2024-03-25 17:49:58)

  • Registered: 2014-10-04
  • Posts: 154

Topic: Query Adjustment: Filtering Grid Results by Year and Month-Year

Hi everyone,

I just have a question regarding my query. I want to have a query to filter some results in my grid based on a date time picker data.

This is my current query in a calculated field in the database and it works like a charm:

bookings.monthly_fee * ROUND((JULIANDAY('now') - JULIANDAY(start_date))/30)  - (SELECT ifnull(SUM(amount),0) from payments where payments.id_bookings = bookings.id)

Now I want to filter based on the year only and month-year instead of the current date. This means when I select the year only, the query should change the current date to the last day of that year. Similarly, when I select a month, it should set the current date to the last day of that month in the selected year.

How can I do it?

Additionally, how can I utilize the DateTimePicker component value in an SQL query form? I mean without any script. Alternatively, if I have to use scripts, how can I pass the results to my grid to display them?

Many thanks and have a lovely day!

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,786

Re: Query Adjustment: Filtering Grid Results by Year and Month-Year

Hi Sonixax,
Perhaps you can take a simpler approach (providing I understand your problem correctly).
Can you not just use two calculated fields (one for 'month' and one for 'year') formatted using 'strftime'?
Have a look at the attachment.
Regards,
Derek

Post's attachments

Attachment icon search by year and month.zip 443.94 kb, 101 downloads since 2024-03-25 

  • Registered: 2014-10-04
  • Posts: 154

Re: Query Adjustment: Filtering Grid Results by Year and Month-Year

derek wrote:

Hi Sonixax,
Perhaps you can take a simpler approach (providing I understand your problem correctly).
Can you not just use two calculated fields (one for 'month' and one for 'year') formatted using 'strftime'?
Have a look at the attachment.
Regards,
Derek

Hi
Thank you for the solution, it solved my Problem.:)