February 25, 2009 at 4:49 am
First of all, the reason why your code turned back into a slow monster is because you used a While Loop... AGAIN! If you're gonna keep doing that to perfectly fast code, then I'm all done. 😉
Second, the only reason why the code might not be right is because the assumptions that you implied with your test data that I took to heart are not correct.
So, what is the actual condition of the data? [font="Arial Black"]Is the Detail_ID in sorted order by Inventory_Code and Detail_Date or not? [/font]
--Jeff Moden
February 25, 2009 at 5:31 am
The Detail_ID is in sorted order by Inventory_Code and Detail_Date.
Why is that when I pass a parameter(Inventory_Code) values are updated correctly? If it was not sorted by Inventory_Code and Detail_Date. Only problem arises is when I execute the same proc for all the Inventory_code.
February 25, 2009 at 6:11 am
Ashwin M N (2/25/2009)
The Detail_ID is in sorted order by Inventory_Code and Detail_Date.Why is that when I pass a parameter(Inventory_Code) values are updated correctly? If it was not sorted by Inventory_Code and Detail_Date. Only problem arises is when I execute the same proc for all the Inventory_code.
Dunno... I'll take a look.
--Jeff Moden
February 25, 2009 at 6:39 am
Thanks Jack... lotta tension on this side, lately, and I had to make sure I wasn't tuggin' on a friend's chain.
Phew! Jeff 😀
Thought you were trying to poke the hornets nest again 😉
Far away is close at hand in the images of elsewhere.
Anon.
February 25, 2009 at 9:01 pm
Alright, Ashwin... here's the whole shootin' match including your original posted table except that I included the clustered PK. I've verified that it returns exactly what your code did and they both do it correctly... so, what's the problem?
--===== Conditionally drop the test table so we can easily rerun when needed
IF OBJECT_ID('TempDB.dbo.Temp') IS NOT NULL
DROP TABLE dbo.Temp
GO
--===== Recreate the test table, WITH A CLUSTERED PK
CREATE TABLE dbo.Temp
(
Detail_ID NUMERIC(18,0) IDENTITY (1,1) NOT NULL ,
Inventory_Code INT NULL ,
Detail_Date DATETIME NULL ,
Open_Price DECIMAL(10, 2) NULL ,
Close_Price DECIMAL(10, 2) NULL ,
Net_Volume BIGINT NULL ,
Net_Value MONEY NULL ,
Prev_close DECIMAL(10, 2) NULL
CONSTRAINT PK_Temp_Detail_ID
PRIMARY KEY CLUSTERED (Detail_ID)
)
GO
--===== Populate the test table with the original data given in the post, but in an abbreviated fashion
INSERT INTO dbo.Temp
(Inventory_Code, Detail_Date, Open_Price, Close_Price, Net_Volume, Net_Value, Prev_close)
SELECT 500002,'Feb 2 2009 12:00:00:000AM',480.00,455.10,24172,11192849.0000,NULL UNION ALL
SELECT 500002,'Feb 3 2009 12:00:00:000AM',455.00,461.10,30706,14205560.0000,NULL UNION ALL
SELECT 500002,'Feb 4 2009 12:00:00:000AM',468.00,463.30,29213,13633590.0000,NULL UNION ALL
SELECT 500002,'Feb 5 2009 12:00:00:000AM',467.00,437.40,73687,32645242.0000,NULL UNION ALL
SELECT 500002,'Feb 6 2009 12:00:00:000AM',441.00,454.90,43711,19665019.0000,NULL UNION ALL
SELECT 500002,'Feb 9 2009 12:00:00:000AM',456.20,462.55,34984,15992694.0000,NULL UNION ALL
SELECT 500002,'Feb 10 2009 12:00:00:000AM',465.00,456.25,44572,20485640.0000,NULL UNION ALL
SELECT 500002,'Feb 11 2009 12:00:00:000AM',451.00,464.20,42001,19242949.0000,NULL UNION ALL
SELECT 500002,'Feb 12 2009 12:00:00:000AM',467.00,455.10,54010,25109443.0000,NULL UNION ALL
SELECT 500002,'Feb 13 2009 12:00:00:000AM',454.90,441.40,181224,81195470.0000,NULL UNION ALL
SELECT 500002,'Feb 19 2009 12:00:00:000AM',409.00,393.20,89347,35786215.0000,NULL UNION ALL
SELECT 500002,'Feb 16 2009 12:00:00:000AM',441.00,417.20,182342,77714063.0000,NULL UNION ALL
SELECT 500002,'Feb 17 2009 12:00:00:000AM',410.00,405.15,161120,65317672.0000,NULL UNION ALL
SELECT 500002,'Feb 18 2009 12:00:00:000AM',405.00,403.55,86998,35131698.0000,NULL
--===== Add some data with a different inventory code.
INSERT INTO Temp
(Inventory_Code, Detail_Date, Open_Price, Close_Price, Net_Volume, Net_Value, Prev_Close)
SELECT 500003, Detail_Date, Open_Price-5, Close_Price-5, Net_Volume-5, Net_Value-5, Prev_Close
FROM dbo.Temp
ORDER BY Detail_ID
--===== Display the unmodified data as a sanity check
SELECT * FROM dbo.Temp ORDER BY Detail_ID
--===== Do the update to solve the problem =============================================================
--===== Declare some obviously named variables
DECLARE @Inventory_Code_To_Update INT
SET @Inventory_Code_To_Update = 500003
DECLARE @Prev_Close DECIMAL(10,2)
DECLARE @Prev_Inventory_Code INT
--===== Do the "data smear" using a "quirky update"
UPDATE dbo.Temp
SET @Prev_Close = Prev_Close = CASE WHEN Inventory_Code = @Prev_Inventory_Code
THEN @Prev_Close
ELSE Open_Price
END,
@Prev_Close = Close_Price,
@Prev_Inventory_Code = Inventory_Code
FROM dbo.Temp WITH(INDEX(0),TABLOCKX)
WHERE Inventory_Code = @Inventory_Code_To_Update
OR @Inventory_Code_To_Update IS NULL
--===== Display the modified data to check the update
SELECT * FROM dbo.Temp ORDER BY Detail_ID
The big difference will be in the performance... the code above will blow the doors off any While loop solution.
--Jeff Moden
February 27, 2009 at 4:08 am
Now continuing with the previous update. I have added a new column to the table with the column name Onweek_PercentageChange.
Alter table temp add Onweek_percentageChange decimal(10,2).
This column stores the value(percentage) change in close_price between todays close_price and close_price of the same inventory_code 5 days before.
Inventory_Code Detail_Date Close_Price Onweek_PercentageChange
500002 2009-02-02 455.10 NULL
5000022009-02-03 461.10 NULL
5000022009-02-04 463.30NULL
5000022009-02-05 437.40NULL
5000022009-02-06 454.90 ((454.90 - 455.10)/455.10)*100
5000022009-02-09 462.55 ((462.55 - 461.10)/461.10)*100
5000022009-02-10 456.25
5000022009-02-11 464.20
5000022009-02-12 455.10
5000022009-02-13 441.40
I tried to do a update query on this column using the same logic but failed.
How can this be done using the logic mentioned below
UPDATE dbo.Temp
SET @Onweek_PercentageChange = Prev_Close = CASE WHEN Inventory_Code = @Prev_Inventory_Code - 5
THEN @Prev_Close
ELSE Open_Price
END,
@Onweek_PercentageChange = Close_Price,
@Prev_Inventory_Code = Inventory_Code -5
FROM dbo.Temp WITH(INDEX(0),TABLOCKX)
WHERE Inventory_Code = @Inventory_Code_To_Update
OR @Inventory_Code_To_Update IS NULL
February 27, 2009 at 5:47 pm
Can you post the actual code you tried? The code you posted has no percentage calculations in it.
--Jeff Moden
March 1, 2009 at 10:31 pm
I did for Oneday_percentchange column and the results are correct.
The logic for that is given below.
DECLARE @OneDay_percentChange DECIMAL(10,2)
DECLARE @Inventory_code INT
UPDATE dbo.Temp
SET @OneDay_percentChange = OneDay_percentChange = CASE WHEN Inventory_code = @Inventory_code
THEN ((Close_Price - @OneDay_percentChange)/@OneDay_percentChange) * 100
ELSE null
END,
@OneDay_percentChange = Close_Price,
@Inventory_code = Inventory_code
FROM dbo.Temp WITH(INDEX(0),TABLOCKX)
where Inventory_code = @Inventory_code
But for OneWeek_PercentChange I not able to get desired results.
DECLARE @OneWeek_PercentChange DECIMAL(10,2)
DECLARE @Prev_Scrip_code INT
UPDATE dbo.Temp
SET @OneWeek_PercentChange = OneWeek_PercentChange = CASE WHEN Inventory_code = @Inventory_code - 5
THEN ((Close_Price - @OneWeek_PercentChange)/@OneWeek_PercentChange) * 100
ELSE null
END,
@OneWeek_PercentChange = Close_Price,
@Inventory_code = Inventory_code
FROM dbo.Temp WITH(INDEX(0),TABLOCKX)
where Inventory_code = @Inventory_code
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply