How to transfer a date table into a month by month report table in SQL?

English
, , , , ,
#1

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

#2

The “[SOLVED]” title note is not used on this site. Instead, add your own answer and then accept it. See guidelines for asking.

#3

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.

#4

Correct, and edit the title to remove “[Solved]”.

#5

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.

#6

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.

#7

I’ve found a solution, with creation of an other table “Months” having 2 columns (“year” and “month”) and one line for each year-month (eg. 2020-05, 2020-04, etc.).
The following SQL command has worked:

SELECT
"m"."year" || RIGHT('0' || "m"."month",2) "yyyymm", /* build a "YearMonth" variable */
"m"."year" "yyyy",
"m"."month" "mm",
COUNT( /* count outstanding case at least one day in a month */
CASE WHEN /* date_begin ≤ yyyymm & date_end is null (meaning case is not yet concluded) */
	EXTRACT(YEAR FROM "t"."date_start") ||
	RIGHT('0' || EXTRACT(MONTH FROM "t"."date_start"),2) /* force a 2-digits month number */
	<=
	"m"."year" || RIGHT('0' || "m"."month",2)
	AND
	"t"."date_end" is null
THEN
	1 /* case is outstanding for this month */
WHEN /* date_begin ≤ yyyymm & date_end ≥ yyyymm */
	EXTRACT(YEAR FROM "t"."date_start") ||
	RIGHT('0' || EXTRACT(MONTH FROM "t"."date_start"),2) /* force a 2-digits month number */
	<=
	"m"."year" || RIGHT('0' || "m"."month",2)
	AND
	EXTRACT(YEAR FROM "t"."date_end") ||
	RIGHT('0' || EXTRACT(MONTH FROM "t"."date_end"),2) /* force a 2-digits month number */
	>=
	"m"."year" || RIGHT('0' || "m"."month",2)
THEN
	1 /* case is outstanding for this month */
ELSE
	null /* case is not outstanding for this month (either not yet existing, or yet concluded) */
END
) "outstanding"

FROM
"Months" "m",
"TableDates" "t"
GROUP BY "yyyymm", "m"."month","m"."year"
ORDER BY "m"."year" DESC, "m"."month" DESC

which is was I was looking for: to count how many cases are (and were) outstanding per month.

Probably (well, surely) there is a better and more beautiful way to do it (as for example to built the YYYYMM variables…), but it seems to work like this.

However, if someone has a better and more direct solution, I would be glad to improve myself in SQL!

Note: I consider that a case is outstanding in a particular month if outstanding at least one day is the targeted month. As an example, above case no. 1 is counted as outstanding for May 2020.

Thanks.

#8

hello greb,

i have added a calendar table which literally took less than a minute to create using calc.
it contains 1 column called “Date” which increments by 1 month for each of 60 rows, that’s 5 years of dates.

i have added 1 query which uses my “Calendar” and your “TableDates” table, it is saved and should be executed in direct mode which dictates that that the “Run SQL command directly” icon in the query design window must be activated.

once i understood what it is you wish to achieve then i realised that the solution is in fact very simple .
i concatenated the years and months in the FROM clause because it’s the best way to join tables by year & month.
you used the same concatenation in your own query within the main body of your code.
it’s worth mentioning that your derived table contains 68 rows while mine has 16, you also repeatedly extract date values. this means that my query is more efficient than yours.

this is the first time i have used Firebird & it seems to lack a few functions. i guess that where there is a will then there is a way but you cannot even rename a table!
CalendarTable.odb

#9

Hello pcb

Thank you for your suggestion, which indeed does the job, well, and much “properly” than what I proposed.

And the “between” function for joining tables will be really useful! I didn’t know it.

#10

hello greb,

Firebird has a LIST function but it is buggy.
it does work with integers but the order of the list values are undefined. an enhancement request has been made.
try this code.

select
	extract(year from c."Date") || ' ' || substring('JanFebMarAprMayJunJulAugSepOctNovDec' from extract(month from c."Date") * 3 - 2 for 3) "Year-Month",
	count(*) "Outstanding",
	list(d."id") "TableDate ID's"
from
	(select "Date", extract(year from "Date") || right('0' || extract(month from "Date"), 2) cd from "Calendar") c
join
	(
		select
			"id",
			extract(year from "date_start") || right('0' || extract(month from "date_start"), 2) fd,
			coalesce(extract(year from "date_end"), extract(year from current_date)) || right('0' || coalesce(extract(month from "date_end"), extract(month from current_date)), 2) td
		from "TableDates"
	) d
	on c.cd between d.fd and d.td
group by c."Date"
order by c."Date"
#11

Ok, thanks for sharing the tip. I’ve tested and seen how it yields.
Actually, I don’t need all the IDs of the cases related to a specific month. (but noted for the LIST Firebird function, thanks!)
Re-reading my question, I think I made a mistake: what I was looking for is a result table with fields “Year-Month” and “Outstanding”, and not “id”. Anyway, your answer did exactly fit to my need.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.