How to transfer a date table into a month by month report table in SQL?
Hello
I'm struggling with some SQL, and actually don't know what is possible or not…
I have a table (eg. "TableDates") providing for example 3 fields:
- "id"
- "date_start"
- "date_end"
In my case, I am dealing with court cases, with the "date_start" field corresponding to the first hearing of a court case (identified with the "id" field), and "date_end", the last hearing (indicating so the conclusion of the case).
I would like to have a resulting table (eg. "TableOutstanding"), for example with 2 fields, telling in which months the cases are outstanding:
- "year_month"
- "id"
For example, if a case (id = 1) started on 2 January 2020 and ended on 5 May 2020, and another (id = 2) started on 2 February 2020 and ended on 27 February 2020, I would have for "TableDates":
"id" | "date_start" | "date_end"
1 | 02-01-2020 | 05-05-2020
2 | 02-02-2020 | 27-02-2020
I would like to have for "TableOutstanding" (or "QueryOutstanding"):
"year_month" | "id"
202001 | 1
202002 | 1
202002 | 2
202003 | 1
202004 | 1
202005 | 1
I guess that this could be done thanks to a macro, but if possible, I would better like to have this procedure stored in a query, in order to show the resulting table in a table control (and furthermore, in between, compute some stats, eg. how many outstanding case per month).
How to do it? Impossible? Possible with loops, CASE WHEN
or other functions…?
I think I can deal with the "year_month" field formatting (EXTRACT
function), but don't see how to transfer dates into _monthly report_.
Any tips or advise will be very welcome!
Thanks in advance for your help.
(sorry for the tables formatting, it seems that the Markdown usual table formatting is not supported here)
LibreOffice 6.4.2.2 - Base with an embedded Firebird database.
[Edit]
I add a file with an example "TablesDates" table, in case.
bdd_example_sql.odb
The "[SOLVED]" title note is not used on this site. Instead, add your own answer and then accept it. See guidelines for asking.
Thank you for your comment.
Just to confirm if I understand well, I should remove the answer part above and add it in a "self-answer" (that I should tick then as the selected answer), right?
Meanwhile, thank you for introduction to good tips on this QA forum.
Correct, and edit the title to remove "[Solved]".
I upvoted both the question and the answer, so now you should have enough karma points to mark the answer as accepted. Or I can do it after a week.
Thank you very much for your help and upvoting.
Actually, I will better mark the answer of @cpb as accepted as it does the job, and more efficiently than my suggestion.
Anyway, thanks for your comments.