[RESOLVED] insert based on value differences

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

[RESOLVED] insert based on value differences

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-03-12 : 10:47:29

I have a table that stores attribute values for a name record:

Name Attr1 Attr2 Attr3 InsertDate
---- ------ ------ ------ ----------
abc A B C 2011-03-12

The purpose of this table is to keep track of attribute changes to the name. I would like to insert a new record for each Name value if any of the attribute values has changed from the last entry in the table. The current values are stored in a work table that I create from a raw data feed from a disparate source.
So I would right an insert statement based on all records from a left join of my work table on the latest Name record in my target table.


INSERT INTO TargetTable (
Name, Attr1, Attr2, Attr3, InsertDate
)
SELECT Name, attr1, attr2, attr3, GetDate()
FROM WorkTable w
LEFT JOIN TargetTable t ON w.Name = t.Name AND w.Attr1 = t.attr1
WHERE t.Name AND t.Attr1....IS NULL

the part that I'm missing is how to join only to the latest record in my target table. Or might there be a better approach to this problem. Any direction would be helpful.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-12 : 22:34:57

Please post real DDL. Learn to use ISO-11179 rules for the data element mames, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. Please learn differences between rows and records.

>> I have a table that stores attribute values for a name record [sic]:<<

There is no generic, magical universal name; it is the name of something in particular.

>> The purpose of this table is to keep track of attribute changes to the dog_name.<<

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 continuum. 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).

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-03-13 : 16:09:31

I appreciate your help.
With regards to the magical name column, the column of my table is indeed [Name].
I apologize for my lack of differentiation between the use of the terminologies row and record.
Unfortunately for me English is not my primary language and I also must have misunderstood the purpose of this forum which as I understand now is only there to provide guidance and assistance from distinguished minds like yourself to meager minds like me if the latter understand the proper protocol to ask a question. Forgive me if my lack of knowledge and preparedness has offended you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources