Prev: How to check that a date field is null and replace by blank value
Next: Fix by Cloning table struction
From: Que on 12 Sep 2009 03:41
Hi Tom

Thanx for your reply , the problem that I am faced with is that the
dates are not always sequencial as we only record the closing balances
of the records that have had any changes

Regards
Que
From: Tom Cooper on 12 Sep 2009 09:42
with cte as
(select
s.Date
, s.Branch
, s.Product
, s.Location
, s.BalanceQty
, row_number() over (partition by s.Branch
, s.Product
, s.Location order by s.Date) As rn
from Stock s)
select
c1.Date
, c1.Branch
, c1.Product
, c1.Location
, c1.BalanceQty
, c1.BalanceQty - coalesce (c2.BalanceQty, 0) As MovmentQty
from
cte c1
left join
cte c2 on c2.rn = c1.rn - 1
and c2.Branch = c1.Branch
and c2.Product = c1.Product
and c2.Location = c1.Location;

Tom

"Que" <aq.mahomed(a)gmail.com> wrote in message
news:8a690a36-d3e1-4e93-abd2-463d8d98ba47(a)e12g2000yqi.googlegroups.com...
> Hi Tom
>
> Thanx for your reply , the problem that I am faced with is that the
> dates are not always sequencial as we only record the closing balances
> of the records that have had any changes
>
> Regards
> Que

From: Que on 12 Sep 2009 11:41
Thanx Tom

I will have a look and try and understand exactly what you done here

Regards
Que
From: --CELKO-- on 12 Sep 2009 13:06
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

>> The problem that I am faced with is that the dates are not always sequential as we only record the closing balances of the records [rows are not records] that have had any changes. <<

This is a history table, but you are mimicking the paper form on a
clipboard at the warehouse. You column names were also too vague or
even reserved words.


Date Branch Product Location
BalanceQty

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 InventoryLevelHistory
(branch CHAR(4) NOT NULL,
location CHAR(4) NOT NULL,
upc CHAR(13) NOT NULL -- industry standard for product
REFERENCES Inventory(upc),
qty_prev_date DATE NOT NULL,
qty_start_date DATE DEFAULT CURRENT_DATE NOT NULL,
qty_end_date DATE, -- null means current price
CHECK (qty_start_date < qty_end_date),
CHECK (qty_start_date = qty_prev_date + INTERVAL 1 DAY), -- prevents
gaps
PRIMARY KEY (branch, location, upc, qty_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. It is also a good
idea to have a VIEW with the current data:

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 code.

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('2007-01-01', 'AA', 100),('2007-01-01', 'BB', 200),
('2007-01-02', 'AA', 100),('2007-01-02', 'BB', 200),
('2007-01-03', 'AA', 100),('2007-01-03', 'BB', 300);

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

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

From: Que on 12 Sep 2009 17:46
Hi Tom

Thanks for the assistance it worked and my problem is solved

I will in future DDL

Many Thanks
Que
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How to check that a date field is null and replace by blank value
Next: Fix by Cloning table struction