By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

query to find a daily value difference

P: 1
Hi,
I m a new SQL leaner, and got my self into a difficult question,
let's say i have this table:
Expand|Select|Wrap|Line Numbers
  1. create table purchase(
  2.     datePurchase date  
  3.     itemiId int,
  4.     nbItemSold int
  5.     cstmrId int
  6. );
How can I get the daily difference of the product sold ?

Ex:
Expand|Select|Wrap|Line Numbers
  1. insert into purchase values
  2. ('2017-02-01' ,4, 'product1'),
  3. ('2017-02-01', 3, 'product2'),
  4. ('2017-02-01', 1, ' product3'),
  5. ('2017-02-02', 1, 'product1'),
  6. ('2017-02-02', 2, 'product2'),
  7. ('2017-02-02', 1, 'product3'),
  8. ('2017-02-03', 5, 'product2'), 
  9. ('2017-02-03', 1, 'product3'),
  10. ('2017-02-03', 0, 'product1');
  11.  
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
Mar 18 '17 #1
Share this Question
Share on Google+
1 Reply


jforbes
Expert 100+
P: 1,107
You could do something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.   Today.ItemID
  3. , Today.dtePurchase
  4. , Today.nbItemSold
  5. , PrevDay.nbItemSold
  6. , Nz(Today.nbItemSold, 0)-Nz(PrevDay.nbItemSold, 0) AS DailyDifference
  7. FROM ItemSalesHistory AS Today
  8. LEFT JOIN ItemSalesHistory AS PrevDay
  9. ON Today.dtePurchase=PrevDay.dtePurchase+1
  10. AND Today.ItemID=PrevDay.ItemID
  11. ORDER BY Today.dtePurchase, Today.ItemID
Mar 20 '17 #2

Post your reply

Sign in to post your reply or Sign up for a free account.