January 14, 2008 at 5:13 pm
Hello,
I am looking for some guidance with a fairly involved SQL query. The situation is basically as follows;
I am writing an application that involves an Expense Journal and Purchase Order modules. The entries in the expense journal are periodically posted against the purchase order table. Before running the expense journal post routine I run a number of validation queries to weed out any faults in the expense journal entries.
Specifically, I have one validation step that attempts to determine if the current expense journal entries reflect an overpayment of a purchase order.
The expense journal table is: EJ
It includes columns: PONumber, Amount
The purchase order header table is: PO_MX
It includes columns: PONumber, PaymentPosted
The purchase order detail table is: PO_DX
It includes columns: QuantityOrdered, Price
When an expense journal item posts against a purchase order, column EJ.Amount is added to the PO_MX.PaymentPosted column. In any given expense journal posting session there may be one or multiple EJ rows to post against the same purchase order. (After all EJ entries post, the EJ table is archived and emptied)
So, at present I am trying to come up with a single SQL query that will return to me the PO Numbers from any expense journal entries whose posting would result in the PO_MX.PaymentPosted column having a value greater than the sum of (QuantityOrdered * Price) for all its associated PO_DX rows. The query needs to include the fact that the PO.PaymentPosted column may already have a non-zero value as a result of previous posting sessions. So, we basically want to test that we don't have the following yielding a True condition...
Sum(EJ.Amount) > (PO_DX.QuantityOrdered * PO_DX.Price) - PO_MX.PaymentPosted
The following query would work fine if we could guarantee that any given batch of EJ posting entries did not have more than one row to be posted against a given PO. The step that I cannot figure out is how to add the piece to the below query that will sum up the EJ.amount field for multiple rows having the same PO number and then testing that value against the PO table.
Select PONumber
From EJ_X
Where Amount >
((Select sum(QuantityOrdered * Price)
From PO_DX
Where PONumber = EJ_X.PONumber) -
(Select PaymentPosted
From PO_MX
Where PONumber = EJ_X.PONumber))
I would greatly appreciate any help you might offer,
thanks
January 14, 2008 at 8:01 pm
I'd start out by looking up the JOIN, GROUP BY and HAVING clauses in Books Online.
January 15, 2008 at 3:21 am
In addition, derived tables, also known as inline views, could be used instead of HAVING:
SELECT PO_MX.PONumber
FROM PO_MX
    JOIN
    (
        SELECT EJ.PONumber, SUM(EJ.Amount) AS Amount
        FROM EJ
        GROUP BY EJ.PONumber
    ) D1
    ON PO_MX.PONumber = D1.PONumber
    JOIN
    (
        SELECT PO_DX.PONumber, SUM(PO_DX.QuantityOrdered * PO_DX.Price) AS Amount
        FROM PO_DX
        GROUP BY PO_DX.PONumber
    ) D2
    ON PO_MX.PONumber = D2.PONumber
WHERE D2.Amount < D1.Amount + PO_MX.Paymentposted
Try running both queries together and then look at the query plans
January 15, 2008 at 9:41 am
Thank you very much for your responses,
Ken, I tried your solution against my tables and it worked perfectly - the first time. I had never used derived tables, but after studying your example and doing a little on-line reading I think they might prove very useful in the future.
I noticed you oriented your query to focus on the Purchase Order tables as opposed to the Expense Journal. Out of curiosity (and just because my head seems to want to approach this from the EJ perspective) I tried modifying your query to perform the Select against the Expense Journal and came up with the following;
SELECT Distinct(EJ.PONumber)
FROM EJ
JOIN
(
SELECT EJ.PONumber, SUM(EJ.Amount) AS Amount
FROM EJ
GROUP BY EJ.PONumber
) As D1
ON EJ.PONumber = D1.PONumber
JOIN
(
SELECT PO_DX.PONumber, SUM(PO_DX.QuantityOrdered * PO_DX.Price) AS Amount
FROM PO_DX
GROUP BY PO_DX.PONumber
) As D2
ON EJ.PONumber = D2.PONumber
JOIN
(
SELECT PO_MX.PONumber, PO_MX.PaymentPosted As PaymentPosted
FROM PO_MX
) As D3
ON EJ.PONumber = D3.PONumber
WHERE D2.Amount < D1.Amount + D3.Paymentposted
It's not as tidy as the other way around, though a brief bit of testing seemed to provide correct output. Ken, thanks again for your response - I sure do appreciate your taking the time to write up a query in response to my question.
One final dumb question - I copy/pasted the above directly from SQL Server Management Studio expecting the key words to copy through color coded to the post. Is there a way an easy way to make that happen?
Thanks again,
Dale
January 16, 2008 at 4:07 am
I started the query with the PO_MX table as you were looking for PONumbers - I find this easier but you can start with any table you like.
You should also understand Jeremy's solution:
SELECT PO.PONumber, PO.Paymentposted
    ,SUM(EJ.Amount) AS EJAmount
    ,SUM(DX.QuantityOrdered * DX.Price) AS DXAmount
