Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Query Help

Posted on 2011-03-07
6
Medium Priority
?
205 Views
Last Modified: 2012-05-11
I have a table OrderHistory

RegionID
Group
Account
orderDate
CreateDate
CreateUserID
LastUpdatedDate
LastUserID

I need to update this table where the two columns
Group
Account

Where the Group IS NULL
with values from the same table having a non null value for the same RegionID, closest to the OrderDate

Example
Current Rows in the table
1)
00123 - region
CENTRAL - Group
A231 - Account
12/10/2010 - Order date
2)
00123 - region
CENTRAL1 - Group
A2312 - Account
12/15/2010 - Order date

3)
Row to be updated
0123 - Region
NULL - Group
NUll - Account
OrderDate - 12/12/2010
0
Comment
Question by:countrymeister
  • 3
  • 2
6 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 668 total points
ID: 35060681
I need to update this table where the two columns
Group
Account


Where they what?

Given your sample data:
Do you want the row to be updated to get the Group and Account data from the row that is closest to but earlier than the Order Date or the one that is just closest to the order date?  Also, if there are 2 rows that are each 2 days prior to the row to be updated but they have different Group and Account data, which do you want to use?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35060710
Also, your sample data indicates that the Region is a zero-left-filled value (e.g. 00123) however, you seem to be wanting to match as though it is an integer.  I.e. you appear to be wanting to update a Region 0123 to a Region 00123 row's values.

Is that column an integer or a character column?
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 332 total points
ID: 35060773
Can we assume that the region for row 3 should be 00123 (0123 being a typo) and that the group value should be updated to CENTRAL and the account value to A231 from row 1 since row 1's order date of 12/10/2010 is closer to row 3's order date of 12/12/2010 than that of row 2's order date of 12/15/2010?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 1

Author Comment

by:countrymeister
ID: 35061560
All, that is a type it should be

3)
Row to be updated
00123 - Region
NULL - Group
NUll - Account
OrderDate - 12/12/2010
0
 
LVL 1

Author Comment

by:countrymeister
ID: 35061572
it is a character column
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 668 total points
ID: 35061902
Okay, so what about the other questions?  

Should the data used to update the row be the closest Order Date prior to the one being updated?
If 2 rows have the same Order Date but different Group/Account information and are "the closest" Order Dates, how is the decision to be made?

0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

608 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