Solved

SQL Query Help

Posted on 2011-03-07
6
195 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 167 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 83 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 167 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Minus first query 1 36
check the deletion of SQL job on who delete/disable it 12 27
subtract 1 in Access 2003 query 7 37
Sql Query 4 0
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard 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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now