Solved

LINQ Statement with a sum option in it....

Posted on 2009-05-15
8
1,117 Views
Last Modified: 2013-11-26
Hi Experts,

I have just asked a previous question that was answered well however I realised I need to modifiy the question to this...

The background is a LINQ statement that collects all invoices that are "open". That is, have not yet been paid off. In some cases these invoices might have been part paid.

Any payment records have "PMT" as the tran_type. So the simple solution is to, for each invoice number, sum all records that have "PMT" as the "tran_type" and the "link_posting_id" equal to the orginal Invoice "posting_id".

The thing is, I have included a couple of nested LINQ queries within the main query to determine what the balance left is if there is one.... this should be what the user/account holder has left to pay on the invoice no matter how many line items there are...

I have used the nested query:

((From ledger1 In db.ledgers _
                                         Where (ledger1.client_no = CType(clientNo, Integer)) And _
                                         (ledger1.acc_id = (From control_acc In db.control_accounts _
                                                              Where control_acc.control_desc = "CREDITORS" _
                                                              Select control_acc.acc_id).Single)) - _
                                            ((From ledger2 In db.ledgers _
                                        Where (ledger2.link_posting_id = ledger.posting_id) And _
                                        (ledger2.tran_type = "PMT") _
                                        Select ledger2.amount).Sum())) <> 0

To determine whether the invoice and all associated payments still dont add up to "0".

I guess I used:

Where (ledger1.client_no = CType(clientNo, Integer)) And _
                                         (ledger1.acc_id = (From control_acc In db.control_accounts _
                                                              Where control_acc.control_desc = "CREDITORS" _
                                                              Select control_acc.acc_id).Single))

to find the invoice total as this will always be where the account ID will be equal to the control account ID.

If you then subtract the total of all payments and this is less then this invoice has not been fully paid off... it should be selected..... thats why the total of the invoice and all the payments should <> 0...

It follows that the balance should be this residual amount...

The problem is that there is a syntax error with the negative or minus (and probably a few other places) as this is the most complicated LINQ statement yet I have attempted......

I have included the test database which has an appropriate example invoice and two payments for client 1007. refer to the table ledger to see the original invoice and two payments.

Incidentally, I have included a zip file of the database but with no extention ".bak" because it wont take the file with the .bak extension... (its time they had a look at this... :)  )

Hope you can shed some light on this...

Kind Regards

Simon
Dim selectedInvoices = From ledger In db.ledgers _

                       Order By ledger.tran_date Ascending _

                       Where ledger.client_no = CType(clientNo, Integer) And _

                       ((From ledger1 In db.ledgers _

                                         Where (ledger1.client_no = CType(clientNo, Integer)) And _

                                         (ledger1.acc_id = (From control_acc In db.control_accounts _

                                                              Where control_acc.control_desc = "CREDITORS" _

                                                              Select control_acc.acc_id).Single)) - _

                                            ((From ledger2 In db.ledgers _

                                        Where (ledger2.link_posting_id = ledger.posting_id) And _

                                        (ledger2.tran_type = "PMT") _

                                        Select ledger2.amount).Sum())) <> 0

                       Select ledger.tran_date, _

                              name = If(ledger.client.first_name <> "", ledger.client.last_name & ", " & ledger.client.first_name, ledger.client.last_name), _

                              ledger.ref1, _

                              ledger.detail, _

                              invoiceTotal = ledger.amount, _

                              balance = ((From ledger1 In db.ledgers _

                                         Where (ledger1.client_no = CType(clientNo, Integer)) And _

                                         (ledger1.acc_id = (From control_acc In db.control_accounts _

                                                              Where control_acc.control_desc = "CREDITORS" _

                                                              Select control_acc.acc_id).Single)) - _

                                            ((From ledger2 In db.ledgers _

                                        Where (ledger2.link_posting_id = ledger.posting_id) And _

                                        (ledger2.tran_type = "PMT") _

                                        Select ledger2.amount).Sum()))

Open in new window

PPL--3-.zip
Ledger-Detail.jpg
Client-Detail.jpg
0
Comment
Question by:si2030
  • 4
  • 4
8 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24402562
Hi Simon;

I am having trouble following the query that you are trying to execute. Can you post a spec on on the steps that you need to do on the tables to achieve the needed results, a step by step in the order needed in this way I can try to come up with a query that gives you what you want. For example in your query you order first which is very inefficient, lets say you have 100,000,000 records in the Ledger table you first sort then remove the records that are not needed where you should remove the records that are not needed first and then order the much smaller subset. Also you have relationships in the database and they should be used over using another query.

Fernando
0
 

Author Comment

by:si2030
ID: 24402974
Hi Fernando,

So am I!  I am working on a better way of describing this and will get back once I have a better idea of the outcome I am trying to achieve. I will post the result in a day or so... and see if this is actually a better way to do it..

Simon
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24403569
OK Simon, will talk to you then.
0
 

Author Comment

by:si2030
ID: 24405759
HI Fernando,

Think I might have worked this out.

Instead of testing all invoices that have ever been created I have added a column that flags the invoice as paid. I now have to test only for invoices that do not have this set to true.

Second, for those invoices that do have an amount part paid by 1 or more payments I have used a nested LINQ statement for the balance. Since this is for creditor invoices I have multiplied the "sum" by -1 to change the sign.

Seems to work OK. I dont anticipate having alot of invoices that are open at any one time...
Dim openInvoices = From ledger In db.ledgers _

                                   Order By ledger.tran_date Ascending _

                                   Where (ledger.client_no = CType(clientNo, Integer)) And _

                                   (ledger.tran_type = "CI" And ledger.acc_id = "2") And _

                                   (ledger.payment_complete = False) _

                                   Select ledger.posting_id, _

                                   ledger.tran_date, _

                                   ledger.ref1, _

                                   ledger.client_no, _

                                   amount = -1 * ledger.amount, _

                                   balance = -1 * ((From ledgerBal In db.ledgers _

                                                    Where (ledgerBal.posting_id = ledger.posting_id And ledgerBal.acc_id = "2") Or _

                                                    (ledgerBal.link_posting_id = ledger.posting_id And ledgerBal.acc_id = "2") Select ledgerBal.amount).Sum())

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 24406085
Hi Simon;

If that works for what you need that is fine. I have one suggestion and that is to always filter before doing any sorting. It may not be necessary in this case but the next query may need it and it is a good habit to get into.

Fernando
Dim openInvoices = From ledger In db.ledgers _

                   Where (ledger.client_no = CType(clientNo, Integer)) And _

                         (ledger.tran_type = "CI" And ledger.acc_id = "2") And _

                         (ledger.payment_complete = False) _

                   Order By ledger.tran_date Ascending _

                   Select ledger.posting_id, _

                          ledger.tran_date, _

                          ledger.ref1, _

                          ledger.client_no, _

                          amount = -1 * ledger.amount, _

                          balance = -1 * ((From ledgerBal In db.ledgers _

                                           Where (ledgerBal.posting_id = ledger.posting_id And ledgerBal.acc_id = "2") Or _

                                                 (ledgerBal.link_posting_id = ledger.posting_id And ledgerBal.acc_id = "2") _

                                           Select ledgerBal.amount).Sum())

Open in new window

0
 

Author Comment

by:si2030
ID: 24406100
Hi Fernando,

I didnt realise that. I will do that from now on. Appreciate your time here.

Simon
0
 

Author Closing Comment

by:si2030
ID: 31581982
Filtering before sorting will help with the bigger queries...
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24406114
Not a problem, always glad to help.  ;=)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now