Replicate BETWEEN in set analysis

[divyasavanth]

Hi Folks,

Please help me replicate the below SQL into SET analysis

Select COUNT(*)  from TableName
where PGM_DESCR = 'PGM1'
and DISTRICT = 'XYZ'
and LAST_DAY(TO_DATE('May-20','Mon-YY')) BETWEEN STATUS_BEG_DATE AND  STATUS_END_DATE 

Please note May-20 is a Prompt and PGM_DESCR  and DISTRICT as well

STATUS_BEG_DATE and STATUS_END_DATE  are columns in TableName

I tried this but dint work:

=Count({$<"$=Num(Monthend([Month Filter AP]))"={">=($=Num(Max(STATUS_BEG_DATE))<=($=Num(Max(STATUS_END_DATE))"}>}CASE_ID)

Thanks for the help in advance.

[lironbaram]

hi 
for starter set analysis works on fields and not expressions 
so you'll need to have a date field for this 

second assuming there are different STATUS_BEG_DATE AND  STATUS_END_DATE  for different rows in the table
set analysis isn't your solution 
you should use an if statement instead 
Count({if(Num(Monthend([Month Filter AP])) >=Num(Max(STATUS_BEG_DATE)) and Num(Monthend([Month Filter AP]))<=Num(Max(STATUS_END_DATE)),CASE_ID))