Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #379

Re: Help needed to write a qury

Show all headers | View raw

Please post real DDL. Learn to use ISO-11179 rules for the data
element names, avoid needless dialect and use ISO-8601 temporal
formats, codes and so forth. Based on the vague narrative you did
post, the design is wrong.

Let me use a history table for price changes.  The fact is that a
price had duration.  This is the nature of time and other continuums.
So a basic history table looks like this in SQL/PSM

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL   -- industry standard
  REFERENCES Inventory(upc),
 price_prev_date DATE NOT NULL,
 price_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
 price_end_date DATE, -- null means current price
 CHECK(price_start_date < price_end_date),
 CHECK (price_start_date = price_prev_date + INTERVAL 1 DAY), --
prevents gaps
 PRIMARY KEY (upc, price_start_date),
 item_price DECIMAL (12,4) NOT NULL
  CHECK (item_price > 0.0000),
 etc.);

You use a BETWEEN predicate to get the appropriate price.  You can
enforce the "one null per item" with a trigger but techically this
should work:

CHECK (COUNT(*) OVER (PARTITION BY upc)
       = COUNT(price_end_date) OVER (PARTITION BY upc) +1)

SELECT ..
  FROM PriceHistory AS H, Orders AS O
 WHERE O.sales_date BETWEEN H.price_start_date
           AND COALESCE (price_end_date, CURRENT_DATE);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
  FROM PriceHistory
 WHERE price_end_date IS NULL;

Now your only problem is to write a stored procedure that will update
the table and insert a new row.  You can do this with a single MERGE
statement, or with a short block of SQL/PSM code:

CREATE PROCEDURE UpdateItemPrice
(IN in_upc CHAR(13), IN new_item_price DECIMAL (12,4))
LANGUAGE SQL
BEGIN ATOMIC
UPDATE PriceHistory
   SET price_end_date = CURRENT_DATE
 WHERE upc = in_upc;
INSERT INTO PriceHistory (upc, price_prev_date, price_start_date,
price_end_date, item_price)
VALUES (in_upc, CURRENT_DATE, CURRENT_DATE + INTERVAL '1' DAY, NULL,
new_item_price);
END;

This will make the price change go into effect tomorrow.

There is a common kludge to repair a failure to design a history table
properly that you can put in a VIEW if you are not able to set things
right.  Assume that every day we take a short inventory and put it in
a journal. The journal is a clip board paper form that has one line
per item per day, perhaps with gaps in the data.  We want to get this
into the proper format, namely periods shown with a (start_date,
end_date) pair for durations where each item had the same quantity on
hand. This is due to Alejandro Mesa

CREATE TABLE InventoryJournal
(journal_date DATETIME NOT NULL,
 item_id CHAR(2) NOT NULL,
  PRIMARY KEY (journal_date, item_id),
 onhand_qty INTEGER NOT NULL);

WITH ItemGroups
AS
(SELECT journal_date, item_id, onhand_qty,
        ROW_NUMBER() OVER(ORDER BY item_id, journal_date, onhand_qty)
        - ROW_NUMBER() OVER(PARTITION BY item_id, onhand_qty
                            ORDER BY journal_date) AS item_grp_nbr
   FROM Journal),

QtyByDateRanges
AS
(SELECT MIN(journal_date) AS start_date,
        MAX(journal_date) AS end_date,
        item_id, onhand_qty
   FROM ItemGroups
  GROUP BY item_id, onhand_qty, item_grp_nbr)

SELECT start_date, end_date, item_id, onhand_qty
  FROM QtyByDateRanges;

This might be easier to see with some data and intermediate steps

INSERT INTO InventoryJournal
VALUES('2013-01-01', 'AA', 100),('2013-01-01', 'BB', 200),
      ('2013-01-02', 'AA', 100),('2013-01-02', 'BB', 200),
      ('2013-01-03', 'AA', 100),('2013-01-03', 'BB', 300);

start_date   end_date     item_id onhand_qty
==========================================
'2013-01-01' '2013-01-03'  'AA'    100
'2013-01-01' '2013-01-02'  'BB'    200
'2013-01-03' '2013-01-03'  'BB'    300

Now, download the Rick Snodgrass book on Temporal Queries in SQL from
the University of Arizona website (it is free).

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Help needed to write a qury Dinesh <dinesht15@gmail.com> - 2011-04-24 10:26 -0700
  Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-24 12:27 -0700
  Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-24 21:51 +0200
    Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 06:50 +0200
      Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 10:14 +0200
        Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 11:52 +0200
          Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 15:30 +0200
            Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 15:31 +0200
              Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 16:29 +0200
              Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-25 11:41 -0700
                Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 23:18 +0200
                Re: Help needed to write a qury Dinesh <dinesht15@gmail.com> - 2011-04-26 01:58 -0700
    Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-25 06:28 -0700
    Re: Help needed to write a qury Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-04-25 17:44 +0200
      Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-05-25 00:01 +0200
        Re: Help needed to write a qury Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-05-25 21:15 +0200
  Re: Help needed to write a qury --CELKO-- <jcelko212@earthlink.net> - 2011-05-26 06:19 -0700

csiph-web