May 17, 2007 at 10:38 pm
I have a table of inventory transactions which details the cost of each transaction. There are about 21000 lines in this table at present, consisting of maybe a few hundred items.
I need to add records to this table for each item, at the end of each day. These records will detail the cost of each item at the end of each day. Moving forward, this is not a problem - the problem I have is writing the code to populate the history. The cost can be based on the last transacted cost for each item.
So, for each item I'll expect to see something like:
| item no. | cost record identifier | time id | cost 1 | cost 2 | cost 3 |
How can I write a SQL statement to populate my inventory transactions table with a daily history of item costs, based on the last transacted cost for each item?
May 18, 2007 at 1:01 am
I've come up with the following code to give me the maximum cost for each day where transactions exist:
SELECT MAX(inv_trx_id),
item_id,
trx_date_id,
MAX(sc_total)
FROM FactInventoryTrx
GROUP BY item_id, trx_date_id
ORDER BY item_id, trx_date_id
Is this useful to me in creating my script to log each item's cost each day?
May 18, 2007 at 9:57 am
--Assuming you have "inventory tables similiar to the following:
--(you should post DDL for all tables involved when looking for this type of help)
declare @invItems_ table (itemNo_ int, itemName_ varchar(20))
declare @invItemTrxs_ table (itemNo_ int, trxDate_ datetime, trxPrice_ decimal(5,2))
--This is the "fact" table
declare @invPrice_fact_ table (itemNo_ int, priceDate_ datetime, price_ decimal(5,2))
--Given these items
insert into @invItems_ values (1,'Item1')
insert into @invItems_ values (2,'Item2')
insert into @invItems_ values (3,'Item3')
--and these transactions
insert into @invItemTrxs_ values (1, '1/1/2007 00:00:01', 1.10)
insert into @invItemTrxs_ values (1, '1/1/2007 00:00:02', 1.20)
insert into @invItemTrxs_ values (1, '1/1/2007 00:00:03', 1.30)
insert into @invItemTrxs_ values (1, '1/2/2007 00:00:01', 1.10)
insert into @invItemTrxs_ values (1, '1/2/2007 00:00:02', 1.20)
insert into @invItemTrxs_ values (1, '1/2/2007 00:00:03', 1.30)
insert into @invItemTrxs_ values (2, '1/1/2007 00:00:01', 1.10)
insert into @invItemTrxs_ values (2, '1/1/2007 00:00:02', 1.20)
insert into @invItemTrxs_ values (2, '1/1/2007 00:00:03', 1.30)
insert into @invItemTrxs_ values (2, '1/2/2007 00:00:01', 1.10)
insert into @invItemTrxs_ values (2, '1/2/2007 00:00:02', 1.20)
insert into @invItemTrxs_ values (2, '1/2/2007 00:00:03', 1.30)
insert into @invItemTrxs_ values (3, '1/1/2007 00:00:01', 1.10)
insert into @invItemTrxs_ values (3, '1/1/2007 00:00:02', 1.20)
insert into @invItemTrxs_ values (3, '1/1/2007 00:00:03', 1.30)
insert into @invItemTrxs_ values (3, '1/2/2007 00:00:01', 1.10)
insert into @invItemTrxs_ values (3, '1/2/2007 00:00:02', 1.20)
insert into @invItemTrxs_ values (3, '1/2/2007 00:00:03', 1.30)
--The following statement will extract the values you want and place them in your
--fact table. The key here is using a left inner join on your transaction
--table to a computed table that has only the item number and max date.
--NOTE: Under the given conditions I would have a unique key on the fact table of (itemNo_,priceDate_)
-- and enforce the "date" value to be timeless with a check statement (I'll add example at bottom)
insert into @invPrice_fact_ (itemNo_, priceDate_, price_)
select a.itemNo_, a.trxDate_, a.trxPrice_
from @invItemTrxs_ a join (select itemNo_, max(trxDate_)
from @invItemTrxs_ a
group by itemNo_) as b (itemNo_, trxDate_) on (a.itemNo_ = b.itemNo_ and a.trxDate_ = b.trxDate_)
select * from @invPrice_fact_
/* -- OUTPUT
itemNo_ priceDate_ price_
----------- ----------------------- ---------------------------------------
3 2007-01-02 00:00:03.000 1.30
2 2007-01-02 00:00:03.000 1.30
1 2007-01-02 00:00:03.000 1.30
*/
/* --Enforcing no time
create table invPrice_fact_ (
itemNo_ int not null,
priceDate_ datetime not null constraint ckc_invPrice_fact_01_ check (priceDate_ = CAST(FLOOR(CAST(priceDate_ AS FLOAT)) AS DATETIME)),
price_ decimal(5,2),
constraint pk_invPrice_fact_ primary key (itemNo_,priceDate_)
)
--Now I would change the insert into statement above to the following
insert into invPrice_fact_ (itemNo_, priceDate_, price_)
select a.itemNo_, CAST(FLOOR(CAST(a.trxDate_ as FLOAT)) AS DATETIME), a.trxPrice_
etc.....
--Now the time is stripped off the result and the inserts into the fact table must be unique or an error will occur.
*/
--JAMES
May 21, 2007 at 12:32 am
Thanks James,
I've come up with the following code:
CREATE VIEW VwHistoryCostPopulator AS
SELECT TOP 100 PERCENT list.item_id,
1 AS trxtype_id,
1 AS reftype_id,
list.time_id AS trx_date_id,
0 AS quantity,
-- repeat about 10 times for relevant different fields
ISNULL(
(ISNULL(fact.sc_total, (
SELECT f2.sc_total
FROM FactInventoryTrx f2
WHERE f2.inv_trx_id = (
SELECT MAX(f3.inv_trx_id)
FROM FactInventoryTrx f3
WHERE f3.trx_date_id list.time_id AND
f3.item_id = list.item_id))
) AS sc_total
-- End repeat
FROM
(SELECT DISTINCT item_id, time_id, swl_id, uom_id
FROM FactInventoryTrx fact CROSS JOIN DimTime time
WHERE swl_id IN (1, 7)) list LEFT OUTER JOIN
(SELECT MAX(inv_trx_id) AS inv_trx_id,
item_id,
trx_date_id,
MAX(sc_total) AS sc_total
FROM FactInventoryTrx
GROUP BY item_id, trx_date_id) cost ON list.item_id = cost.item_id AND list.time_id = cost.trx_date_id LEFT OUTER JOIN FactInventoryTrx fact
ON cost.inv_trx_id = fact.inv_trx_id
ORDER BY list.item_id, list.time_id
----------------------------
This has done exactly what I needed, but it's very inefficient. Is there a way I can tidy it up?
May 21, 2007 at 7:31 am
Hmm, are we still working the same inventory problem? Post the DDL (definition) for the FactInventoryTrx table, and your DateTime table, along with some test data (three or four records) and and example of the output you expect. Then I can look at the SQL you developed. Just glancing at I can see why its so inefficient (sub selects, with sub selects, follwed by a derived table that is a cross join between your entire transaction table and your date table), that is asking for trouble. How many transactions do you have and how many datetime records do you have? If this is a major inventory spanning years of data this will quickly become and unreasonable demand on server resources, not to mention this is a view and I would assume it means it will be utilized many times. Right off the bat, you should think of changing this into a FACT table and populate each night with a query. It seems to me that the data for any given day in the past is unlikely to change so why recalculate EVERY time you need it.
James.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply