Complex query

[Mahamed_Qlik]

Hi Experts,

Can anyone help me write load script for below query:

select ID1 , ID2

FROM (SELECT A.ID1,
A.ID2,
A.TYPE_ID,
A.DeliverDate,
A.ResignDate,
TO_NUMBER (TO_CHAR (A.DeliverDate, 'yyyymmdd')) abc

FROM Table1 A, Table2 B

WHERE
AND A.ID1 = B.ID1
AND A.C_ID IN (SELECT C_ID FROM Table3 WHERE STATUS2 = 1)
AND (TO_CHAR (A.DeliverDate, 'YYYYMM') <= '202204'
AND (TO_CHAR (NVL (A.ResignDate, SYSDATE) ,'YYYYMM') >'202204')) ) TEMPTABLE

WHERE ABC =

(SELECT MAX (ABC)
FROM (SELECT TO_NUMBER (TO_CHAR (DeliverDate, 'yyyymmdd')) abc
FROM Table1 A
WHERE
A.C_ID IN (SELECT C_ID FROM Table3 WHERE STATUS2 = 1)
AND (TO_CHAR (A.DeliverDate, 'YYYYMM') <=202204
AND (TO_CHAR (NVL (A.ResignDate, SYSDATE),'YYYYMM') >202204))
AND ID1 = TEMPTABLE.ID1));

Regards,

Mahamed

[Andrew_Delaney]

HI Mahamed,

If you want help with this kind of thing it would be helpful to give us more context.

What are you trying to do exactly? Load items from a data source based on date?

Where are you currently in this process? What does your query currently do?

[Mahamed_Qlik]

Dear Andrew

thanks for reaching out.

Well, here I want to calculate count of ID1  based on DeliverDate, ResignDate which should be handle on dashboard side. 

Means needs to create master calendar first using these dates so when user select any date period on dashboard so for that period 

AND (TO_CHAR (A.DeliverDate, 'YYYYMM') <= '202204'
AND (TO_CHAR (NVL (A.ResignDate, SYSDATE) ,'YYYYMM') >'202204')) ) 

this condition needs to apply also make sure for selected period DeliverDate should be max.