Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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.
0
Fester7572
Asked:
Fester7572
  • 4
  • 4
1 Solution
 
ThomasianCommented:
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

0
 
Fester7572Author Commented:
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
0
 
ThomasianCommented:
In that case, we need to change it to a SELECT query
SELECT *
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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Fester7572Author Commented:
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

 SSRS Screenshot
0
 
ThomasianCommented:
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 ...
0
 
Fester7572Author Commented:
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

0
 
ThomasianCommented:
Sorry, t2 should be v

i.e.

v.[Unit Price], v.[Starting Date], etc.
0
 
Fester7572Author Commented:
Perfect. Thank you so much for your help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now