Need to signal 'this price' covered by 'this pay'

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need to signal 'this price' covered by 'this pay'

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-27 : 16:37:08

Hi,
need to show that Salesorderitems 4 and 5
are covered by the payment of -22995.0500.
It kind of already does because of the invoice number and I might be
able to relate that AccounttransValue to is own unique key but
I am also presented with the case where AccounttransValue may not cover Salesorderitems 4 and 5 completely but may be a percentage.

IE AccounttransValue = -200000.0000 for both items 4 and 5
This comes from a case where partially invoicing is not maintaining it's relationship to the original Invoice line items which is generated from a pick list of Salesorderitems.
I'm lost as to how to work on this other than attack it with this simplified example. So I need to somehow show that there would be a balance remaining somewhere spreadout on items 4 an 5 dosen't really matter where at this point because they were invoiced together and the payment comes in against both of them. The point is to eventually show what is left to be paid per item. So I could display a percentage
on each, OR item 4 paid in full and some remaining on 5. I'm pretty shaky on what I'm trying to accomplish here.

SET NOCOUNT ON

CREATE TABLE #temp
(
Salesorder char(6),
Salesorderitem char(3),
Partname varchar(20),
Partrev char(3),
INvoiceno char(10),
InvoicePrice money,
RelesedPrice money,
Invtodate money,
AccounttransValue money
)

INSERT INTO #temp Values ('22XC','1','Base','NS','17492',75920.17,75920.17,75920.17,-75920.17)
INSERT INTO #temp Values ('22XC','2','Custom','NS','17493',12994.03,12994.03,12994.03,-12994.03)
INSERT INTO #temp Values ('22XC','3','Special Colour','NS','17494',7811.02,7811.02,7811.02,-7811.02)
INSERT INTO #temp Values ('22XC','4','Pinstipping','NS','17495',16425.04,16425.04,16425.04,-22995.05)
INSERT INTO #temp Values ('22XC','5','Chrome','NS','17495',6570.01,6570.01,6570.01,-22995.05)
INSERT INTO #temp Values ('22XC','6','glosslevelchange','NS','17562',1168,1168,1168,-1168)

select * from #temp
DROP TABLE #temp

Voted best SQL forum nickname...."Tutorial-D"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 21:50:38

Sitka -- I think I'm with you one this one. You have Orders broken down into a further level than payments, and when you want to break each payment down to the Order item level. But sometimes payments don't cover the entire order amount, so it is tricky.

Assume you have a table of Orders:

*Order
*Item
OrderPrice

(*primary key)

and a table of Payments:

*PaymentID
Order
Amount
..etc..

How about something like this:



SELECT 
   A.Order, A.Item, A.OrderPrice,
   B.PaidPct * A.OrderPrice as PaidAmount
FROM
   Orders A
INNER JOIN 
  (SELECT 
        I.Order, ISNULL(P.TotalPayments,0)/I.OrderTotal as PaidPct
   FROM
       (SELECT 
           Order, SUM(OrderPrice) as OrderTotal 
        FROM 
           Orders 
        GROUP BY 
           Order) I
   LEFT OUTER JOIN
       (SELECT 
           Order, SUM(Amount) as TotalPayments 
        FROM 
           Payments 
        GROUP BY 
           Order) P
   ON 
        I.Order = P.Order) B 
ON A.Order = B.Order

We get the percent paid for each order, and then take that pct and
multiple it by the item-level numbers for each order. this spreads
the payments to the order details.

Is that what you are looking for, or something like it?

I can explain the above further if it helps.

- Jeff

Edited by - jsmith8858 on 11/27/2002 21:53:10

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-28 : 06:52:58

Thanks, If I don't get this Christmas is not going to be merry. Can't wait to try your model!!!!!

I'll post back.

Voted best SQL forum nickname...."Tutorial-D"

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-28 : 09:28:00

SET NOCOUNT ON
CREATE TABLE #A
(
SORsono char(6),
SORinum smallint,
SORprice money,
ARIprice money,
ARIinvoice char(12),
fnamount money,
fcacctnum char(12)
)

INSERT INTO #A VALUES ('xx281',1,43950.00000,43950.00000,'0000017384',-46150.0000,'xxx00100')
INSERT INTO #A VALUES ('xx281',2,2200.00000,2200.00000,'0000017384',-46150.0000,'xxx00100')
INSERT INTO #A VALUES ('xx281',3,6900.00000,6900.00000,'0000017660',-6900.0000,'xxx00100')

