Solved

SQL Server Query Syntax

Posted on 2011-02-21
14
364 Views
Last Modified: 2012-05-11
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!!
0
Comment
Question by:johnnyaction
  • 8
  • 4
  • 2
14 Comments
 
LVL 1

Expert Comment

by:gparrish
ID: 34945844
This is just a stab, but could you do this, how predictive is your data?

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
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34945868
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?

0
 
LVL 1

Expert Comment

by:gparrish
ID: 34945935
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


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:johnnyaction
ID: 34946045
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
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34946144
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]

Open in new window

tested with your sample data.
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
*/

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 34946167
But if you update the previous weeks data, you lost the history. Are you fine with that?
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34946191
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
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34946337
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
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34946359
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?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34946378
Can you post the not working data?
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34946456
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
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34946711
I got the Region updated for your latest data also. Are you running exactly as I ran?
declare @table table (StoreID int,    Region varchar(50),[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)
insert @table values(33 ,             'MW Racks'      ,       6)
insert @table values(33 ,               'MW Racks'      ,       7)
insert @table values(33 ,               'NW/NCAL Racks'      ,   5)

;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
33	MW Racks	6
33	MW Racks	7
33	MW Racks	5
*/

Open in new window

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 34947162
Your right,!!  Thanks for your help!!
0
 
LVL 1

Author Closing Comment

by:johnnyaction
ID: 34947163
thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL server 2008 and after encryption method 32 50
SSRS Subscription jobs disabled, yet still running 4 36
Sql Stored Procedure field variable 17 31
Webservices in T-SQL 3 31
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question