FROM PO_MX PO
    JOIN EJ
        ON PO.PONumber = EJ.PONumber
    JOIN PO_DX DX
        ON PO.PONumber = DX.PONumber
GROUP BY PO.PONumber, PO.Paymentposted
HAVING SUM(DX.QuantityOrdered * DX.Price) < SUM(EJ.Amount) + PO.Paymentposted
It is often useful to write two queries and then compare the query plans. The plans are often the same but sometimes one is more efficient.
As to the colors - I just run the queries through the attached replace script in my text editor, NoteTab Pro.
January 16, 2008 at 4:40 pm
Ken,
Thanks for the optional solution. I spent some time studying it and like it in that it seems conceptually fairly straightforward. I have not previously used the HAVING clause but I understand that it basically filters the result of what comes before. I tested this solution against the same data as the first solution and unfortunately this one will not work in its present form. I stripped it down to the following to get a little clearer look at what it was doing;
SELECT PO.PONumber, PO.Paymentposted,
EJ.Amount AS EJAmount,
DX.QuantityOrdered * DX.Price AS DXAmount
FROM PO_MX PO
JOIN EJ
ON PO.PONumber = EJ.PONumber
JOIN PO_DX DX
ON PO.PONumber = DX.PONumber
When you look at the resultant four columns, basically the query is returning all possible combinations of PONumber, PaymentPosted, EJAmount, and QuantityOrdered * Price.
For example, regarding PONumber 20081227 we have;
Three EJ entries; $80, $20, $20
The PO has three line items: Qty 1 @ $103.40, Qty 2 @ $3.60, Qty 2 @ $4.40
The PO has a previous PaymentPosted amount of $10
(Bear with me, these are test numbers that don't necessarily make sense)
Anyhow, executing the above query returns nine rows as follows;
PONumber PaymentPosted EJAmount DXAmount
============================================
20081227 10.00 80.00 103.40
20081227 10.00 80.00 7.20
20081227 10.00 80.00 8.80
20081227 10.00 20.00 103.40
20081227 10.00 20.00 7.20
20081227 10.00 20.00 8.80
20081227 10.00 20.00 103.40
20081227 10.00 20.00 7.20
20081227 10.00 20.00 8.80
If we run the query in its original form, less the HAVING clause, we get the following row returned for PO 20081227;
PONumber PaymentPosted EJAmount DXAmount
============================================
20081227 10.00 360.00 358.20
The EJAmount column should of course be $120 and the DXAmount column $119.40
My thought is that a variation on the current JOIN might be the solution. I may have time this evening to try that out. In any case, I would be very curious how to make the original query work as I like the overall solution.
Thank you for your time
January 17, 2008 at 3:38 am
Dale,
My apologies – I did not visualize the joins correctly. (More coffee required!)
I do not think HAVING can be used easily in this case.
Ken
January 17, 2008 at 7:04 am
Ken,
No problem. I actually learned more wrestling with that last query trying to make it work than I would have if it worked out of the box. Anyhow, thanks greatly for the help - this gets me over the hurdle and gives me a couple new techniques to use in the future.
Dale
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply