johnnyaction
asked on
SQL Server Query Syntax
I have a table that I append to every week with the latest weeks worth of data. I have a field called Region that once in a while gets changed to a new Region. So depending on the latest weeks data I need to update ALL the other values, for a StoreID, where the Regions are different. For example
Data in table
StoreID Region Week
1 NE 1
2 MW 1
3 SW 1
New Data adding to the table
StoreID Region Week
1 NW 2
2 MW 2
3 SE 2
Now the table looks like this
StoreID Region Week
1 NE 1
2 MW 1
3 SW 1
1 NW 2
2 MW 2
3 SE 2
After I add the new data to the table I need to update the Regions with the latest weeks data. So, StoreID 1 - Region should be updated to NW and for StoreID 3- Region should be updated to SE. How would I go about updating all the regions to the last weeks Region? Any help would be appreciated!!
Data in table
StoreID Region Week
1 NE 1
2 MW 1
3 SW 1
New Data adding to the table
StoreID Region Week
1 NW 2
2 MW 2
3 SE 2
Now the table looks like this
StoreID Region Week
1 NE 1
2 MW 1
3 SW 1
1 NW 2
2 MW 2
3 SE 2
After I add the new data to the table I need to update the Regions with the latest weeks data. So, StoreID 1 - Region should be updated to NW and for StoreID 3- Region should be updated to SE. How would I go about updating all the regions to the last weeks Region? Any help would be appreciated!!
ASKER
Couldnt I do it on the Max(Week) somehow? Id like to be able to just update all the Regions based on the 'last' weeks Region value?
Yes you could, good idea!
select top 1 from Table where StoreID=1 order by Week DESC.
There is likely an easier way to get them all also, would need some testing.
select top 3 from Table where StoreID in (1,2,3) order by Week DESC.
and then....
select top 3 from Table where StoreID in (1,2,3) order by Week DESC.
UPDATE Table TBLU
SET Region = (select top 3 from Table TBLS where StoreID in (1,2,3) order by Week DESC.)
WHERE TBLU.StoreID=TBLS.StoreID
Copy that to a TEMP table and test it first of course!
-greg
select top 1 from Table where StoreID=1 order by Week DESC.
There is likely an easier way to get them all also, would need some testing.
select top 3 from Table where StoreID in (1,2,3) order by Week DESC.
and then....
select top 3 from Table where StoreID in (1,2,3) order by Week DESC.
UPDATE Table TBLU
SET Region = (select top 3 from Table TBLS where StoreID in (1,2,3) order by Week DESC.)
WHERE TBLU.StoreID=TBLS.StoreID
Copy that to a TEMP table and test it first of course!
-greg
ASKER
I appreciate your help with this but there has to be a better way of updating these values. I would rather use MAX(Week) instead of worrying about using order by desc because when I add the data every week I just want to be able to run an update on all the data and if there is a change then have it updated. Thanks again
try this.
;with cte as (
select *,
ROW_NUMBER() over (partition by StoreID order by [Week] desc) rn,
MAX([Week]) over (partition by StoreID) max_StoreID
from your_table)
update t1
set t1.Region = t2.Region
from your_table t1
join cte t2 on t1.StoreID = t2.StoreID
where t2.rn = 1 and t1.[Week] <> t2.[Week]declare @table table (StoreID int, Region varchar(10),[Week] int)
insert @table values(1 , 'NE' , 1)
insert @table values(2 , 'MW' , 1)
insert @table values(3 , 'SW' , 1 )
insert @table values(1 , 'NW' , 2)
insert @table values(2 , 'MW' , 2)
insert @table values(3 , 'SE' , 2)
;with cte as (
select *,
ROW_NUMBER() over (partition by StoreID order by [Week] desc) rn,
MAX([Week]) over (partition by StoreID) max_StoreID
from @table)
update t1
set t1.Region = t2.Region
from @table t1
join cte t2 on t1.StoreID = t2.StoreID
where t2.rn = 1 and t1.[Week] <> t2.[Week]
select * from @table
/*
StoreID Region Week
1 NW 1
2 MW 1
3 SE 1
1 NW 2
2 MW 2
3 SE 2
*/
But if you update the previous weeks data, you lost the history. Are you fine with that?
ASKER
Yes, I am only concerned with what the latest Region is. I save copies of the table every week anyway. I will test, thank you
ASKER
This didnt work for my data. Every week I append data for the same store ids to the table and I need every records region for that store id to be updated with the latest weeks region. I just did a check on the table after I did the update and the first value I checked was not updated? Does this make sense? Thanks for your help
ASKER
To make this even easier....The last week I entered is week number 7. If I could just simply update every region by StoreID with Week 7's Region and then I could change the week number every week then that would work...What do you think?
Can you post the not working data?
ASKER
This is data that is in my table right now..
Store id Region week
33 MW Racks 6
33 MW Racks 7
33 NW/NCAL Racks 5
Week 5 was NW/NCAL Racks prior to the update I ran. Week 6 was already MW Racks and so was Week 7. What I need is for every weeks Region to be MW Racks because that was what Week 7(Which is the latest week to be inserted) is.
Thank
Store id Region week
33 MW Racks 6
33 MW Racks 7
33 NW/NCAL Racks 5
Week 5 was NW/NCAL Racks prior to the update I ran. Week 6 was already MW Racks and so was Week 7. What I need is for every weeks Region to be MW Racks because that was what Week 7(Which is the latest week to be inserted) is.
Thank
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your right,!! Thanks for your help!!
ASKER
thanks
1. Add a column for date/time like a timestamp
2. Select the Region (or any fields needed) from top 3 rows ordered descending based on timestamp.
3. Collect that select into a subquesry and update other rows or all rows as needed.
UPDATE Table
SET Region = ( SELECT top 1 Region from table where StoreID = 1 order by timestamp DESC)
WHERE StoreID=1
HTH,
greg