November 10, 2009 at 5:37 am
/*
Ok so 1st the basic table and history table structure
*/
Create Table Model
(ModelID int Identity(1,1) not null
,Code varchar (10)
,Name varchar(50)
,RetailPrice decimal(18,2)
,CreateDate datetime
,ModifyDate datetime)
Create Table Model_Hist
(ModelID int
,Code varchar (10)
,Name varchar(50)
,RetailPrice decimal(18,2)
,CreateDate datetime
,ModifyDate datetime)
GO
/*
Then a trigger to track changes. the production version contains a ton of other info as well like user info etc...
But for what I'm trying to do here I'm not going to bother with those details
*/
Create TRIGGER trig_Model_Hist ON Model
FOR UPDATE
AS
INSERT INTO Model_Hist
(ModelID
,Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
SELECT
del.ModelID
,del.Code
,del.Name
,del.RetailPrice
,del.CreateDate
,getdate()
FROM
deleted del
GO
/*
Now to Insert some test data
*/
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD001'
,'PRODUCT 1'
,10.00
,'2009-01-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD002'
,'PRODUCT 2'
,11.00
,'2009-02-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD003'
,'PRODUCT 3'
,12.00
,'2009-03-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD004'
,'PRODUCT 4'
,15.00
,'2009-04-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD005'
,'PRODUCT 5'
,20.00
,'2009-05-01 00:00:00'
,null
/*
Now We need to modify some retail prices:
*/
update Model
Set RetailPrice = 10.50
Where Code = 'PROD001'
update Model
Set RetailPrice = 11.50
Where Code = 'PROD002'
update Model
Set RetailPrice = 12.50
Where Code = 'PROD003'
update Model
Set RetailPrice = 10.75
Where Code = 'PROD001'
/*
Now to select the data??????
*/
I need to select the data to look as follows:
Code RetailPrice DateFrom DateTo
Usually i can figure these thing out, and have written a few function that refrence these history tables, but this one has been a headache for 2 days now.
Any input on how I can do this?
November 10, 2009 at 7:47 am
Thanks for providing the test data. Can you be more specific about the data you want returned? Do you want results like this:
Code RetailPrice DateFrom DateTo
---- ---------- -------- ------
PROD001 10.00 2009-01-01 2009-11-10 + timepart
PROD001 10:50 2009-11-10 2009-11-10 + timepart
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 10:32 pm
Exactly like that, allthough the time part is not that important.
The prices are only updated once every 6 months or so and I will most likely trim and convert the from date with 00:00:00 and to date with 23:59:59
But yeah in the test data we will need to look at the time stamp, unless we update the history table dates to be a day later or something.
So for each stock Item we need a retail price, with a from and to date in which that price was active.
November 11, 2009 at 7:17 am
Are you really on 2000? Here is an answer for 2005/2008:
/*
Ok so 1st the basic table and history table structure
*/
IF OBJECT_ID('dbo.Model', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Model
END
IF OBJECT_ID('dbo.Model_Hist', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.Model_Hist
END
Create Table Model
(ModelID int Identity(1,1) not null
,Code varchar (10)
,Name varchar(50)
,RetailPrice decimal(18,2)
,CreateDate datetime
,ModifyDate datetime)
Create Table Model_Hist
(ModelID int
,Code varchar (10)
,Name varchar(50)
,RetailPrice decimal(18,2)
,CreateDate datetime
,ModifyDate datetime)
GO
/*
Then a trigger to track changes. the production version contains a ton of other info as well like user info etc...
But for what I'm trying to do here I'm not going to bother with those details
*/
Create TRIGGER trig_Model_Hist ON Model
FOR UPDATE
AS
INSERT INTO Model_Hist
(ModelID
,Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
SELECT
del.ModelID
,del.Code
,del.Name
,del.RetailPrice
,del.CreateDate
,getdate()
FROM
deleted del
GO
/*
Now to Insert some test data
*/
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD001'
,'PRODUCT 1'
,10.00
,'2009-01-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD002'
,'PRODUCT 2'
,11.00
,'2009-02-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD003'
,'PRODUCT 3'
,12.00
,'2009-03-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD004'
,'PRODUCT 4'
,15.00
,'2009-04-01 00:00:00'
,null
INSERT INTO Model
(Code
,Name
,RetailPrice
,CreateDate
,ModifyDate)
Select
'PROD005'
,'PRODUCT 5'
,20.00
,'2009-05-01 00:00:00'
,null
/*
Now We need to modify some retail prices:
*/
update Model
Set RetailPrice = 10.50
Where Code = 'PROD001'
update Model
Set RetailPrice = 11.50
Where Code = 'PROD002'
update Model
Set RetailPrice = 12.50
Where Code = 'PROD003'
/*
added a 1 minute delay so that there would
be different modify date for this entry.
*/
WAITFOR DELAY '00:01:00'
update Model
Set RetailPrice = 10.75
Where Code = 'PROD001'
/* Return the Data as desired */
;WITH cteModelINFO AS
(
SELECT
ModelID,
Code,
[Name],
RetailPrice,
CreateDate,
'1/1/2999' AS ModifyDate
FROM
dbo.Model AS M
UNION ALL
SELECT
*
FROM
dbo.Model_Hist AS MH
),
cteModelOrder AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY cteModelINFO.ModelId ORDER BY cteModelINFO.ModifyDate Desc) AS row_id,
*
FROM
cteModelINFO
)
SELECT
MO1.modelid,
MO1.code,
MO1.NAME,
MO1.RetailPrice,
ISNULL(MO2.modifydate, MO1.createdate) AS price_start,
NULLIF(MO1.modifydate, '1/1/2999') AS price_end
FROM
cteModelOrder MO1 LEFT JOIN
cteModelOrder MO2 ON
MO1.ModelId = MO2.ModelId AND
MO1.row_id = MO2.row_id - 1
ORDER BY
modelid,
price_start
Obviously if you are really on 2000 then this won't work. I'll work on a 200 solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2009 at 7:29 am
Okay, here's a 2000 solution that I think meets your needs:
DECLARE @table TABLE (row_id INT IDENTITY(1,1), modelid INT, code VARCHAR(10), NAME VARCHAR(50), retailprice DECIMAL(18,2), createdate DATETIME, modifydate DATETIME)
INSERT INTO @table (
modelid,
code,
[NAME],
retailprice,
createdate,
modifydate
)
SELECT
modelid,
code,
[NAME],
retailprice,
createdate,
ISNULL(modifydate, '1/1/2999') AS modifydate
FROM
model as M
UNION ALL
SELECT
modelid,
code,
[NAME],
retailprice,
createdate,
ISNULL(modifydate, '1/1/2999') AS modifydate
FROM
dbo.Model_Hist AS MH
ORDER BY
modelid,
modifydate DESC
SELECT
MO1.modelid,
MO1.code,
MO1.NAME,
MO1.RetailPrice,
ISNULL(MO2.modifydate, MO1.createdate) AS price_start,
NULLIF(MO1.modifydate, '1/1/2999') AS price_end
FROM
@table MO1 LEFT JOIN
@table MO2 ON
MO1.ModelId = MO2.ModelId AND
MO1.row_id = MO2.row_id - 1
ORDER BY
MO1.modelid,
price_start
I make no warranties that either solution is the best solution. Someone may have a better way.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 11, 2009 at 11:59 pm
Hi,
Yes, I am in 2000.
We have started discussions for the new version of the application, and this will run on 2008, but we have not even started flowcharting yet. This development is at least still a year away.
So for now, I need to code in hieroglyphics still 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply