Solved

SQL Update field if NULL with value from prior record

Posted on 2014-10-30
14
182 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

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.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 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

15 Experts available now in Live!

Get 1:1 Help Now