Routine Fix

[New2DS]

Hello All,

I need some help in fixing my routine. I am not getting the expected results.

In simple english the result I want is :

If Result is @TRUE and (CurrDate = PrevDate or MinCurrDate = MaxEndDt) and StCode in (20,29) then

I)If EntryDt is between start and enddate then count =1
2)If MaxEndDt = EndDate then Count =0
3) If EntryDate is null then count = 0

and in the end result I have to concatinate the equal flag to make sure the prev and current rows are the same.

Code: Select all

If CurrMCol = PrevMCol and CurrPCol = PrvPCol Then
   Result = @TRUE
   Equal = 'Y'
End
Else
  Result = @FALSE
   Equal = 'Y'
End


If (Result) Then
   If (CurrDate = PrevDate or MinCurrDate = MaxEndDt) then

       If (StCode = '20' OR StCode = '29' ) then
          If ( EntryDate >= StartDate and EntryDate <= EndDate) then
             Count = 1
          End
       End

       If (StCode = '20' OR StCode = '29' ) then 
          If (MaxEndDt = EndDate) then
             Count = 0
          End
       End
       
       If ( IsNull(EntryDate) or EntryDate = '') then
           Count = 0
       End
   End
End

Ans = Count : '|' : Equal

I tried to use the else conditions in between but no luck. I think I did something wrong in my code while using the else.
Do I have to convert all the dates into internal format before performing the conditions?

thanks

[ArndW]

Try looking at this, I just removed some redundancies and added an ELSE. I don't think your text and code match, but this might help you find the difference.:

Code: Select all

   If (CurrMCol = PrevMCol)AND(CurrPCol = PrvPCol) 
   Then
      If (CurrDate = PrevDate or MinCurrDate = MaxEndDt) then 
          If (StCode = '20' OR StCode = '29' ) then 
             If ( EntryDate >= StartDate and EntryDate <= EndDate) then Count = 1 
             Else If (MaxEndDt = EndDate) then Count = 0 
             Else If ( IsNull(EntryDate) or EntryDate = '') then Count = 0
             Else Count = 'IDONTKNOW'
      End  ;** of if date match
   End ;** of if a result
   Ans = Count : '|Y'

[Sainath.Srinivasan]

Set Count to something like '-1' in the very beginning so you will know whether something is happening to it.

For e.g. what if 'If (CurrMCol = PrevMCol)AND(CurrPCol = PrvPCol)' is FALSE

[New2DS]

Sai, Thanks.

Below is the req if the condition is false

If Not ((CurrMCol = PrevMCol)AND(CurrPCol = PrvPCol)) and MaxEndDt = EndDate and stCode in (20,29)
then 0 else 1

I tried to initialize the count to 0 at the beginning and all the result is getting concatenated with 0 and the flag 0|Y.


thanks

[Sainath.Srinivasan]

As you have multiple conditions, you need to mix and match them properly into a full logic set. i.e. something like 'if (a = b) then count = x else count = y'.

If your logic, there appear to be lots of 'if' without 'else'. It may be better to group them before you frame the logic.

[New2DS]

The initial count value will be equal to 0 and the count should return values based on the conditions below with a flag where the current and previous row are equal or not.

Code: Select all

If (CurrMCol <> PrevMCol) AND (CurrPCol <> PrvPCol) and Code 20,29 then count = 0 else count = 1

If (CurrMCol = PrevMCol) AND (CurrPCol = PrvPCol) and (CurrDate = PrevDate or MinCurrDate = MaxEndDt) and StCode in (20,29) then 

I)If EntryDt is between start and enddate then count =1 
2)If MaxEndDt = EndDate then Count =0 
3) If EntryDate is null then count = 0 
else no change in the count 

Do we have to convert the dates into internal format if the dates are passed as parameters or will they be automatically converted? How do we compare the dates inside the routine?

Hope I get some help

thanks

[xcb]

To compare you dates you can do this either of two ways.
1. Convert them into their internal date equivalent. Using the iconv function and the D code.

Code: Select all

InputDate = "13/04/2005"
InternalDate = Iconv(InputDate, "D/DMY[2,2,4]")

Read the basic pdf document that should have been installed along with your client for additional info on how to use this.

2. Convert your dates into strings of the format YYYY-MM-DD HH:MM:SS.

Code: Select all

InputDate = "2005-04-13 12:00:03"

Using either of the two methods you can compare your two dates using any logical comparison operator eg. and, or, =, <>, <, >, >=, <= to return a boolean (true\false) result.