472,364 Members | 2,080 Online

Can an update statement be used for interpolating missing data?

Here is a small sample of data from a table of about 500 rows
(Using MSSqlserver 2000)

EntryTime Speed Gross Net
------------------ ----- -----
21:09:13.310 0 0 0
21:09:19.370 9000 NULL NULL
21:09:21.310 NULL 95 NULL
21:10:12.380 9000 NULL NULL
21:10:24.310 NULL 253 NULL
21:11:24.370 8000 NULL NULL
21:11:27.310 NULL 410 NULL
21:11:51.320 NULL 438 NULL
21:11:51.490 NULL NULL 10

After the first row, every row has only one value of the three.
I would like to replace all the NULL values with calculated
interpolations.

I can do it w/ cursors or while loops.
I could do it w/ VB (I think)

Can this be done w/ an Update statement using self joins?
What would be the best way?

The value for speed can increase or decrease over time, but can never
be < 0

Net is always less than gross, and neither can go below 0.

Thanks,
BM

Jul 23 '05 #1
9 2835
It's not really clear how you want to calculate the new values, but
perhaps you can look at CASE and COALESCE in Books Online. If this
want to calculate the new values.

Simon

Jul 23 '05 #2
I expect it will be possible with an UPDATE and a join/subquery.

UPDATE YourTable
SET speed =
(SELECT ...
FROM YourTable
WHERE entrytime < YourTable.entrytime ...)
WHERE speed IS NULL

If you need a complete solution then explain the calculation, show the
result you want and post DDL for the table. Also, it's best to post sample
data as INSERT statements so that others can more easily test out possible
solutions. That way you'll get accurate and useful answers more quickly.
See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--
Jul 23 '05 #3
Thank you greatly for the FAQ link. I learned a lot just reading it.

The table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[tblProfileTemp]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [tblProfileTemp]
GO

if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblProfileTemp]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [tblProfileTempX] (
--[Item] [int] IDENTITY (1, 1) NOT NULL ,
[Item] [int] NOT NULL ,
[EntryTime] [datetime] NULL ,
[RunTime] [numeric](12, 3) NULL ,
[Speed] [int] NULL ,
[gross] [int] NULL ,
[net] [int] NULL
) ON [PRIMARY]
END

