Prev: SSIS Variables and variables. What is best practise.
Next: Nesting Insert/EXEC
From: Han on 23 Mar 2010 14:01
Hi,

PO table
PO Price
100 $2000
100-1 $150
100-2 $50

I want to write a query to sum Sub PO 100-1 and 100-2 (would be $150 + $50 =
$200) then substract the sum to the main PO 100 of $2000 ($2000 - $200 =
$1800)

The result should be
PO Price
100 $1800

I am thinking about sub queries but don't know the syntax. Could any one
help me?
Thanks a lot in advance.

Han.

Query #1 result
PO Price
100 $2000

Query #2 result result
PO Price
100-1 $150
100-2 $100

Can I write a query to get sum of all the Price in Query #2 ($250) then
subtract this return price with Query #1 result ($2000 - $250)

select
100-3 $250



--
Han
From: Plamen Ratchev on 23 Mar 2010 14:13
Assuming the main PO is always 3 characters, you can do this:

SELECT SUBSTRING(po, 1, 3) AS po,
SUM(
CASE WHEN LEN(po) > 3 THEN -1 * price
ELSE price
END) AS total_po
FROM POs
GROUP BY SUBSTRING(po, 1, 3);

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 23 Mar 2010 17:20
You are mixing payments in with Purchase Orders; they are not the same
thing. Please talk to an accountant. If you really want to pursue this
bad design, then do it in the DDL and not in the DML:

CREATE TABLE Purchase_Orders
(po_nbr INTEGER NOT NULL
CHECK (po_nbr > 0),
sub_po_nbr INTEGER DEFAULT 0 NOT NULL,
CHECK (sub_po_nbr >= 0),
PRIMARY KEY (po_nbr, sub_po_nbr),
purchase_or_payment_amt DECIMAL (8,2) NOT NULL
CHECK (purchase_or_payment_amt <> 0.00),
CHECK (CASE WHEN sub_po_nbr = 0
AND SIGN(purchase_or_payment_amt) = 1
THEN 'T'
WHEN sub_po_nbr > 0
AND SIGN(purchase_or_payment_amt)= -1
THEN 'T'
ELSE 'F' END = 'T')


Notice the horrible name "purchase_or_payment_amt" which shows it is
overloaded. The final CHECK() keeps the payments negative and the
original amount (sub_po_nbr = 0) positive.

CREATE VIEW Current_PO_Balances
AS
SELECT po_nbr, SUM(purchase_or_payment_amt) AS po_balance
FROM Purchases_Orders
GROUP BY po_nbr;

Think in terms of datga and not procedures.
 | 
Pages: 1
Prev: SSIS Variables and variables. What is best practise.
Next: Nesting Insert/EXEC