Date Format -Urgent

[krishna20]

Hi ,

I'm having two dates which are in integer format . I have calculation with these dates.

Please help me to convert these dates for exact calculation. I'm having sql query based on this i converted these dates.But exact result is not getting.

SELECT

ROUND((TP_POL_TO_DT - TP_POL_FM_DT),2) DIFF,

ROUND((SYSDATE  - TP_POL_FM_DT  ),2) REMAINING_DAYS,

SUM(TP_PREM_LC_1)- SUM(TP_DISC_LC_1) + SUM(TP_LOAD_LC_1) GROSS_PREM,

FROM T_GI_POLICY

WHERE

TP_COMP_CODE='001'

AND TP_CUST_CODE='111502'

GROUP BY TP_POL_FM_DT,TP_POL_TO_DT

Result is :

365

104.51

1018848

I'm converting one of the date above query as below

=(Round(Date(Today(),'DD/MM/YYYY'),2)-Round(Date(TP_POL_FM_DT,'DD/MM/YYYY'),2)) as Remaining Days

My Result is  : 364 Instead of 365 and  102 Instead of 104.51 I'm getting this. Please help me out

Regards

Krishna

[#2]

Try like below,

As Gysbert suggest like below,

LOAD

*,

If(Floor(TP_POL_TO_DT)>Floor(Now()),

(SUM(TP_GrossPremium_LC_1)/((Floor((TP_POL_TO_DT))-(Floor(TP_POL_FM_DT)))))*(Floor((Now()))-Floor((TP_POL_FM_DT))), TP_GrossPremium_LC_1) as TP_EarnedPremium_LC_1

FROM T_GI_POLICY

WHERE

TP_COMP_CODE='001'

AND TP_CUST_CODE='111502'

GROUP BY TP_POL_FM_DT,TP_POL_TO_DT

[krishna20]

Hi Harsha,

No Luck !!

[Gysbert_Wassenaar]

You need to use LOAD ... RESIDENT ... if you want to load data from a table you created earlier. If you don't want to keep that first table then perhaps you can do a preceding load instead:

MyResultTable:

LOAD *, B+C as D; \\ this is the preceding load that uses the data from the next load statement as its source

LOAD A, B, C FROM MyTable;

[#5]

Hi,

Your apporach is like below,

OldTable:

Load *

from source;

New_Table:

LOAD

Key,

If(Floor(TP_POL_TO_DT)>Floor(Now()),

(SUM(TP_GrossPremium_LC_1)/((Floor((TP_POL_TO_DT))-(Floor(TP_POL_FM_DT)))))*(Floor((Now()))-Floor((TP_POL_FM_DT))), TP_GrossPremium_LC_1) as TP_EarnedPremium_LC_1

resident OldTable GROUP BY Key;

*Post your qvd & app on my mailid.

[krishna20]

Hi Gysbert,

Let me try as per your suggestion and let you  know the result.

[krishna20]

Hi Gysbert ,

I tried in both ways Preceding load and Resident concept .But, Same error it raising.I don't know where i'm doing the mistake.Please Identify the mistake in attached sample app.

[krishna20]

Hi Friends,

Any Idea on this.Please suggest me.

[#9 · Accepted solution]

Hi Krishna,

Hope your issue will be resloved.

PFA,

Thanks

[krishna20]

Thank You all !!!

Regards

Krishna