SQL Logic and syntax

[SSSB]

I have a table that has contract number, yearmonth(start date of contract in the form of YYYYMM), Length ( duration of contract in years) Now I need an output table that gives me data of how many contracts are active for every month of the year strating from 1st contracts start yearmonth till last contract's last yearmonth. Example: If one contract is active from june 2022 to june 2023 it should give 1 as count for all the month from 202206 till 220306 and so on for all the contracts.

[Chanty4u]

Try something

Calendar:

LOAD Distinct

    YearMonth

Resident

(

    SELECT Min(YearMonth) as MinYearMonth, Max(YearMonth) as MaxYearMonth

    FROM Contracts

);

CONCATENATE (Contracts)

LOAD

    ContractNumber,

    YearMonth,

    Length

Resident Contracts;