tailieunhanh - Joe Celko s SQL for Smarties - Advanced SQL Programming P46

Joe Celko s SQL for Smarties - Advanced SQL Programming P46. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 422 CHAPTER 19 PARTITIONING DATA IN QUERIES SELECT tot_cost - tot_qty_on_hand - order_qty_on_hand unit_price AS cost FROM LIFO AS L1 WHERE stock_date SELECT MAX stock_date FROM LIFO AS L2 WHERE tot_qty_on_hand order_qty_on_hand This is straight algebra and a little logic. Find the most recent date that we had enough or more quantity on hand to meet the order. If by dumb blind luck there is a day when the quantity on hand exactly matched the order return the total cost as the answer. If the order was for more than we have in stock then return nothing. If we go back to a day when we had more in stock than the order was for then look at the unit price on that day multiply by the overage and subtract it. Alternatively you can use a derived table and a case expression. The case expression computes the cost of units that have a running total quantity less than the order_qty_on_hand and then it does algebra on the final block of inventory which would put the running total over the limit. The outer query does a sum on these blocks. SELECT SUM AS cost FROM SELECT CASE WHEN SUM order_qty_on_hand THEN ELSE order_qty_on_hand - SUM - END FROM InventoryReceipts AS R1 InventoryReceipts AS R2 WHERE GROUP BY HAVING SUM - order_qty_on_hand AS R3 v FIFO can be done with a similar view or derived table CREATE VIEW FIFO stock_date unit_price tot_qty_on_hand tot_cost AS FIFO and LIFO Subsets 423 SELECT SUM SUM FROM InventoryReceipts AS R1 InventoryReceipts AS R2 WHERE GROUP BY With the corresponding query SELECT tot_cost - tot_qty_on_hand - order_qty_on_hand unit_price AS cost FROM FIFO AS F1 WHERE stock_date SELECT MIN stock_date FROM FIFO AS F2 WHERE .