URGENT HELP!!!

[#1]

Field Name	Value
SERIAL NUMBER	Example - A12B34
Contract Flag	This field will have potentially 3 values C – Under contract based on Contract Start Date and Contract End Date N – No contract based on the same dates P – Used to have contracts but does not have any contracts now

For example, the serial number A12B34, has a contract number 0012345678 , which has validity of 04/12/12 to 04/11/13, in this case, the flag will have P as this serial number does not have a contract right now but it used to have in the past.

I got 'C and 'N' but unable to get the 'P'...How to achieve it....Thanks in advance!!!

[manojkulkarni]

didn't understand the problem, could elaborate the problem..

[juleshartley]

More detail please...!

[#4]

Output:

Field Name	Value
SERIAL NUMBER	A12B34
Contract Flag	P

My Contract flag shud have the above three values as C,N,P which I need to be created.

For example,

Serial Number     Contract          Start Date        End Date          Contract flag

A12B34               12345           04/23/2012       04/23/2013               P

B12D34               54321           04/23/2012       04/23/2014               C

T12B35               22345           04/23/2012       04/23/2012               N

P->displays all  serial numbers ie., A12B34  because it used to have a validity but now it has expired as of today.

C->displays all  serial numbers ie., B12D34  because it  is still valid as of today.

N->displays all  serial numbers ie., T12B35   because it  start date is equal to end date.

[#5]

Reply given

[puttemans]

I'd make your end  date a serial number by 'Num(End Date) as End Date' in the script,

Then you can play around with it:

If((End Date = Start date), N,

     If ((End Date = Today()), C,

          If((End Date < Today()), P))) as Flag

[ashwanin]

I think you need to add values as per your serial id, which must contain P,C, N

You can also get it from Serial number of contract.

if(left([Serial Number],1)='A','P ',if(left([Serial Number],1)='B','C',if(left([NUMBER],1)='T','N')) as Flag

[#8]

Yes, I already tried that before, not getting the value P

[#9]

this is not what i need, johan is nearer to my issue

[juleshartley]

Can you explain why Johan's answer does not work?