Solved

SQL Update field if NULL with value from prior record

Posted on 2014-10-30
14
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 101

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 27

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

696 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