troubleshooting Question

Identifying/Updating multiple groups of null values in a table

Avatar of jisoo411
jisoo411 asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
3 Comments1 Solution379 ViewsLast Modified:
Hello everyone,

I'm having some trouble with a particular problem and I couldn't find any topics similar to this.  I have the following table:

create table1 (
  surrogate_key bigint identity(1,1),
  category_id int,
  office_id int,
  order_id int,
  product_id int,
  seq_num int,
  date_created datetime
)

Table data looks like this:
1  10  1  100  25  1  2012-01-01 12:00:00
2  10  1  100  25  2  2012-01-01 12:00:00
3  null  1  null  25  3  2012-01-03 04:00:00
4  null  1  null  25  4  2012-01-04 02:00:00
5  11  1  101  25  5  2012-01-07 05:00:00
6  null  1 null  25  6  2012-01-08 12:00:00
7  12  2  15  30  1  2012-02-01 12:00:00  
8  null  2  null  30  2  2012-02-02 4:00:00
9  12  2  15  30  3  2012-02-03 01:00:00

The sequence number column represents the order of individual units of that product_id within that office_id in chronological order of entry.

What I'm trying to do is for rows where the category_id and order_id are missing, I want to fill it in with values from the latest row preceding it, matching on office_id and product_id.  I've been working on this for a while and am stumped on how I can identify those preceding rows to grab the values for an update.  Any help is appreciated.

Thanks,
Glen
ASKER CERTIFIED SOLUTION
Harish Varghese
Project Leader

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros