August 21, 2014 at 11:34 am
Hi everyone
I really hope someone can help me with this...
I have a table in SQL called dbo.Sales which looks something like this
ID DATE REGION BRANCH SALES ROWNUM
1 May 14 North HS 12 1
2 Jun 14 North HS 4 2
3 Jul 14 North West HS 1 3
4 May 14 South HS 7 1
5 Jun 14 South East HS 10 2
I want to update the table so that the REGION field changes to the value determined by the maximum ROWNUM ie IDs 1 and 2 will have their REGION changed to North West (the REGION value for ID 3) and ID 4 will change to South East (the value of ID 5).
Any suggestions greatly appreciated...
BO
August 21, 2014 at 12:21 pm
ByronOne (8/21/2014)
Hi everyoneI really hope someone can help me with this...
I have a table in SQL called dbo.Sales which looks something like this
ID DATE REGION BRANCH SALES ROWNUM
1 May 14 North HS 12 1
2 Jun 14 North HS 4 2
3 Jul 14 North West HS 1 3
4 May 14 South HS 7 1
5 Jun 14 South East HS 10 2
I want to update the table so that the REGION field changes to the value determined by the maximum ROWNUM ie IDs 1 and 2 will have their REGION changed to North West (the REGION value for ID 3) and ID 4 will change to South East (the value of ID 5).
Any suggestions greatly appreciated...
BO
Your explanation is missing something. What you are explaining doesn't make any sense based on the data provided. How do you know what the ID for a given region is? And why does South become South East? North becomes North West? What is the logic or business rule here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2014 at 12:50 pm
Apologies I pasted misleading info the table should look like this
ID DATE REGION BRANCH SALES ROWNUM
1 May 14 North HS 12 1
1 Jun 14 North HS 4 2
1 Jul 14 North West HS 1 3
2 May 14 South DD 7 1
2 Jun 14 South East DD 10 2
3 May 14 East HF 7 1
3 Jun 14 East HF 10 2
I want the REGION updated for each ID to match the max RowNum for that particular iD ie ID 1 will have a REGION of North West, ID 2 will be South East and ID 3 will be East (which is of course the same but is the Max RowNum).
I hope that's a little clearer?
Thanks in advance
August 21, 2014 at 1:25 pm
Would be a lot easier if you could post ddl and sample data next time. Saves us a lot of time putting this all together. I am still not 100% I understand what you are trying to do but I think this is it.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
ID int,
NonDate varchar(10),
Region varchar(10),
Branch char(2),
Sales int,
RowNum int
)
insert #Something
select 1, 'May 14', 'North', 'HS', 12, 1 union all
select 1, 'Jun 14', 'North', 'HS', 4, 2 union all
select 1, 'Jul 14', 'North West', 'HS', 1, 3 union all
select 2, 'May 14', 'South', 'DD', 7, 1 union all
select 2, 'Jun 14', 'South East', 'DD', 10, 2 union all
select 3, 'May 14', 'East', 'HF', 7, 1 union all
select 3, 'Jun 14', 'East', 'HF', 10, 2;
with RegionNums as
(
select ID, MAX(RowNum) as RowNum
from #Something
group by ID
),
NewRegions as
(
select r.ID, s.Region as NewRegion
from RegionNums r
join #Something s on s.ID = r.ID and s.RowNum = r.RowNum
)
update s
set Region = n.NewRegion
from #Something s
join NewRegions n on s.ID = n.ID
select * from #Something
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2014 at 1:36 pm
Cheers Sean for this - greatly appreciated...
August 21, 2014 at 1:40 pm
ByronOne (8/21/2014)
Cheers Sean for this - greatly appreciated...
Make sure you understand what that code is doing before you implement it. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2014 at 1:49 pm
Will do!! 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply