Delphi & SQLite: Get the records count per month for the last 12 months

Question

I'm trying to get the number of record grouped by month for the last 12 months including the current month. Not from the beginning of the current year.

I have a table EVENTS and a field WODATE. I can do this

  aFDQuery.Connection := TrackingDBConnection;
  aFDQuery.SQL.Clear;
  with aFDQuery.SQL do
    begin
      BeginUpdate;
      try
        Add('Select MONTH(MAX(WODate)) AS month, COUNT(*) AS count ');
        Add('from events where YEAR(WODATE) = 2020 ');
        Add('GROUP BY YEAR(WODate), MONTH(WODate) ORDER BY YEAR(WODate), MONTH(WODate) ASC');
      finally
        EndUpdate;
      end;

With that query, I get a result only for months where there are values other than 0 and obviously only for 2020

But I would like to get the last 12 months from now even with 0 like this

[01]    11/2019     12 
[02]    12/2019     24 
[03]    01/2020      0 
[04]    02/2020     12
[05]    03/2020     44
[06]    04/2020      0
[07]    05/2020     21
[08]    06/2020     37
[09]    07/2020      0
[10]    08/2020     15 
[11]    09/2020     45 
[12]    10/2020      6

I have tried this but I get a Syntax error

Add('from EVENTS where WODATE >= DATE(NOW, -12 month) ');

ESQLiteNativeException: [FireDAC][Phys][SQLite] ERROR: near "month": syntax error

Is it possible ? Thank you in advance for any suggestion

With the answer from Peter (See below), I get this

2019-11-01 - 2019-11-30    0
2019-12-01 - 2019-12-31    0
2020-01-01 - 2020-01-31    0
2020-02-01 - 2020-02-29    0
2020-03-01 - 2020-03-31    0
2020-04-01 - 2020-04-30    0
2020-05-01 - 2020-05-31    0
2020-06-01 - 2020-06-30    0
2020-07-01 - 2020-07-31    0
2020-08-01 - 2020-08-31    0
2020-09-01 - 2020-09-30    0
2020-10-01 - 2020-10-31   13

Which is perfect Thanks a lot Peter. You deserve a belgian beer

