• Registered: 2020-01-19
  • Posts: 42

Topic: SQL search dates, From Sunday to Saturday, and count the dates

Hi everybody

I am attempting to do an SQL SEARCH such that I have a list of dates indicating the closure of client files. The quary is to see how many files have been closed during week periods. So that you could see that on any given weeks the quantity closed.

FROM                TO                        QTY
2026-01-01         2026-01-07           32
2026-01-08         2026-01-14           12


Best I could manage with the little program and help from the net was the below, but i keep getting blocked :

table : reference
column : Datecompletion
grid :  reports.Gridperformance       (and the property i think is sqlvalue)

Any help would be amazing cause im starting to loose my mind.

Tks.



reports.GridPerformance.sqlValue := SQLExecute(
    'SELECT ' +
    '    date(DateCompletion, ''-'' || ((strftime(''%w'', DateCompletion)+6) % 7) || '' days'') AS week_monday, ' +
    '    COUNT(*) AS total_in_week ' +
    'FROM referalDb ' +
    'GROUP BY week_monday ' +
    'ORDER BY week_monday;'
);

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,056

Re: SQL search dates, From Sunday to Saturday, and count the dates

Hi Jeff,
Can you attach your project?
Derek.

  • Registered: 2014-06-25
  • Posts: 879

Re: SQL search dates, From Sunday to Saturday, and count the dates

It's difficult to give a good answer without having access to your database but as far as what I see in the query is that the table you are querying does not match the description you give in the 2nd paragraph.

You might also need to update the sqlite.dll to get access to some functions which do not exist in the already old version of MyVisualDatabase.

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,436

Re: SQL search dates, From Sunday to Saturday, and count the dates

Your solution contains several errors:
- The table's sqlValue property is intended to retrieve the ID. ( https://myvisualdatabase.com/help_en/sc … egrid.html )
- The SQLExecute function is intended to retrieve a single value in a query, such as a record ID, date, or quantity. To process multiple records, use the SQLQuery function.
- Everything you're trying to do can be accomplished using standard MVD tools, using a calculated field ( https://myvisualdatabase.com/help_en/Datatypes.html ) or by using the "SQL query" button if you prefer to write queries manually.. ( https://myvisualdatabase.com/help_en/SQLquery.html )

  • Registered: 2020-01-19
  • Posts: 42

Re: SQL search dates, From Sunday to Saturday, and count the dates

I will attempt to make a small mock of the tables and forms.  Please do not pay attention to my script put up earlier. Its what the internet suggested and I wasn't going anywhere with it, simply because it was deep in code and I couldnt make heads or tails of it.

Be right back !

  • Registered: 2020-01-19
  • Posts: 42

Re: SQL search dates, From Sunday to Saturday, and count the dates

Here is a mock of what I want to do, very simple. Note my creative EXCEL image for what I want the report to look like. I also would like to either print on excel or FAST REPORT.

tks

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,056

Re: SQL search dates, From Sunday to Saturday, and count the dates

Hi Jeff,
Can you re-load your attachment - nothing's come through.
Derek.

  • Registered: 2020-01-19
  • Posts: 42

Re: SQL search dates, From Sunday to Saturday, and count the dates

Here you are...Note that the amount I put in for the grid is just random. I didnt count each accurately from my table.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,056

Re: SQL search dates, From Sunday to Saturday, and count the dates

Nothing seems to have been uploaded.
Can you try again.

  • Registered: 2020-01-19
  • Posts: 42

Re: SQL search dates, From Sunday to Saturday, and count the dates

Here you are..sorry.

Post's attachments

Attachment icon Report-qty-Week.zip 349 kb, 17 downloads since 2026-07-03 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,056

Re: SQL search dates, From Sunday to Saturday, and count the dates

Hi Jeff,
Sorry for the delay - life keeps getting in the way smile
As others have indicated, there are quite a few different approaches you could take.
But as always, I take the easy road (!) and would just use calculated fields.
Have a look at the attachment that has 3 options
Option 1
Most straightforward - a summary grid showing closed files (by week) sits side by side with the main grid that holds all the individual details.
Option 2
A variation on the above - the summary grid is hidden until you click the relevant button (click it again to hide).
Option 3
Again, a summary grid and a main grid.  This time, click any row in the summary grid and it filters the rows in the main grid (the gridlines also turn red as a visual reminder that a filter is active).  Click another row in the summary grid to see a different set of filtered results.  Clicking a second time on the filtered row clears all the filters, displays all the rows in the main grid and resets the gridlines back to black.
I created a 'calendar' table to hold 'weekstart' (Monday) and weekend' (Sunday) dates just to simplify things (and also to future-proof for any possible extra requirements).  Note that the 'calendar' table was simply populated by copy and paste from Excel (it was quickest) rather than add extra code to the script.
I didn't do anything on the report side of things as I don't know if you want to report everything or just weekly summaries of closed files.  But the raw data is all there so it should be pretty straightforward.
Hope this helps,
Derek.

Post's attachments

Attachment icon jeff mtl.zip 1.31 mb, 26 downloads since 2026-07-04 

  • Registered: 2020-01-19
  • Posts: 42

Re: SQL search dates, From Sunday to Saturday, and count the dates

Thank you Derek

That is exactly what I was looking for. The basic version is perfect.

Now comes the fun part—I need to take some time to go through it piece by piece, understand what you did, and see how all the parts connect together. From what I can tell already, there's a real elegance in its clean simplicity.

Looks like I have a great weekend project ahead of me. Thanks again!

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,056

Re: SQL search dates, From Sunday to Saturday, and count the dates

Most of the calculated fields were because I was trying to second-guess where you might want to go with the project (and to give the functionality of Option 3 in the previous attachment).
But if the basic option does all you want, then you only need a single calculated field (see attached) which simplifies things a lot (and gives you the weekend off - LOL!!).
Derek.

Post's attachments

Attachment icon jeff option 1 simplified.zip 444.97 kb, 27 downloads since 2026-07-04 

  • Registered: 2020-01-19
  • Posts: 42

Re: SQL search dates, From Sunday to Saturday, and count the dates

Hi Derek

I tried the simple option 1,  checked line per line, double checked...compared your code with mine on Microsoft word to make sure they were identical (except for the reference to the databases columns). And still can't get it to work.

I even thought it could be the date format, so I added a date dd/mm/yyyy field from my initial date field that is  yyyy/MM/dd. And still nothing.

At least I don't get an error message.

Any theories to help me out ?

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,056

Re: SQL search dates, From Sunday to Saturday, and count the dates

If you attach the latest version of your project I can have a quick look.
Derek.