SELECT * FROM #A

DROP TABLE #A

/*
this is a cleaned up version
SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price money,
invoice char(12),
invamount money,
)

INSERT INTO #A VALUES (1,43950.00000,'0000017384',-46150.0000)
INSERT INTO #A VALUES (2,2200.00000,'0000017384',-46150.0000)
INSERT INTO #A VALUES (3,6900.00000,'0000017660',-6900.0000)

SELECT item, price, invoice, invamount FROM #A

DROP TABLE #A
*/

****RAW table************

SORsono SORinum SORprice ARIprice ARIinvoice fnamount fcacctnum
------- ----------- ------------------- ------------------- -------------------- --------------------- -------------------------
xx281 1 43950.00000 43950.00000 0000017384 -46150.0000 xxx00100
xx281 2 2200.00000 2200.00000 0000017384 -46150.0000 xxx00100
xx281 3 6900.00000 6900.00000 0000017660 -6900.0000 xxx00100

****A set of desired results when the Received payment changes******
****change the RAW table to include the YETTOPAY column and the calculation*******

SORsono SORinum SORprice ARIprice ARIinvoice fnamount fcacctnum YETTOPAY
------- ----------- ------------------- ------------------- -------------------- --------------------- ------------------------- ----------------------
xx281 1 43950.00000 43950.00000 0000017384 -46150.0000 xxx00100 0
xx281 2 2200.00000 2200.00000 0000017384 -46150.0000 xxx00100 0
xx281 3 6900.00000 6900.00000 0000017660 -6900.0000 xxx00100 0

SORsono SORinum SORprice ARIprice ARIinvoice fnamount fcacctnum YETTOPAY
------- ----------- ------------------- ------------------- -------------------- --------------------- ------------------------- ----------------------
xx281 1 43950.00000 43950.00000 0000017384 -43940.0000 xxx00100 10
xx281 2 2200.00000 2200.00000 0000017384 -43940.0000 xxx00100 2200
xx281 3 6900.00000 6900.00000 0000017660 -6900.0000 xxx00100 0

SORsono SORinum SORprice ARIprice ARIinvoice fnamount fcacctnum YETTOPAY
------- ----------- ------------------- ------------------- -------------------- --------------------- ------------------------- ----------------------
xx281 1 43950.00000 43950.00000 0000017384 -44950.0000 xxx00100 0
xx281 2 2200.00000 2200.00000 0000017384 -44950.0000 xxx00100 2100
xx281 3 6900.00000 6900.00000 0000017660 -6900.0000 xxx00100 0

The raw table is built by the only method I can follow at this point.
So now I have this raw table that is easy enough to interpret by looking at it, it is easy to see that the -46150.0000 value covers the 43950.00000 and the 2200.00000 but now I need to do work on that raw table to produce the YETTOPAY column. I'm pretty sure this is the desired result, any prdictions on when I get to slap my head. It's been three weeks.

Edited by - sitka on 11/28/2002 10:22:34

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-28 : 10:48:58

jsmith8858...

I gave your solution a shot but haven't made it work yet,
changed Order to Orderx and # for temp tables

HOLD ON.....

WOOW..

Payments table has double entry....

I added the DDL, cut and paste

/*
Assume you have a table of Orders:
*Orderx
*Item
OrderPrice
(*primary key)
*/

CREATE TABLE #Orders
(
Orderx char(6),
item smallint,
OrderPrice money
)
INSERT INTO #Orders VALUES('xx281',1,43950.00000)
INSERT INTO #Orders VALUES('xx281',2,2200.00000)
INSERT INTO #Orders VALUES('xx281',3,6900.00000)

--SELECT * FROM #Orders
--DROP TABLE #Orders

/*
and a table of Payments:
*PaymentID
Orderx
Amount
..etc..
*/

CREATE TABLE #Payments
(
PaymentID char(12),
Orderx char(6),
Amount money
)

INSERT INTO #Payments VALUES('0000017384','xx281',46150.0000)
---INSERT INTO #Payments VALUES('0000017384','xx281',46150.0000)
INSERT INTO #Payments VALUES('0000017660','xx281',6900.0000)

--SELECT * FROM #Payments
--DROP TABLE #Payments

/*
How about something like this:
*/

