asked on

How do I use T-SQL to populate a row with previous value

I am trying to write a query that gives the unit price for each hour from 2 hours up to 20 hours.

The table I am getting the information from has the prices where the unit price changes. I have attached the sample data I am working with SampleData.xlsx

I need to populate the Null values with the previous valid value. For example Hours 5,6,7,8 and 9 should show the values for the 4 hour shift, the 11 hour shift should show the value for the 10 hour shift and the values from 13 to 20 should show the values for the 12 hour shift.

The query needs to know to check for a previous valid value as it could be different shift lengths that are used as the price break points in the live data.

Thanks in advance for any help given.

Thomasian

UPDATE t
SET
	[Unit Price] = v.[Unit Price]
	,[Minimum Quantity] = v.[Minimum Quantity]
	,[Starting Date] = v.[Starting Date] 
	,EndingDate = v.EndingDate
FROM TestTable t CROSS APPLY
	(SELECT TOP 1 *
	FROM TestTable t2
	WHERE t.[No_ of Hours]>t2.[No_ of Hours]
		AND t2.[Unit Price] IS NOT NULL
	ORDER BY t2.[No_ of Hours] DESC) v
WHERE t.[Unit Price] IS NULL

Open in new window

Fester7572

ASKER

Thanks very much. I am sorry but I did not explain myself clearly enough. I forgot to mention that I cannot change the original table data so I need to do this with something like a derived table. Will your solution still work?
Thanks again

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fester7572

ASKER

Thanks very much. One last question. It works perfectly but it produces 2 sets of columns and becuase I need to manipulate the result further I cannot seem to be able to put the results in to a selectable result.
Attached is a sample of what the result looks like plus a screenshot of what happens if I try to use this in SSRS 2005.

SampleData.xlsx

Thomasian

In the query, I used SELECT * which returns all results. You must explicitly define all the column you will need. All column that needs to be "copied" from a previous valid row must be from t2, and everything else from t

i.e.

SELECT t.Code, t.Description, t.[No of Hours], t2.[Starting Date], t2.[Ending Date]
FROM ...

Fester7572

ASKER

I get the following error message when I try to execute the query:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t2.Unit Price" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t2.Starting Date" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t2.EndingDate" could not be bound.

SELECT    t.code,t.Description,t.[No_ of Hours],t2.[Unit Price],t2.[Starting Date],t2.EndingDate
FROM         TestTable AS t CROSS APPLY
	(SELECT     TOP (1) *
	FROM TestTable t2
	WHERE t.[No_ of Hours]>=t2.[No_ of Hours]
		AND t2.[Unit Price] IS NOT NULL
	ORDER BY t2.[No_ of Hours] DESC) v

Open in new window

Thomasian

Sorry, t2 should be v

i.e.

v.[Unit Price], v.[Starting Date], etc.

Fester7572

ASKER

Perfect. Thank you so much for your help.