Could you make a SQLite database file example available somewhere so that I can experiment request with your data? — fpiette, Oct 27 '20 at 13:55
Hi François. I don't have such amount of data yet. Just building the queries for the Dashboard. But I can quickly build a sample db with fake data. Where / How to send that to you ? Email ? — Bob Baudewyns, Oct 27 '20 at 14:12
You can send it by email (You'll find my email easily searching on Google), but better to make it available on a download site so that other may test as well. — fpiette, Oct 27 '20 at 14:14
With comma before month, I get : ESQLiteNativeException: [FireDAC][Phys][SQLite] ERROR: no such column: NOW — Bob Baudewyns, Oct 27 '20 at 14:15
What version of SQLite do you use? Since 3.8.3 you can use CTE and VALUES clause. `SELECT sqlite_version()` — Peter Wolf, Oct 27 '20 at 14:17
As Peter Wolf said, values could be the way to go. For Example union join a fixed Number of Dataset into your Data. For Example for every first in Month one extra value with the SQL Command VALUES, and than taking your count -1 to get the correct value. — fisi-pjm, Oct 27 '20 at 14:25
Your test database has not even a format compatible with the request you gave :-( — fpiette, Oct 27 '20 at 15:34
`now`, `start of monts`, etc must be enclosed in single quotes in SQL text - as in my answer. Use two single quotes in string literal as an escape sequence for single quote, e.g.: `Add('(date(''now'', ''start of month'', ''-11 month''), date(''now'', ''start of month'', ''-10 month'', ''-1 day'')), ');`. See [How does one escape characters in Delphi string](https://stackoverflow.com/questions/302409/how-does-one-escape-characters-in-delphi-string). — Peter Wolf, Oct 27 '20 at 15:54
Using `Format` and `QuotedStr` functions you just overcomplicate the things. You only need to use two quotes instead of one in SQL text. I added the Delphi snippet at the end of my answer. Besides you use spaces to align values in SQL text (e.g. `' -9 month'`), but that's causing issues with SQLite `date` function which doesn't expect space there and produces some `NULL` values. — Peter Wolf, Oct 27 '20 at 17:29

Peter Wolf · Accepted Answer · 2020-12-16T11:33:06.127

This isn't much a Delphi question but rather SQLite question. Since version 3.8.3 you can use common table expressions (CTE) and the VALUES clause. Below I'm going to explain how.

First you can construct static date ranges table with 12 entries, where each entry represents a single month from past 12 months including the current:

VALUES
  (date('now', 'start of month', '-11 month'), date('now', 'start of month', '-10 month', '-1 day')),
  (date('now', 'start of month', '-10 month'), date('now', 'start of month', '-9 month', '-1 day')),
  (date('now', 'start of month', '-9 month'),  date('now', 'start of month', '-8 month', '-1 day')),
  (date('now', 'start of month', '-8 month'),  date('now', 'start of month', '-7 month', '-1 day')),
  (date('now', 'start of month', '-7 month'),  date('now', 'start of month', '-6 month', '-1 day')),
  (date('now', 'start of month', '-6 month'),  date('now', 'start of month', '-5 month', '-1 day')),
  (date('now', 'start of month', '-5 month'),  date('now', 'start of month', '-4 month', '-1 day')),
  (date('now', 'start of month', '-4 month'),  date('now', 'start of month', '-3 month', '-1 day')),
  (date('now', 'start of month', '-3 month'),  date('now', 'start of month', '-2 month', '-1 day')),
  (date('now', 'start of month', '-2 month'),  date('now', 'start of month', '-1 month', '-1 day')),
  (date('now', 'start of month', '-1 month'),  date('now', 'start of month', '-1 day')),
  (date('now', 'start of month'),              date('now', 'start of month', '+1 month', '-1 day'))

This gives (as of current date):

StartDate	EndDate
2019-11-01	2019-11-30
2019-12-01	2019-12-31
2020-01-01	2020-01-31
2020-02-01	2020-02-29
2020-03-01	2020-03-31
2020-04-01	2020-04-30
2020-05-01	2020-05-31
2020-06-01	2020-06-30
2020-07-01	2020-07-31
2020-08-01	2020-08-31
2020-09-01	2020-09-30
2020-10-01	2020-10-31

Using a CTE you can join your events table to the above list:

WITH DateRanges(StartDate, EndDate) AS (VALUES
  (date('now', 'start of month', '-11 month'), date('now', 'start of month', '-10 month', '-1 day')),
  (date('now', 'start of month', '-10 month'), date('now', 'start of month', '-9 month', '-1 day')),
  (date('now', 'start of month', '-9 month'),  date('now', 'start of month', '-8 month', '-1 day')),
  (date('now', 'start of month', '-8 month'),  date('now', 'start of month', '-7 month', '-1 day')),
  (date('now', 'start of month', '-7 month'),  date('now', 'start of month', '-6 month', '-1 day')),
  (date('now', 'start of month', '-6 month'),  date('now', 'start of month', '-5 month', '-1 day')),
  (date('now', 'start of month', '-5 month'),  date('now', 'start of month', '-4 month', '-1 day')),
  (date('now', 'start of month', '-4 month'),  date('now', 'start of month', '-3 month', '-1 day')),
  (date('now', 'start of month', '-3 month'),  date('now', 'start of month', '-2 month', '-1 day')),
  (date('now', 'start of month', '-2 month'),  date('now', 'start of month', '-1 month', '-1 day')),
  (date('now', 'start of month', '-1 month'),  date('now', 'start of month', '-1 day')),
  (date('now', 'start of month'),              date('now', 'start of month', '+1 month', '-1 day'))
)
SELECT
  DateRanges.StartDate,
  DateRanges.EndDate,
  COUNT(Events.ROWID) AS Count
FROM DateRanges
  LEFT OUTER JOIN Events ON (DateRanges.StartDate <= Events.WODate) AND (Events.WODate <= DateRanges.EndDate)
GROUP BY
  DateRanges.StartDate, DateRanges.EndDate
ORDER BY
  DateRanges.StartDate

See sample SQLFiddle.

Edit

I see that you're struggling to execute the SQL using TFDQuery, so I did it for you:

with aFDQuery.SQL do
begin
  BeginUpdate;
  try
    Add('WITH DateRanges(StartDate, EndDate) AS (VALUES');
    Add('(date(''now'', ''start of month'', ''-11 month''), date(''now'', ''start of month'', ''-10 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-10 month''), date(''now'', ''start of month'', ''-9 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-9 month''),  date(''now'', ''start of month'', ''-8 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-8 month''),  date(''now'', ''start of month'', ''-7 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-7 month''),  date(''now'', ''start of month'', ''-6 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-6 month''),  date(''now'', ''start of month'', ''-5 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-5 month''),  date(''now'', ''start of month'', ''-4 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-4 month''),  date(''now'', ''start of month'', ''-3 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-3 month''),  date(''now'', ''start of month'', ''-2 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-2 month''),  date(''now'', ''start of month'', ''-1 month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month'', ''-1 month''),  date(''now'', ''start of month'', ''-1 day'')),');
    Add('(date(''now'', ''start of month''),              date(''now'', ''start of month'', ''+1 month'', ''-1 day'')))');
    Add('SELECT DateRanges.StartDate, DateRanges.EndDate, COUNT(Events.ROWID) AS Count');
    Add('FROM DateRanges LEFT OUTER JOIN Events ON (DateRanges.StartDate <= Events.WODate) AND (Events.WODate <= DateRanges.EndDate)');
    Add('GROUP BY DateRanges.StartDate, DateRanges.EndDate');
    Add('ORDER BY DateRanges.StartDate');
  finally
    EndUpdate;
  end;
end;

Yes, indeed. I am struggling a lot with SQL syntax. Thanks a lot for your help. Your code is working perfectly — Bob Baudewyns, Oct 27 '20 at 17:31

score 1 · Answer 2 · answered Oct 27 '20 at 16:37

User Peter Wolf answer I designed this small modification answering - IMO - better the question:

WITH DateRanges(StartDate, EndDate) AS (VALUES
  (date('now', 'start of month', '-11 month'), date('now', 'start of month', '-10 month', '-1 day')),
  (date('now', 'start of month', '-10 month'), date('now', 'start of month', '-9 month', '-1 day')),
  (date('now', 'start of month', '-9 month'),  date('now', 'start of month', '-8 month', '-1 day')),
  (date('now', 'start of month', '-8 month'),  date('now', 'start of month', '-7 month', '-1 day')),
  (date('now', 'start of month', '-7 month'),  date('now', 'start of month', '-6 month', '-1 day')),
  (date('now', 'start of month', '-6 month'),  date('now', 'start of month', '-5 month', '-1 day')),
  (date('now', 'start of month', '-5 month'),  date('now', 'start of month', '-4 month', '-1 day')),
  (date('now', 'start of month', '-4 month'),  date('now', 'start of month', '-3 month', '-1 day')),
  (date('now', 'start of month', '-3 month'),  date('now', 'start of month', '-2 month', '-1 day')),
  (date('now', 'start of month', '-2 month'),  date('now', 'start of month', '-1 month', '-1 day')),
  (date('now', 'start of month', '-1 month'),  date('now', 'start of month', '-1 day')),
  (date('now', 'start of month'),              date('now', 'start of month', '+1 month', '-1 day'))
)
SELECT
  strftime('%m/%Y', DateRanges.StartDate) as Month,
  COUNT(Events.ROWID) AS Count
FROM DateRanges
  LEFT OUTER JOIN Events ON (DateRanges.StartDate <= Date(Events.WODate)) AND (Events.WODate <= Date(DateRanges.EndDate))
GROUP BY
  DateRanges.StartDate, DateRanges.EndDate
ORDER BY
  DateRanges.StartDate

Delphi & SQLite: Get the records count per month for the last 12 months

2 Answers2

Edit