date difference

[sagarrahul]

	in i want
	current date subtract max date ie (current date - max date )
	after that when it output arrives if the differnce between date is 1 day ie
	eg (04-Feb-2016 - 03-Feb-2016) then i another column it should show difference as '1'and in another column its sholud its claim no difference
	and its max date in another coulmn  .

	CLM_INTIMATION_DATE	max date	claim no	current date
	28-Jan-2016	28-Jan-2016	RC-HS15-10335711	5/30/2016
	03-Feb-2016	03-Feb-2016	RC-HS15-10341139	5/30/2016
	08-Mar-2016	08-Mar-2016	RC-HS15-10343361	5/30/2016
	22-Mar-2016	22-Mar-2016	RC-HS15-10336529	5/30/2016
	08-Feb-2016	08-Feb-2016	RC-HS15-10336520	5/30/2016
	08-Feb-2016	08-Feb-2016	RC-HS15-10337526	5/30/2016
	15-Feb-2016	22-Mar-2016	RC-HS15-10343361	5/30/2016
	11-Feb-2016	12-Feb-2016	RR-HS15-10336940	5/30/2016
	11-Feb-2016	13-Feb-2016	RR-HS15-10336940	5/30/2016
		14-Feb-2016	RR-HS15-10341978	5/30/2016

	20-Jan-2016	20-Jan-2016	RR-HS15-10341246	5/30/2016
	09-Mar-2016	09-Mar-2016	RR-HS15-10341246	5/30/2016
	09-Mar-2016	07-Mar-2016	RR-HS15-10341246	5/30/2016

so pls help me in this as soon as possible

[mightyqlikers]

hi

could you please share expected out put and input in excel..

$@M.

[PrashantSangle]

Hi,

try,

for date difference

If(interval(currentDate - maxDate,'D')>0,interval(currentDate - maxDate,'D'))

for Claim No

If(interval(currentDate - maxDate,'D')>0,ClaimNo)

If your date contain timestamp also then use floor() to round the date value

Regards

[sagarrahul]

thank u max dreamer for ur time and help to me .

u gave its work nicely but my work is half done

now i wnt this

current date - max date that is ( 31-May-2016 - 30-May-2016 ) so its comes 1 and difference is also 1

when difference comes to 1 it must show it max date and along with claim no

[PrashantSangle]

Where ?? in same column??

then

If(interval(currentDate - maxDate,'D')>0,

interval(currentDate - maxDate,'D')=1,

maxDate & ' - ' & ClaimNo,interval(currentDate - maxDate,'D')))

Note : might be I am missed some bracket or semicolon. Please correct it

But logic to achieve your requirement is identify where date difference is 1 if it is equal to 1 then

concatenate two value using & &

Regards

[sagarrahul]

no they are in different different column so now wht

and its not working properly

[Kushal_Chawda]

What if difference is 0 or >1?

[sagarrahul]

dif  wil b one day day or more

[PrashantSangle]

Hi,

can tell us what is your expected output in provided sample data.??

So it will be easy to us to provide solution

Regards

[sagarrahul]

k will provide u