Solved

SQL Update field if NULL with value from prior record

Posted on 2014-10-30
14
179 Views
Last Modified: 2015-05-17
I have a table with a date field called Tech1Pickup. If Tech1Pickup is NULL, I would like the value to be updated with the last prior value, for example, the first 4 NULLs in the below example should be updated to 10/1/14, the 2nd NULL field should show 10/6/14, the next 4 should show 10/8/14.

CURRENT:
Tech1Pickup
10/1/14
NULL
NULL
NULL
NULL
10/6/14
NULL
10/8/14
NULL
NULL
NULL
NULL
10/13/14
NULL

DESIRED RESULT:
Tech1Pickup
10/1/14
10/1/14
10/1/14
10/1/14
10/1/14
10/6/14
10/6/14
10/8/14
10/8/14
10/8/14
10/8/14
10/8/14
10/13/14
10/13/14

Thank you so much!
0
Comment
Question by:Bianca
14 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413473
Which version of SQL Server?
0
 

Author Comment

by:Bianca
ID: 40413507
2005
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413637
Are the dates you want in ascending order? For example, would you ever get this?

10/1/14
NULL
NULL
NULL
NULL
9/30/14
NULL
10/8/14
NULL
NULL
NULL
NULL
10/13/14
NULL
0
 

Author Comment

by:Bianca
ID: 40413722
They are always in ascending order
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413763
CREATE TABLE [dbo].[MyTable](
	[Tech1Pickup] [date] NULL,
	[ID] [int] IDENTITY(1,1) NOT NULL)

with CalcTable as (
select M.Tech1PickUp as CurrentTech1PickUp, M.id, max(N.Tech1PickUp) as NewTech1PickUp
from dbo.MyTable M
left join dbo.MyTable N
on M.id > N.id
group by M.Tech1PickUp, M.id)

select coalesce(CurrentTech1PickUp, NewTech1PickUp) as Tech1PickUp
from CalcTable 
order by id

Open in new window

0
 

Author Comment

by:Bianca
ID: 40413780
I only showed the one field but this is in a Calendar table already with multiple other fields... I notice this starts with Create Table.. what is the alternative?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 40413787
Will the first record in your record set ever have a NULL date?
If so what value do you want used?

I assume you are trying to do this in a Delphi program

The basic algorithm would be

Open your recordset
Set LastDate = DefaultDate
Loop through the recordset
    If (Tech1Pickup IS NOT NULL) then
       Set LastDate = Tech1Pickup
    Else
         Set Tech1Pickup = LastDate
         Update record
    EndIF
    Get NEXT RECORD
End LOOP

mlmcc
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40413792
What is your current table structure?
0
 

Author Comment

by:Bianca
ID: 40413934
ID      decimal(18, 0)      Unchecked
CalendarDays      datetime      Checked
WeekDays      nvarchar(50)      Checked
Tech1Pickup      datetime      Checked
Tech2Pickup      datetime      Checked
Tech3Pickup      datetime      Checked
Tech4Pickup      datetime      Checked
OfficeWorkdays      nvarchar(50)      Checked
0
 

Author Comment

by:Bianca
ID: 40413937
For every calendar day, each tech picks up 2 times a week.. for example Tech1 picks up on Mon and Wed if the calendar day is a Tuesday, the date to show for pickup date should be the previous Mon.  I have the dates entered in, I just need to fill in the blanks..
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40413944
You will need to alter the dbo.MyTable name, but here is the code:

with CalcTable as (
select M.Tech1PickUp as CurrentTech1PickUp, M.id, max(N.Tech1PickUp) as NewTech1PickUp
from dbo.MyTable M
left join dbo.MyTable N
on M.id > N.id
group by M.Tech1PickUp, M.id)

select coalesce(CurrentTech1PickUp, NewTech1PickUp) as Tech1PickUp
from CalcTable 
order by id

Open in new window

0
 
LVL 25

Expert Comment

by:Sinisa Vuk
ID: 40415188
if your primary key is in sequential order and dates are growing as ID field .....
update YourTable set Tech1Pickup=(select max(yt.Tech1Pickup) from YourTable as yt
where yt.ID<YourTable.ID);

Open in new window

0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40781525
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

13 Experts available now in Live!

Get 1:1 Help Now