Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
From below data i want to get month difference between whatever the minimum date is in fromdate and whatever the last max date in todate.. how i get that . .
POLIYNO From Date To Date
114371 21-05-2019 30-06-2019
114371 03-04-2019 30-06-2019
114371 08-12-2018 30-06-2019
114371 01-07-2019 31-12-2019
How you want the output to look like?, please paste required output in next column.
POLICYNO From Date To Date Month_Difference
CAR/114371 08-12-2018 30-06-2019 6
CAR/114371 03-04-2019 30-06-2019 2
CAR/114371 21-05-2019 30-06-2019 1
CAR/114371 01-07-2019 31-12-2019 11
In last record 11 because from min date 8-12-2018 and max date 31-12-2019 total 11 months is the difference .. so i want that difference only in last record of each policy
i tried this
Table:
LOAD POLICY_NO,
fromdate,
todate ,
Num(fabs (
( (year( fromdate) * 12) + month( fromdate) )
- ( ((year(todate) * 12) + month(todate)) )
)) as Month_Difference,
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
Resident table3
Group By POLICY_NO;
Store table3 into [C:\Users\HP\Desktop\test\table_3.qvd](qvd);
Drop Table table3;