private	CommonTableExpression<Record2<OffsetDateTime, OffsetDateTime>>
			cteTimePeriod()
	{
		Field<Timestamp> nowAsMonth	= DSL.trunc(DSL.now(), DatePart.MONTH);
		Field<Timestamp> startAtTs	= nowAsMonth.sub(new PgInterval("1 month"));
		Field<Timestamp> endAtTs	= nowAsMonth.sub(new PgInterval("0 month"));

		// Values need to be compared to clt_rec.captured_at, which is time zone-aware. At least in
		// Postgres "now()" should be the same already as well, but jOOQ seems to model a timestamp
		// without time zone for that function for some reason.
		Field<OffsetDateTime> startAt	= startAtTs	.cast(SQLDataType.OFFSETDATETIME);
		Field<OffsetDateTime> endAt		= endAtTs	.cast(SQLDataType.OFFSETDATETIME);

		return CteDefs.TimePeriod.CTE
			.fields(CteDefs.TimePeriod.COL_START_AT.getName(),
					CteDefs.TimePeriod.COL_END_AT.getName())
			.as(DSL.selectFrom(DSL.values(DSL.row(startAt, endAt))));
	}

	private	CommonTableExpression<Record8<Integer, String, String, Long, OffsetDateTime, MeterMfctCode, String, MeterType>>
			cteRecsInTimePeriod()
	{
		CommonTableExpression<Record2<OffsetDateTime, OffsetDateTime>> timePeriod = this.cteTimePeriod();

		return CteDefs.RecsInTimePeriod.CTE
			.fields
			(
				CteDefs.RecsInTimePeriod.COL_RC_USER_ID.getName(),
				CteDefs.RecsInTimePeriod.COL_REAL_ESTATE_NR.getName(),
				CteDefs.RecsInTimePeriod.COL_CLT_MAC.getName(),
				CteDefs.RecsInTimePeriod.COL_CLT_REC_ID.getName(),
				CteDefs.RecsInTimePeriod.COL_CLT_REC_CAPTURED_AT.getName(),
				CteDefs.RecsInTimePeriod.COL_METER_MFCT_CODE.getName(),
				CteDefs.RecsInTimePeriod.COL_METER_READING_SERIAL.getName(),
				CteDefs.RecsInTimePeriod.COL_METER_TYPE.getName()
			)

			.as
			(
				DSL.select
				(
					READING_COMPANY.USER_ID	.as(CteDefs.RecsInTimePeriod.COL_RC_USER_ID),
					REAL_ESTATE.NUMBER		.as(CteDefs.RecsInTimePeriod.COL_REAL_ESTATE_NR),
					COLLECTOR.MAC_ADDRESS	.as(CteDefs.RecsInTimePeriod.COL_CLT_MAC),
					CLT_REC.ID				.as(CteDefs.RecsInTimePeriod.COL_CLT_REC_ID),
					CLT_REC.CAPTURED_AT		.as(CteDefs.RecsInTimePeriod.COL_CLT_REC_CAPTURED_AT),
					METER.MFCT_CODE			.as(CteDefs.RecsInTimePeriod.COL_METER_MFCT_CODE),
					METER.READING_SERIAL	.as(CteDefs.RecsInTimePeriod.COL_METER_READING_SERIAL),
					METER.TYPE				.as(CteDefs.RecsInTimePeriod.COL_METER_TYPE)
				)

				.from(timePeriod, CLT_REC)

				.join(CLT_REC_SRC)		.on(CLT_REC_SRC.ID				.eq(CLT_REC.ID))
				.join(COLLECTOR)		.on(CLT_REC_SRC.COLLECTOR		.eq(COLLECTOR.ID))
				.join(REAL_ESTATE)		.on(COLLECTOR.REAL_ESTATE		.eq(REAL_ESTATE.ID))
				.join(READING_COMPANY)	.on(REAL_ESTATE.READING_COMPANY	.eq(READING_COMPANY.ID))
				.join(OMS_REC)			.on(CLT_REC.OMS_REC				.eq(OMS_REC.ID))
				.join(METER)			.on(OMS_REC.METER				.eq(METER.ID))

				.where
				(
					CLT_REC.CAPTURED_AT.between
					(
						timePeriod.field(CteDefs.TimePeriod.COL_START_AT),
						timePeriod.field(CteDefs.TimePeriod.COL_END_AT)
					)

					// TODO access permissions + restrict to own real estates!
					.and(REAL_ESTATE.DELETED.isFalse())
					.and(METER.REPLACED_WITH.isNull())
				)
			);
	}

	private	CommonTableExpression<Record7<MeterMfctCode, String, MeterType, String, String, Integer, OffsetDateTime>>
			cteReAndCltPerMeterLid()
	{
		CommonTableExpression<Record8<Integer, String, String, Long, OffsetDateTime, MeterMfctCode, String, MeterType>> recsInTimePeriod = this.cteRecsInTimePeriod();

		return CteDefs.ReAndCltPerMeterLid.CTE
			.fields
			(
				CteDefs.ReAndCltPerMeterLid.COL_METER_MFCT_CODE.getName(),
				CteDefs.ReAndCltPerMeterLid.COL_METER_READING_SERIAL.getName(),
				CteDefs.ReAndCltPerMeterLid.COL_METER_TYPE.getName(),
				CteDefs.ReAndCltPerMeterLid.COL_REAL_ESTATE_NR.getName(),
				CteDefs.ReAndCltPerMeterLid.COL_CLT_MAC.getName(),
				CteDefs.ReAndCltPerMeterLid.COL_METER_LID_CNT.getName(),
				CteDefs.ReAndCltPerMeterLid.COL_METER_LID_LAST_CAPTURED_AT.getName()
			)

			.as
			(
				DSL.select
				(
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_MFCT_CODE),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_READING_SERIAL),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_TYPE),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_REAL_ESTATE_NR),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_CLT_MAC),
					DSL.count().as(CteDefs.ReAndCltPerMeterLid.COL_METER_LID_CNT),
					DSL
						.max(recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_CLT_REC_CAPTURED_AT))
						.as(CteDefs.ReAndCltPerMeterLid.COL_METER_LID_LAST_CAPTURED_AT)
				)

				.from(recsInTimePeriod)

				.groupBy
				(
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_MFCT_CODE),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_READING_SERIAL),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_METER_TYPE),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_REAL_ESTATE_NR),
					recsInTimePeriod.field(CteDefs.RecsInTimePeriod.COL_CLT_MAC)
				)
			);
	}