Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I am trying to build set analysis of the below sql equivalent and the results from the sql are different from that of set. Please could someone guide me in the right direction.
SQL
select distinct c_poly_num
from mis_lpm_lr_earnedprem
where n_year=2014
and '30-JUN-2014' between d_start_date and d_end_Date
Set Analysis
Count({<N_YEAR={'2014'},Policy_Start_Date = {'<=30-06-2014'},Policy_End_Date = {'<=30-06-2014'}>} DISTINCT(C_POLY_NUM))
regards
Varghese
As per my understanding you want to count distinct "Policy No" with in a given period. You can try like:
Count({<N_YEAR={'2014'},Date = {'>=$(=yearstart(Max(Date))) <=$(=Max(Date))'}>} DISTINCT(C_POLY_NUM)
Count({$<N_YEAR={'2014'},[Date]={">=$(=YearStart(max( [Date])))<=$(=max([Date]))"}>}DISTINCT(C_POLY_NUM))
Should give you result.
Vikas
Count({<N_YEAR={2014},d_start_date={"<=$(=Date(makedate(2014,06,30),'Your Format'))"},d_end_Date={">=$(=Date(makedate(2014,06,30),'Your Format'))"}>}DISTINCT(C_POLY_NUM))
You may need to play with date format and may need small tweaking here and there before you get final expected outcome..