SELECT
A.Orderx, A.Item, A.OrderPrice,
B.PaidPct * A.OrderPrice as PaidAmount
FROM
#Orders A
INNER JOIN
(SELECT
I.Orderx, ISNULL(P.TotalPayments,0)/I.OrderTotal as PaidPct
FROM
(SELECT
Orderx, SUM(OrderPrice) as OrderTotal
FROM
#Orders
GROUP BY
Orderx) I
LEFT OUTER JOIN
(SELECT
Orderx, SUM(Amount) as TotalPayments
FROM
#Payments
GROUP BY
Orderx) P
ON
I.Orderx = P.Orderx) B
ON A.Orderx = B.Orderx

DROP TABLE #Orders
DROP TABLE #Payments

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 11/28/2002 10:52:57

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-28 : 11:07:23

The jsmith8858 method works but my issue is that the Payment
isn't related directly to an Order(x) but an invoice, and my stomach is so knotted from this I just barfed.

SO in the example presented if I move the payment value 46150.0000 downwards it gets distributed to Item #3 which it can't do because that payment path is covered by a different invoice.

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 11/28/2002 12:01:39

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-29 : 10:21:18

I hope I've upped the stakes,
actually got to talk to my
programmer sister last night about this
and of course she quickly explained
the iterative method.

I think my version of it sucks.
But here it is. In one of the above posts I simplified the model of what needed to be done with this DDL.

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price money,
invoice char(12),
invamount money,
)
INSERT INTO #A VALUES (1,43950.00000,'0000017384',-46150.0000)
INSERT INTO #A VALUES (2,2200.00000,'0000017384',-46150.0000)
INSERT INTO #A VALUES (3,6900.00000,'0000017660',-6900.0000)
SELECT item, price, invoice, invamount FROM #A
DROP TABLE #A
*/

AND THE SOLUTION DERIVED FROM THIS MODEL

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price money,
invoice char(12),
invamount money,
)

INSERT INTO #A VALUES (1,43950.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (2,2200.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (3,1000.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (4,6900.00000,'0000017660',6900.0000)

CREATE TABLE #B
(
Bitem smallint,
Bprice money,
Binvoice char(12),
Binvamount money,
Byettopay money
)

DECLARE model CURSOR
FOR SELECT item, price, invoice, invamount FROM #A
FOR READ ONLY

DECLARE @item smallint, @price money, @invoice char(12), @invamount money
DECLARE @yettopay money

DECLARE @aprev char(12)
DECLARE @d money

SET @aprev = 0

OPEN model

FETCH NEXT FROM model INTO @item, @price, @invoice, @invamount

WHILE (@@FETCH_STATUS=0)BEGIN
IF @invoice <> @aprev
BEGIN
SET @d=@invamount
IF @invamount >= @price BEGIN SET @yettopay = 0 END
IF @invamount < @price BEGIN SET @yettopay = @price-@d END
IF @d-@price <= 0 BEGIN SET @d = 0 END
IF @d-@price > 0 BEGIN SET @d = @d-@price END
END

IF @invoice = @aprev
BEGIN
IF @price <= @d BEGIN SET @yettopay = 0 END
IF @price > @d BEGIN SET @yettopay = @price-@d END
IF @d-@price <= 0 BEGIN SET @d = 0 END
IF @d-@price > 0 BEGIN SET @d = @d-@price END
END

INSERT INTO #B VALUES (@item, @price, @invoice, @invamount, @yettopay)
SET @aprev = @invoice
FETCH NEXT FROM model INTO @item, @price, @invoice, @invamount

END

CLOSE model
DEALLOCATE model

SELECT * FROM #B

DROP TABLE #A
DROP TABLE #B

...please snipe away

Voted best SQL forum nickname...."Tutorial-D"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-03 : 18:40:47

NR would be pleased.

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price money,
invoice char(12),
invamount money,
)

INSERT INTO #A VALUES (1,43950.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (2,2200.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (3,1000.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (4,6900.00000,'0000017660',6900.0000)

SELECT A.item, A.invoice, A.invamount, A.price, A.sumprice,
CASE
WHEN A.invamount >= A.sumprice THEN 0.00
ELSE A.sumprice - A.invamount
END As YetToPay
FROM
(
SELECT a.item,a.invoice,a.price, a.invamount, SUM(b.price) sumprice
FROM #A a
CROSS JOIN #A b
WHERE (b.invoice = a.invoice and b.item < = a.item)
GROUP BY a.item,a.invoice,a.invamount,a.price
) A
ORDER BY A.item,A.invoice,A.invamount,A.price

