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

user_7755619 0
Viewed 297

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

firedac firemonkey delphi sqlite
2 Answers

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;
user_115621880

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
user_1891030
Related