PolicyNo From Date To Date STATUS
W16266 04-10-2018 28-12-2018 NEW
WC65266 08-11-2018 28-12-2018 NEW
P/000159 01-10-2018 04-10-2018 NEW
WC425 16-10-2018 14-12-2018 NEW
P56 11-12-2018 07-09-2019 NEW
C399 01-06-2018 01-06-2018 new
C399 01-06-2018 30-11-2018 new
C399 29-11-2018 30-05-2019 NEW
C399 01-12-2018 30-05-2019 NEW
C399 31-05-2019 30-11-2019 renew
first 4 records should be lost because todate is less than today date and fromdate is not avaible under last 12 months
p56 is new because fromdate is under 12 months
last policy C399 first record date 01-06-2018 is new because there are multiple records records against this policy so when this policy came first time then new .. and other is renew but when last record of this policy todate if todate is less than today date then should be lost otherwise renew ..
this is the condition what i tried but not works
if(
todate< date(today())
and Month_Difference_2 <= 12 ,'NEW',
if(date(todate) > date(today()) and Min_Max_Diff >= 12,'renew','lost')) as STATUS
this is the script i have
Table:
LOAD POLICY_NO,
fromdate,
todate ,
Num(fabs (
( (year( fromdate) * 12) + month( fromdate) )
- ( ((year(today()) * 12) + month(today())) )
)) as Month_Difference_2
FROM temp_table.qvd (qvd);
table3:
NoConcatenate
LOAD *
Resident Table
Order By fromdate,todate asc;
DROP Table Table;
left Join (table3)
LOAD POLICY_NO,
Min(fromdate) as Min_Date,
Max(todate) as Expiry_Max_Date ,
((max(todate) - min(fromdate))/30) as Min_Max_Diff
Resident table3
Group By POLICY_NO;
Store table3 into [C:\Users\HP\Desktop\test\table_3.qvd](qvd);
Drop Table table3;
