sql equilvanet

[#1]

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

[senpradip007]

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)

[vikasmahajan]

Count({$<N_YEAR={'2014'},[Date]={">=$(=YearStart(max( [Date])))<=$(=max([Date]))"}>}DISTINCT(C_POLY_NUM))

Should give you result.

Vikas

[Digvijay_Singh]

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..