March 12, 2004 at 6:16 am
I have a table called PriceName having schema
CREATE TABLE PriceName (PriceNameID INT IDENTITY(1,1), Price DECIMAL(9,2), PriceName VARCHAR(100))
In this table the data is populated in the Price field. Now i want to update this table to update the PriceName column, that shows the range in which that price lies.
Ex is shown below
PriceNameID Price PriceName
1 200 0-200
2 300 200-300
3 350 300-350
......
.....
99 1500 1400-1500
100 NULL 1500 & Above
Can someone suggest me the update query for this ??
Regards
Prashant Thakwanithakwani_prashant@yahoo.co.in
March 12, 2004 at 6:57 am
UPDATE p
SET PriceName = (CASE
WHEN p.Price IS NULL THEN CAST(FLOOR(a.Price) AS varchar) + ' & Above'
ELSE CAST(FLOOR(ISNULL(a.Price,0)) AS varchar) + '-' + CAST(FLOOR(p.Price) AS varchar)
END)
FROM PriceName p
LEFT OUTER JOIN PriceName a
ON a.PriceNameID = (p.PriceNameID - 1)
Far away is close at hand in the images of elsewhere.
Anon.
March 12, 2004 at 7:10 am
You can just run this using the update statement or automate it by adding it as a trigger, as entered below.
Update PriceName
set PriceName = Case
When cast(Price as int) between 0 and 200 then '0-200'
When cast(Price as int)> 1500 then 'Over 1500'
When Price is Null then Null
else Cast((convert(int,Price)-(convert(int,Price)%100)) as varchar)
+ '-' + Cast( ((convert(int,Price)-(convert(int,Price)%100))+100) as varchar)
end
If using trigger should modify code to only affect rows inserted/updated.
create trigger tr_PriceName on PriceName for insert,update as
Update PriceName
set PriceName = Case
When cast(p.Price as int) between 0 and 200 then '0-200'
When cast(p.Price as int)> 1500 then 'Over 1500'
When p.Price is Null then Null
else Cast((convert(int,p.Price)-(convert(int,p.Price)%100)) as varchar)
+ '-' + Cast( ((convert(int,p.Price)-(convert(int,p.Price)%100))+100) as varchar)
end
From PriceName p inner join INSERTED i on p.PriceNameID = i.PriceNameID
March 12, 2004 at 7:53 am
David's Solution is OK as long as you have CONSECUTIVES Ids . If you don't then you can:
DECLARE @strLPriceName varchar(100)
SELECT @strLPriceName = ''
UPDATE PriceName
SET @strLPriceName = PriceName = CASE When r.strPrice IS NULL THEN Right(@strLPriceName,Charindex('-',Reverse(@strLPriceName))-1) + ' & Above'
ELSE (CAse When Len(@strLPriceName) = 0 then '0'
Else Right(@strLPriceName,Charindex('-',Reverse(@strLPriceName))-1)END)+ '-'+ r.strPrice END
FROM PriceName b
INNER JOIN (
SELECT TOP 100 PERCENT Cast(Floor(Price) as Varchar) as strPrice, PriceNameID
FROM PriceName
ORDER BY PriceNameID
) r ON
r.PriceNameID = b.PriceNameID
select * from PriceName
* Noel
March 12, 2004 at 8:05 am
Or just something like this:
UPDATE p SET PriceName = LTRIM(STR(ISNULL(
(SELECT MAX(Price)
FROM PriceName
WHERE Price < p.Price),CASE WHEN Price IS NULL THEN
(SELECT MAX(Price)
FROM PriceName) ELSE 0 END))) + ISNULL('-' + LTRIM(STR(Price)),' & Above')
FROM PriceName p
--Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply