query to find a daily value difference

[bozmin]

Hi,
I m a new SQL leaner, and got my self into a difficult question,
let's say i have this table:

create table purchase(
    datePurchase date  
    itemiId int,
    nbItemSold int
    cstmrId int
);

How can I get the daily difference of the product sold ?

Ex:

insert into purchase values
('2017-02-01' ,4, 'product1'),
('2017-02-01', 3, 'product2'),
('2017-02-01', 1, ' product3'),
('2017-02-02', 1, 'product1'),
('2017-02-02', 2, 'product2'),
('2017-02-02', 1, 'product3'),
('2017-02-03', 5, 'product2'), 
('2017-02-03', 1, 'product3'),
('2017-02-03', 0, 'product1');
 

Query result would be (getting the difference between the number of the product sold on the D day with those sold on the D-1 day):
the final table resulting of these difference should contain this datas

2017-02-02, product1, -3
2017-02-02, product2, -1
2017-02-02, product3, 1
2017-02-03, product1, -1
2017-02-03, product2, 3
2017-02-03, product3, 0

the customer doesn't matter. i just want to find the daily difference in selling products

[jforbes]

You could do something like this:

SELECT 
  Today.ItemID
, Today.dtePurchase
, Today.nbItemSold
, PrevDay.nbItemSold
, Nz(Today.nbItemSold, 0)-Nz(PrevDay.nbItemSold, 0) AS DailyDifference
FROM ItemSalesHistory AS Today
LEFT JOIN ItemSalesHistory AS PrevDay
ON Today.dtePurchase=PrevDay.dtePurchase+1
AND Today.ItemID=PrevDay.ItemID
ORDER BY Today.dtePurchase, Today.ItemID