The insert statements (the code to generate this was an education in
itself).
(10 rows should be enough) I commented out the identity contraint so
inserting w/o the column list would be possible.
INSERT INTO [tblProfileTempx] VALUES(1,'Jun 21 2005
9:09:13:310PM',0.000,0,0,0)
INSERT INTO [tblProfileTempx] VALUES(2,'Jun 21 2005
9:09:19:370PM',6.060,9000,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(3,'Jun 21 2005
9:09:21:310PM',8.000,NULL,95,NULL)
INSERT INTO [tblProfileTempx] VALUES(4,'Jun 21 2005
9:10:12:380PM',59.070,9000,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(5,'Jun 21 2005
9:10:24:310PM',71.000,NULL,253,NULL)
INSERT INTO [tblProfileTempx] VALUES(6,'Jun 21 2005
9:11:24:370PM',131.060,8000,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(7,'Jun 21 2005
9:11:27:310PM',134.000,NULL,410,NULL)
INSERT INTO [tblProfileTempx] VALUES(8,'Jun 21 2005
9:11:51:320PM',158.010,NULL,438,NULL)
INSERT INTO [tblProfileTempx] VALUES(9,'Jun 21 2005
9:11:51:490PM',158.180,0,NULL,NULL)
INSERT INTO [tblProfileTempx] VALUES(10,'Jun 21 2005
9:13:51:310PM',278.000,NULL,446,NULL)

Explanation of data:
The data represents the output of a running press. Each data element
is recorded at EntryTime. RunTime represents the time elapsed since
the start, and is expressed in seconds. Gross is number of copies
printed. Net is number of copies not rejected automatically by various
defect detectors.
Desired Result:
Example:
Gross for item 1 is 0
Gross for item 2 is null
Gross for item 3 is 95

I need to replace the null in item 2 with a value that represents the
gross count for that time, assuming a constant press speed. It will
not necesarily be constant, but the error will be slight.

The formula for that value will be:

Gross2 = Gross1 + ((Gross3 - Gross1) * ((RunTime2-RunTime1) / (RunTime3
- RunTime1)))

Similar interpolations will be calculated for Net and Speed.

It gets harder where there are two or more nulls between known values.

I was working along the lines of:

Update t1
Set t1.Gross = t0.Gross + ((t2.Gross = t0.Gross) *
((t1.runtime-t0.runtime)/(t2.runtime-t0.runtime)))

from tblProfileTempX t1 inner join tblProfileTempX t0 on t0.item =
t1.item
inner join tblProfileTempX t2 on t2.item = t0.item

where t1.gross is null
and t0.EntryTime = (select Max(EntryTime) from tblProfileTempX
where gross is not null and item < t1.item)
and t2.EntryTime = (select Min(EntryTime) from tblProfileTempX
where gross is not null and item > t1.item)

I've reduce the errors to the following:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'and'.

Thanks in advance for your time and effort, and apologies for the group
etiquette breach,

Regards,
BM

David Portas wrote:
I expect it will be possible with an UPDATE and a join/subquery.

UPDATE YourTable
SET speed =
(SELECT ...
FROM YourTable
WHERE entrytime < YourTable.entrytime ...)
WHERE speed IS NULL

If you need a complete solution then explain the calculation, show the
result you want and post DDL for the table. Also, it's best to post sample
data as INSERT statements so that others can more easily test out possible
solutions. That way you'll get accurate and useful answers more quickly.
See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--

Jul 23 '05 #4
Groan:
Tired eyes mistook an = sign for a - sign in line two.
With that fixed, it runs, but:

0 rwos affected

Jul 23 '05 #5

Vorpal (br****@gmail.com) writes:
Here is a small sample of data from a table of about 500 rows
(Using MSSqlserver 2000)

EntryTime Speed Gross Net
------------------ ----- -----
21:09:13.310 0 0 0
21:09:19.370 9000 NULL NULL
21:09:21.310 NULL 95 NULL
21:10:12.380 9000 NULL NULL
21:10:24.310 NULL 253 NULL
21:11:24.370 8000 NULL NULL
21:11:27.310 NULL 410 NULL
21:11:51.320 NULL 438 NULL
21:11:51.490 NULL NULL 10

After the first row, every row has only one value of the three.
I would like to replace all the NULL values with calculated
interpolations.

I can do it w/ cursors or while loops.
I could do it w/ VB (I think)

Can this be done w/ an Update statement using self joins?

Not "an", but a couple. In the below script I get the data into a temp
table with an IDENTITY column, which has a consecutive number. I then
find the next and previous row with a non-NULL value for speed, for those
rows that have a NULL value. Once I have these pointers I can make the
interpolation. There is no extrapolation for the NULL values at the end.

The number of UPDATE statements could be reduced if you have three
sets of pointer columns, but I'm not sure that is worth the pain.

The script does not include handling of Net. That is left as an exercise

CREATE TABLE tbl (entrytime datetime NOT NULL PRIMARY KEY,
speed int NULL,
gross int NULL,
net int NULL)
go
INSERT tbl(entrytime, speed, gross, net)
SELECT '21:09:13.310', 0, 0, 0 UNION
SELECT '21:09:19.370', 9000, NULL, NULL UNION
SELECT '21:09:21.310', NULL, 95, NULL UNION
SELECT '21:10:12.380', 9000, NULL, NULL UNION
SELECT '21:10:24.310', NULL, 253, NULL UNION
SELECT '21:11:24.370', 8000, NULL, NULL UNION
SELECT '21:11:27.310', NULL, 410, NULL UNION
SELECT '21:11:51.320', NULL, 438, NULL UNION
SELECT '21:11:51.490', NULL, NULL, 10
go
CREATE TABLE #temp (ident int IDENTITY UNIQUE,
entrytime datetime NOT NULL PRIMARY KEY,
speed int NULL,
gross int NULL,
net int NULL,
prevval int NULL,
nextval int NULL)

INSERT #temp(entrytime, speed, gross, net)
SELECT entrytime, speed, gross, net
FROM tbl
ORDER BY entrytime

UPDATE #temp
SET prevval = (SELECT MAX(t2.ident)
FROM #temp t2
WHERE t2.ident < t.ident
AND t2.speed IS NOT NULL)
FROM #temp t
WHERE t.speed IS NULL

UPDATE #temp
SET nextval = (SELECT MIN(t2.ident)
FROM #temp t2
WHERE t2.ident > t.ident
AND t2.speed IS NOT NULL)
FROM #temp t
WHERE t.speed IS NULL

UPDATE t
SET speed = p.speed +
1E0 * (n.speed - p.speed) * (t.ident - t.prevval) /
(t.nextval - t.prevval)
FROM #temp t
JOIN #temp p ON t.prevval = p.ident
JOIN #temp n ON t.nextval = n.ident
WHERE t.speed IS NULL

UPDATE #temp
SET prevval = NULL, nextval = NULL
UPDATE #temp
SET prevval = (SELECT MAX(t2.ident)
FROM #temp t2
WHERE t2.ident < t.ident
AND t2.gross IS NOT NULL)
FROM #temp t
WHERE t.gross IS NULL

UPDATE #temp
SET nextval = (SELECT MIN(t2.ident)
FROM #temp t2
WHERE t2.ident > t.ident
AND t2.gross IS NOT NULL)
FROM #temp t
WHERE t.gross IS NULL

UPDATE t
SET gross = p.gross +
1E0 * (n.gross - p.gross) * (t.ident - t.prevval) /
(t.nextval - t.prevval)
FROM #temp t
JOIN #temp p ON t.prevval = p.ident
JOIN #temp n ON t.nextval = n.ident
WHERE t.gross IS NULL

UPDATE #temp
SET prevval = NULL, nextval = NULL

go
UPDATE tbl
SET speed = t.speed,
gross = t.gross,
net = t.net
FROM tbl
JOIN #temp t ON tbl.entrytime = t.entrytime
go
SELECT * FROM #temp
SELECT * FROM tbl ORDER BY entrytime
go
DROP TABLE tbl
DROP TABLE #temp

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Erland Sommarskog (es****@sommarskog.se) writes:
UPDATE t
SET speed = p.speed +
1E0 * (n.speed - p.speed) * (t.ident - t.prevval) /
(t.nextval - t.prevval)
FROM #temp t
JOIN #temp p ON t.prevval = p.ident
JOIN #temp n ON t.nextval = n.ident
WHERE t.speed IS NULL

So I did not consider time. This might be better:

UPDATE t
SET speed = p.speed +
1E0 * (n.speed - p.speed) *
datediff(ms, p.entrytime, t.entrytime) /
datediff(ms, p.entrytime, n.entrytime)
FROM #temp t
JOIN #temp p ON t.prevval = p.ident
JOIN #temp n ON t.nextval = n.ident
WHERE t.speed IS NULL

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
I have settled on the following for now:
1. First, if the final value is a null, set it to the maximum value
for that field.
This is necessary so that the intervening values can be calculated.
This update does that:
update t1
set Gross = t0.gross
from tblProfiletemp t1, tblprofiletemp t0
where t1.gross is null
and t1.entrytime = (select max(entrytime) from tblprofiletemp)
and t0.gross = (select max(gross) from tblprofiletemp)

Once that is done, then the following update statement fills in all
intervening values with the correct value:
Update t1
set Gross=t0.Gross + ((t2.Gross - t0.Gross) *
((t1.runtime-t0.runtime)/(t2.runtime-t0.runtime)))
from tblProfiletemp t0 ,tblProfiletemp t1,tblProfiletemp t2
where t1.gross is null
and t0.entrytime = (select Max(EntryTime) from tblProfiletemp where
item < t1.item and gross is not null)
and t2.EntryTime = (select Min(EntryTime) from tblProfiletemp where
item > t1.item and gross is not null)

The reason I could not get the update statement to work before is that
I was erroneously creating self joins.

The above will work properly on the columns where the data always
increases. It may need some modification for the speed columns.

Thanks for all the input.
BM

Jul 23 '05 #8
Don't rely on the IDENTITY column to drive the sequence. IDENTITY is
only supposed to be an arbitrary key. EntryTime should be a better way
to do it:

UPDATE tblProfileTemp
SET gross =
(SELECT T0.gross +
((T2.gross - T0.gross) *
((tblProfileTemp.runtime-T0.runtime)/(T2.runtime-T0.runtime)))
FROM tblProfileTemp AS T0,
tblProfileTemp AS T2
WHERE T0.entrytime =
(SELECT MAX(entrytime)
FROM tblProfileTemp AS T
WHERE entrytime < tblProfileTemp.entrytime
AND gross IS NOT NULL)
AND T2.entrytime =
(SELECT MIN(entrytime)
FROM tblProfileTemp AS T
WHERE entrytime > tblProfileTemp.entrytime
AND gross IS NOT NULL))
WHERE gross IS NULL

Thanks for posting the DDL and sample. It helped.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #9
For the speed table, I took a different approach.
Once the values for gross copy count have been inserted, then the speed
can be calculated backwards as
Speed = (Gross - Gross0)/(RunTime - RunTime0).

I changed the runtime so it is recorded in seconds, rather than
minutes, and round the speed value to the neares 100.
These two changes give a smoother graph.

Where a speed needs to be calculated from two gross values recorded
very short times apart, then the speed sometimes appears anomalously
high or low. (usually high).

The results of this are now in testing, and I'll see what feedback from
the users is before making further changes.

Thanks tremendously to all who helped.

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

 8 by: Lauren Quantrell | last post by: In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID: UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON... 8 by: Jan van Veldhuizen | last post by: The UPDATE table FROM syntax is not supported by Oracle. I am looking for a syntax that is understood by both Oracle and SqlServer. Example: Table1: id name city ... 17 by: kalamos | last post by: This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by... 16 by: robert | last post by: been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the... 5 by: Klemens | last post by: I get SQL30090 reason 18 by trying to do an insert in a federated table and an update in a local table in one transaction Do I have to change some settings to get done or ist this not possible by... 5 by: Alastair Anderson | last post by: I have created a very simple form with which I would like to update a single value in a single row of a database as a proof of concept. The relevant parts of the form are a DBWebTextBox (which... 5 by: wpellett | last post by: I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table... 2 by: Miro | last post by: I will ask the question first then fumble thru trying to explain myself so i dont waste too much of your time. Question / Statement - Every mdb table needs a PrimaryKey ( or maybe an index - i... 16 by: ARC | last post by: Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for... 0 by: antdb | last post by: Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and... 0 by: Arjunsri | last post by: I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection... 1 by: Matthew3360 | last post by: Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a... 0 by: Carina712 | last post by: Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important... 0 by: BLUEPANDA | last post by: At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also... 2 by: Ricardo de Mila | last post by: Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what... 1 by: Johno34 | last post by: I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If... 1 by: ezappsrUS | last post by: Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the... 0 by: jack2019x | last post by: hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.