Re: set based guru's help wanted

From: Steve Kass (skass_at_drew.edu)
Date: 12/11/04 

Date: Sat, 11 Dec 2004 13:33:15 -0500

Good job! Using sum(T2.x) and sum(T2.x) - T1.x is a great trick for
getting a current and previous total in the same row.

SK

John Gilson wrote:

>"Alex" <nospam@devdex.com> wrote in message news:%23SUdNC43EHA.1292@TK2MSFTNGP10.phx.gbl...
>
>
>>let's suppose s1 holds the available items (q = quantity) for a sales
>>store, and s2 holds the orders from customers. so i want to consume the
>>available quantity with the quantity ordered by a customer.
>>there is no relationship between s1 and s2
>>
>>thanx,
>>Alex
>>
>>*** Sent via Developersdex http://www.developersdex.com ***
>>Don't just participate in USENET...get rewarded for it!
>>
>>
>
>>From your expected results I'll conclude that you want to fill
>customer orders by increasing customer_id and draw from
>store inventory by increasing store_id.
>
>CREATE TABLE Inventory
>(
>store_id INT NOT NULL PRIMARY KEY,
>item_quantity INT NOT NULL CHECK (item_quantity >= 0)
>);
>
>CREATE TABLE Orders
>(
>customer_id INT NOT NULL PRIMARY KEY,
>item_quantity INT NOT NULL CHECK (item_quantity > 0)
>);
>
>// Your sample data
>INSERT INTO Inventory (store_id, item_quantity)
>VALUES (10, 2);
>INSERT INTO Inventory (store_id, item_quantity)
>VALUES (20, 3);
>INSERT INTO Inventory (store_id, item_quantity)
>VALUES (30, 2);
>
>INSERT INTO Orders (customer_id, item_quantity)
>VALUES (1, 4);
>INSERT INTO Orders (customer_id, item_quantity)
>VALUES (2, 2);
>
>SELECT I.store_id, O.customer_id,
> CASE WHEN O.end_running_quantity <= I.end_running_quantity
> THEN O.end_running_quantity
> ELSE I.end_running_quantity
> END -
> CASE WHEN O.start_running_quantity >= I.start_running_quantity
> THEN O.start_running_quantity
> ELSE I.start_running_quantity
> END AS items_consumed_tally
>FROM (SELECT I1.store_id,
> SUM(I2.item_quantity) - I1.item_quantity AS
> start_running_quantity,
> SUM(I2.item_quantity) AS end_running_quantity
> FROM Inventory AS I1
> INNER JOIN
> Inventory AS I2
> ON I2.store_id <= I1.store_id
> GROUP BY I1.store_id, I1.item_quantity) AS I
> INNER JOIN
> (SELECT O1.customer_id,
> SUM(O2.item_quantity) - O1.item_quantity AS
> start_running_quantity,
> SUM(O2.item_quantity) AS end_running_quantity
> FROM Orders AS O1
> INNER JOIN
> Orders AS O2
> ON O2.customer_id <= O1.customer_id
> GROUP BY O1.customer_id, O1.item_quantity) AS O
> ON O.start_running_quantity < I.end_running_quantity AND
> O.end_running_quantity > I.start_running_quantity
>ORDER BY store_id, customer_id;
>
>store_id customer_id items_consumed_tally
>10 1 2
>20 1 2
>20 2 1
>30 2 1
>
>--
>JAG
>
>
>
>

Relevant Pages

  • Re: BIG BUG in Deleting detali records
    ... in the database in order to enforce referential integrity. ... when the customer himself is deleted from the master table. ... customerID int identity not null primary key, ...
    (borland.public.delphi.database.ado)
  • Re: report on customer rage
    ... >> verbatim FYI for those with customer service nightmares (and I ... >> As shoppers head into the holiday shopping season, ... >> A shopper at a Michael's craft store is behind a man doing a price ... who rebuilt yur kombi for ya? ...
    (alt.fashion)
  • Re: TSWLTH revisited
    ... Not the company; not the store manager. ... to JoAnn Fab. ... That, of course, isn't true, and has been replaced by "The customer ... nobody's mind is going to be changed about JoAnn/s Fabrics by ...
    (alt.sewing)
  • Re: Need help setting up a Parent form with three subforms
    ... PartNumandDesc -- no ned to store this in Access, it can be easily combined anytime to display or let the users pick from in a combo or listbox ... you should make a Manufacturers table with MfgID autonumber and MfgName. ... yes -- but you need to make sure the same customer is not in there multiple times -- this often happens when you bring data in from other places. ... What you are calling 'Quote Details' I would call Quotes -- whatever is your header information. ...
    (microsoft.public.access.forms)
  • Re: Best way to do so.
    ... It's a design mistake to try and optimize storage. ... you maximum flexibility and ease in conducting ad-hoc querying etc. ... > customer itself? ... > but as what I see, I've about 50'000 search/month, and I don't store them ...
    (comp.lang.php)
Quantcast