Advertisement
Guest User

Untitled

a guest
Mar 25th, 2017
65
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.82 KB | None | 0 0
  1. stock
  2. stock_id
  3. p_id
  4. stock_qty
  5.  
  6. product
  7. p_id
  8. p_name
  9.  
  10. delivery
  11. d_id
  12. d_datetime
  13.  
  14. withdrawal
  15. w_id
  16. w_datetime
  17.  
  18. wd
  19. p_id
  20. wd_qty
  21.  
  22. dd
  23. p_id
  24. dd_qty
  25.  
  26. SELECT product.p_id AS 'ID',
  27. product.p_name AS 'Item Name',
  28. stock.stock_datetime AS 'Last Stock Update',
  29. IFNULL(stock.stock_qty,0) AS 'Beginning Count',
  30. IFNULL(SUM(dd.dd_qty),0) AS 'Total Delivery',
  31. IFNULL(SUM(wd.wd_qty),0) as 'Total Withdrawals',
  32. IFNULL(stock.stock_qty+SUM(dd.dd_qty)-SUM(wd.wd_qty),0) AS 'Remaining'
  33. FROM product
  34. LEFT JOIN stock ON
  35. stock.p_id=product.p_id
  36. LEFT JOIN wd ON
  37. product.p_id =wd.p_id
  38. LEFT JOIN dd ON
  39. product.p_id =dd.p_id
  40. INNER JOIN
  41. delivery d ON d.d_id =dd.d_id
  42. INNER JOIN
  43. withdrawal w ON w.w_id=wd.w_id
  44. WHERE w.w_datetime AND d.d_datetime BETWEEN '2016-01-01 00:00:00' AND Now()
  45. GROUP BY product.p_id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement