Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
osupratt
Posting Yak Master
238 Posts |
 Posted - 2012-02-16 : 10:53:46
|
| I have a piece of code a Navision developer sent me that I need help deciphering and re-writing in sql. This is for an 'avg days to pay' and an 'avg days to pay last year' fields. I need to write some CASE statements from this. Any help would be GREATLY appreciated as I am struggling with this. Not sure if this is visual basic or something. Here is what I received://>> Loops through the process 3 times, 1 is calculated for This Period on the Customer Entry Statistics screen, 2 This Year, 3 is Last year. FOR i := 1 TO 3 DO BEGIN CustLedgEntry2.RESET; CustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date"); CustLedgEntry2.SETRANGE("Customer No.","No.");//>> The entries are being filtered by Customer/Posting Date. The CustDateFilter that they are using for posting date needs to be set by what you need. For example, last year will need to be the date filter for ‘010111..123111’. CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter[i]); CustLedgEntry2.SETRANGE("Posting Date",0D,CustLedgEntry2.GETRANGEMAX("Posting Date")); DtldCustLedgEntry2.SETCURRENTKEY("Customer No.","Posting Date"); CustLedgEntry2.COPYFILTER("Customer No.",DtldCustLedgEntry2."Customer No."); CustLedgEntry2.COPYFILTER("Posting Date",DtldCustLedgEntry2."Posting Date"); DtldCustLedgEntry2.CALCSUMS("Amount (LCY)"); CustBalanceLCY := DtldCustLedgEntry2."Amount (LCY)"; HighestBalanceLCY[i] := CustBalanceLCY; DaysToPay := 0; NoOfInv := 0; CustLedgEntry2.SETFILTER("Posting Date",CustDateFilter[i]); IF CustLedgEntry2.FIND('+') THEN REPEAT j := CustLedgEntry2."Document Type"; IF j > 0 THEN NoOfDoc[i][j] := NoOfDoc[i][j] + 1; CustLedgEntry2.CALCFIELDS("Amount (LCY)"); CustBalanceLCY := CustBalanceLCY - CustLedgEntry2."Amount (LCY)"; IF CustBalanceLCY > HighestBalanceLCY[i] THEN HighestBalanceLCY[i] := CustBalanceLCY; // Optimized Approximation //>>This is the code which calculates the Days to Pay. IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND NOT CustLedgEntry2.Open //>>Checks if the invoice is not open THEN IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN //>> Checks if the closing date is greater than the posting date on the customer entry. UpdateDaysToPay(CustLedgEntry2."Closed at Date" - CustLedgEntry2."Posting Date") ELSE IF CustLedgEntry2."Closed by Entry No." <> 0 THEN BEGIN IF CustLedgEntry3.GET(CustLedgEntry2."Closed by Entry No.") THEN //>> If the Closed By Entry No. is not 0 then it calculates the Days to Pay adding the days to pay. The process also increments the No of Invoices variable. UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date"); END ELSE BEGIN CustLedgEntry3.SETCURRENTKEY("Closed by Entry No."); CustLedgEntry3.SETRANGE("Closed by Entry No.",CustLedgEntry2."Entry No."); IF CustLedgEntry3.FIND('+') THEN UpdateDaysToPay(CustLedgEntry3."Posting Date" - CustLedgEntry2."Posting Date"); END; UNTIL CustLedgEntry2.NEXT(-1) = 0; IF NoOfInv <> 0 THEN //>>If the NoOfInv is not 0, it divides the Days To Pay by the No Of Invoices. AvgDaysToPay[i] := DaysToPay / NoOfInv;END; |
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2012-02-16 : 15:36:55
|
| i figured it out. please disregard. thanks. |
 |
|
|
|
|
|
|
|
help translating code to sql