Solved

SQL Update field if NULL with value from prior record

Posted on 2014-10-30
14
185 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tools to scan a SQL server's problem 14 26
SQL USE DATABASE VARIABLE 5 27
changing page verifacation 1 26
SQL Recursion schedule 13 14
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

839 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