Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;