DROP TABLE #A

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-04 : 07:49:10

Thanks tons.
Woo hoo what a treat, I'm gonna really try to see this properly.
I new it had to do with a join to itself, I just don't see them that well.
But upon first look I'm getting an arithmatic overflow error
I can correct it but also want to know why it is happening.

To summarize

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price money,
invoice char(12),
invamount money,
)
Server: Msg 8115, Level 16, State 8, Line 15
Arithmetic overflow error converting numeric to data type numeric.

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price real,
invoice char(12),
invamount money,
)
no error

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price money,
invoice char(12),
invamount real,
)
no error

Damn I'm excited

It's almost like b.price loses it's data type
think the best form is

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price numeric(16,4),
invoice char(12),
invamount numeric(16,4),
)

for now...

Edited by - sitka on 12/04/2002 08:14:58

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-04 : 09:13:25

OK,

this makes sense kind of

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
price money,
invoice char(12),
invamount money,
)

INSERT INTO #A VALUES (1,43950.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (2,2200.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (3,1000.00000,'0000017384',46150.0000)
INSERT INTO #A VALUES (4,6900.00000,'0000017660',6900.0000)

SELECT A.item, A.invoice, A.invamount, A.price, A.sumprice,
CASE
WHEN A.invamount >= A.sumprice THEN $0.00
ELSE A.sumprice - A.invamount
END As YetToPay
FROM
(
SELECT a.item,a.invoice,a.price, a.invamount, SUM(b.price) sumprice
FROM #A a
CROSS JOIN #A b
WHERE (b.invoice = a.invoice and b.item < = a.item)
GROUP BY a.item,a.invoice,a.invamount,a.price
) A
ORDER BY A.item,A.invoice,A.invamount,A.price

DROP TABLE #A

To me it looks like the allocated "slot" for the result of the Case statement on the first row sets it at 0.00 which is not money
and this functions below the level of implicit conversions. Kind of like any of the other implicit declarations around (variant). This sets the result slot to a certain type which seems to be numeric and then we ask it to be money when the other condition is met, ie. other than 0.00. SO if we set it to money to begin with all is fine.
This is kind of a big deal to to me cause have wrestled with this before, the error dosen't help in this case because when is numeric NOT numeric?? When you wnat to put money into a slot that is expecting numeric and is not handled by implicit conversion. Case may be an example of this. I ain't positive, but you would think that 0.0000 would work ie...to ten-thousandth of a monetary unit that makes it pretty close to money but not quite and therefore fails as well.

Tell you what is money is ValterBorges and jsmith8858 solutions
thanks.

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 12/04/2002 09:17:22

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-04 : 09:16:51

Take the $ out of the numeric value. It will probably fail an implicit conversion to numeric or money.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-04 : 09:19:10

I'm writing as your posting.

So to summarize. I believe in this case $0.00
is correct.

Agreement..?

Voted best SQL forum nickname...."Tutorial-D"

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-05 : 15:43:32

oops.....almost broke it.
the addition takes care of real small invoice values too.
note* reordered the temp table to match the picks.
Makes the model a little more obvious

SET NOCOUNT ON
CREATE TABLE #A
(
item smallint,
invoice char(12),
invamount money,
price money
)

INSERT INTO #A VALUES (1,'0000017384',6150.0000,43950.00000)
INSERT INTO #A VALUES (2,'0000017384',6150.0000,2200.00000)
INSERT INTO #A VALUES (3,'0000017384',6150.0000,1000.00000)
INSERT INTO #A VALUES (4,'0000017660',6900.00000,6900.0000)

SELECT A.item, A.invoice, A.invamount, A.price, A.sumprice,

CASE
WHEN A.invamount >= A.sumprice THEN $0.00
ELSE CASE WHEN A.sumprice - A.invamount > A.price THEN A.price ELSE A.sumprice - A.invamount END
END As YetToPay

FROM
(
SELECT a.item,a.invoice,a.price, a.invamount, SUM(b.price) sumprice
FROM #A a
CROSS JOIN #A b
WHERE (b.invoice = a.invoice and b.item < = a.item)
GROUP BY a.item,a.invoice,a.invamount,a.price
) A
ORDER BY A.item,A.invoice,A.invamount,A.price

DROP TABLE #A

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources