Can some help with equivalent set analysis expression for below sql query.
SELECT COUNT (DISTINCT A.PARTY_INSURED_ID)
FROM PD.BI_INS_STS A
WHERE A.INSURANCE_TYPE_ID <> 5
AND A.CONTRACT_STATUS = 1
AND A.CONTRACT_ID IN (SELECT CONTRACT_ID
FROM BI_INS_SALARY_CHANGE
WHERE STATUS = 1)
AND (( TO_CHAR (A.CONTRACT_START_DATE, 'YYYYMM') <= 202111
AND (TO_CHAR (NVL (A.RESIGNATION_DATE, SYSDATE) + 1,
'YYYYMM') >
202111)
OR ( TO_CHAR (A.CONTRACT_START_DATE, 'YYYYMM') = 202111
AND ( TRUNC (A.CONTRACT_START_DATE, 'MONTH')
- TRUNC (A.CONTRACT_END_DATE, 'MONTH')) =
0)));
I have tried to generate the master calendar for start date and end date then final set expression is like this but output is not matching with query
count({< ContractStartDateKey={"<= $(vFS_CY_Start)"}, InsuranceTypeID-={5}, CONTRACT_STATUS={1}, SAL_STATUS={1},
ResignationDateKey={"> $(vFS_CY_End)"} , MonthYear =,Year = , Month =>//} distinct InsuranceNumber)
+
<ContractStartDateKey={">= $(vFS_CY_Start) <= $(vFS_CY_End)"}, InsuranceTypeID-={5}, CONTRACT_STATUS={1}, SAL_STATUS={1}, DUMMY_CONTRACT_STATUS={1},MonthYear =,Year = , Month =
>} distinct InsuranceNumber)
in the above vFS_CY_Start = min(DateKey)
vFS_CY_End = max(DateKey)
if(date(Monthstart(CONTRACT_END_DATE))-date(Monthstart(CONTRACT_START_DATE))=0,1,0) as DUMMY_CONTRACT_STATUS,
