Subtracting Two Columns In SQL After Join

Question

I have one table called Orders

PurchaseID VARCHAR
purchaseDate DATETIME
purchasePrice FLOAT

I want to find the difference between the purchase price on one day versus another day - The puyrchaseID will be the same.(This is just an example table of course)

SELECT a.purchasePrice AS purchasePriceDay1 ,  b.purchasePrice AS  purchasePriceDay1
from Orders a , Orders b
where a.PurchaseID = b.PurchaseID

Would that actually work

Answer 1

SELECT 
    a.PurchaseID, 
    ABS(a.PurchaseID - b.PurchaseID) AS diff
FROM 
    PurchaseID a INNER JOIN PurchaseID b ON a.PurchaseID=b.PurchaseID
WHERE a.PurchaseID=? 
    AND a.purchaseDate=? 
    AND b.purchaseDate=?

Fill in the placeholders with the ID and dates to compare.

Consult your DBMS documentation to see if you have the math absolute function.

Answer 2

You'd probably also want to add AND a.purchaseDate <> b.purchaseDate

Answer 3

have you tried something like

SELECT a.purchasePrice AS p1,  b.purchasePrice AS  p2, a.purchasePrice - b.purchasePrice as difference
from Orders a inner join Orders b on a.PurchaseID = b.PurchaseID

Answer 4

SELECT ((SELECT purchasePrice FROM Orders WHERE PurchaseID = 1 AND purchaseDate = '2010-06-21') - (SELECT purchasePrice FROM Orders WHERE PurchaseID = 1 AND purchaseDate = '2010-06-20')) AS diff; 

Answer 5

You can perform the subtracting right in the query as well if you want to. You probably also want to make sure you don't get two purchases from the same day:

SELECT a.purchasePrice AS purchasePriceDay1 ,  b.purchasePrice AS  purchasePriceDay2, (purchasePriceDay2-purchasePriceDay1) AS purchasePriceDelta
from Orders a , Orders b
where a.PurchaseID = b.PurchaseID
and a.PurchaseDate < b.PurchaseDate