[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

SQL Query Help

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
countrymeister
Asked:
countrymeister
  • 3
  • 2
3 Solutions
 
8080_DiverCommented:
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
 
8080_DiverCommented:
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
 
awking00Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
countrymeisterAuthor Commented:
All, that is a type it should be

3)
Row to be updated
00123 - Region
NULL - Group
NUll - Account
OrderDate - 12/12/2010
0
 
countrymeisterAuthor Commented:
it is a character column
0
 
8080_DiverCommented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now