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