[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Query Help

Posted on 2011-03-07
6
Medium Priority
?
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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