Solved

SQL Query Help

Posted on 2011-03-07
6
194 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 31

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

19 Experts available now in Live!

Get 1:1 Help Now