June 24, 2014 at 3:48 pm
I would like to ask for help to write a query that helps me calculate the number of days (approx) that it takes to a customer to pay an invoice.
Document Types
1 Payment
2 Invoice
3 Credit Memo
4 Finance Charge Memo
5 Reminder
6 Refund
There are three methods to consider when calculating the days to pay logic.
Method 1 - Simple
Look for Document Type 2 (Invoice), if "closed at date" > "posting date" then number of days = ("closed at date" - "posting date")
Method 2 - A Document Type 1 (payment) closes a Document Type 2 (Invoice)
For this method the formula would be: Payment Record (1) "posting date" - Invoice Record (2) "posting date"
Method 3 - An Invoice closes the payment.
On a payment entry “closed by entry no.” refers to an Invoice entry.
a. In our code we are not on the payment looking for the invoice, we are on the invoice.
i. Because of this we need to find the entry that our current invoiced has closed.
I am taking this from a page that has the pascal code that I need to translate to SQL.
I know this is a sql server forum, but I am including that code in case it helps to get sql accurate. I am really not familiar with the syntax.
IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND
NOT CustLedgEntry2.Open
THEN
IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN
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
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;
I am also including create table and insert data scripts in hopes to get some help to figure this out.
Thank you very much.
June 25, 2014 at 8:29 am
Great job posting ddl and sample data. Any chance you can trim the data to a few dozen rows instead of thousands? Also, what we need to understand is what is the output you are expecting? Once you trim down the sample data to a manageable size we would want to know what the output should be for the sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 11:09 am
Hi Sean, I trimmed the number of rows to 500 just to make sure there is enough data to work the 3 methods that calculate the DaysToPay value.
The output I need is a field that contains the value AvgDaysToPay, grouped by customer for records only for this year.
The formula is:
AvgDaysToPay = DaysToPay / NoOfInv
June 25, 2014 at 12:28 pm
itortu (6/25/2014)
Hi Sean, I trimmed the number of rows to 500 just to make sure there is enough data to work the 3 methods that calculate the DaysToPay value.The output I need is a field that contains the value AvgDaysToPay, grouped by customer for records only for this year.
The formula is:
AvgDaysToPay = DaysToPay / NoOfInv
I meant the actual values of what you expect as output. That way we can compare our results to what you say is correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 12:30 pm
And you might want to take a look at your sample data. It will not execute as posted. You are missing ' around all your dates.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 1:45 pm
I fixed the data.
June 25, 2014 at 1:57 pm
itortu (6/25/2014)
I fixed the data.
Now if you can post what you expect as output we can get started on working on a solution. I think we should reduce the amount of data significantly. Maybe just 3-4 customers. Enough to cover the logic.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 2:10 pm
Here is some output I verified for some customers.
Customer NumberAverage Days To Pay
1000234
100031
1000639
1001447
1001994
1002012
1002223
1002325
10024-2
1002654
1002717
100310
1003939
1004122
1004254
I hope this is good to get help for solution.
I truly appreciate your willingness to help.
June 25, 2014 at 2:19 pm
itortu (6/25/2014)
Here is some output I verified for some customers.
Customer NumberAverage Days To Pay1000234
100031
1000639
1001447
1001994
1002012
1002223
1002325
10024-2
1002654
1002717
100310
1003939
1004122
1004254
I hope this is good to get help for solution.
I truly appreciate your willingness to help.
Boy this is really a long haul to get information here. How did you come up with those numbers? You said previously:
The formula is:
AvgDaysToPay = DaysToPay / NoOfInv
What are DaysToPay and NoOfInv? I don't at all understand how for customer 10002 you can come up with 34. From your sample data the earliest PostingDate (I assume you want to use this column?) is 1/23/2014 and the most recent value is 2/20/2014. That is only 28 days. I did not look past that because this is so far off. Help me to help you by giving me the information required to help you find a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2014 at 2:56 pm
I attached one more time the insert data script.
I made sure I ran it in my development environment and for only a few customers:
('10002', '10003', '10006', '10014', '10019', '10020', '10022', '10023', '10024', '10026', '10027', '10031', '10039', '10041', '10042')
Then I got into the software in the development environment that uses the pascal code I posted in the first comment and the Number Average Days To Pay per Customer are:
Customer NumberDays To Pay
1000228 <<<<< You were right on the number of days to pay
1000311
1000639
1001447
100190
1002012
1002266
1002325
10024-2
100260
1002713
100310
1003934
1004117
1004255
The Number of Invoices is calculated in the code:
IF CustLedgEntry2.FIND('+') THEN
REPEAT
j := CustLedgEntry2."Document Type";
IF j > 0 THEN
NoOfDoc[j] := NoOfDoc[j] + 1;
CustLedgEntry2.CALCFIELDS("Amount (LCY)");
CustBalanceLCY := CustBalanceLCY - CustLedgEntry2."Amount (LCY)";
IF CustBalanceLCY > HighestBalanceLCY THEN
HighestBalanceLCY := CustBalanceLCY;
// Optimized Approximation
IF (CustLedgEntry2."Document Type" = CustLedgEntry2."Document Type"::Invoice) AND
NOT CustLedgEntry2.Open
THEN
IF CustLedgEntry2."Closed at Date" > CustLedgEntry2."Posting Date" THEN
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
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
AvgDaysToPay := DaysToPay / NoOfInv;
If you notice, it checks for a Document Type of Invoice, right after // Optimized Approximation, and also for the column Open to be 0
Then the 3 methods begin.
There is also a check when the second method check begins: IF CustLedgEntry2."Closed by Entry No." <>
I apologize for making this a long process. I do struggle with clarity when making posts. I am trying to the best of my abilities.
Thank you for your help.
June 26, 2014 at 7:21 am
You are going to have to provide some of the business rules. Simply pasting a portion of some code is not going to cut it. It appears from the code that there are some rules based on the order of the data. What is the order? There are lots of people around that can help but you have to put in some effort. We need to know what the rules are.
Why is CustomerNo 10003 11? Why isn't it 22?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply