Fill in the gap between 2 number range

Question

Fill in the gap between 2 number range

davidku

Ten Centuries

Points: 1256
More actions
April 2, 2014 at 4:01 pm

#306045

In my SQL Server database, I have table with the following records
counter, value1
12345, 10.1
12370, 10.5
12390, 9.7
Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.
For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.
1234510.1 --> Record 1
1235010.1
1235510.1
1236010.1
1236510.1
1237010.5 --> Record 2
1237510.5
1238010.5
1238510.5
123909.7 --> Record 3
Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.
In case you are wondering why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.
Thanks for your input.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100947 More actions · Answer 1

You don't need to actually INSERT rows to use those values in a computation. Simply determine the number of "steps" between each row, and multiply the value in the first one by that.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

davidku Ten Centuries Points: 1256 More actions · Answer 2

I did try to use Record 2 - Record 1 then divide by 5 which is the input gap. However, I still need to populate each record with data from Record 1.

For example using my example

12345 --> Record 1

12350

12355

12360

12365

12370 --> Record 2

12370 - 12345 = 25

25 divide by gap 5 = 5 points

Probably due to my understanding, I don't see much value from this method. I still need to loop through each record and assign the value from Record 1. Correct me if I am wrong. Thanks.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

First of all, you should provide sample data in a consumable form if you want to get better and faster answers. I did it for you this time because you're new to this forum, but you should try to include it in the future.

Second, stop thinking about cursors as they're bad for performance as you might have noticed. SQL Server is designed to work with sets of data and not row by row.

Here's an example on how you could implement Scott's idea along with sample data and comparison test.

CREATE TABLE #Test1(

[counter]int,

value1decimal(12,4))

INSERT #Test1

SELECT 12345, 10.1 UNION ALL

SELECT 12370, 10.5 UNION ALL

SELECT 12390, 9.7

CREATE TABLE #Test2(

[counter]int,

value1decimal(12,4))

INSERT #Test2

SELECT 12345,10.1 UNION ALL --> Record 1

SELECT 12350,10.1 UNION ALL

SELECT 12355,10.1 UNION ALL

SELECT 12360,10.1 UNION ALL

SELECT 12365,10.1 UNION ALL

SELECT 12370,10.5 UNION ALL --> Record 2

SELECT 12375,10.5 UNION ALL

SELECT 12380,10.5 UNION ALL

SELECT 12385,10.5 UNION ALL

SELECT 12390,9.7 --> Record 3

DECLARE @gap int = 5;

WITH CTE AS(

SELECT *, ROW_NUMBER() OVER( ORDER BY [counter]) rn

FROM #Test1

)

SELECT SUM(ISNULL( (b.[counter] - a.[counter]) / @gap, 1) * a.value1) / SUM(ISNULL( (b.[counter] - a.[counter]) / @gap, 1))

FROM CTE a

LEFT

JOIN CTE b ON a.rn = b.rn - 1

SELECT AVG( value1)

FROM #Test2;

GO

DROP TABLE #Test1

DROP TABLE #Test2

I can't use the AVG directly, that's why the average is calculated in the traditional way.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

davidku Ten Centuries Points: 1256 More actions · Answer 4

Hi ScottPletcher, I changed my logic to calculate the number of data points between Record 1 and Record 2 and then, use a WHILE LOOP to navigate through the data points and insert it into a new table.

Managed to reduce my execution time by 30%.

Thanks for the idea.

davidku Ten Centuries Points: 1256 More actions · Answer 5

Hi Luis Cazares,

Thanks for your suggestion. I will try it out.

For the data, I will prepare it next time. Sorry for the inconvenient.

Actually, my counter is a date in seconds. I got to do this to prepare a data for stagnant point to plot a trend chart.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

I got lost with your last post. Do you mean that you really need to show all the possible values in the gap so they're available in a chart?

If you need that, you might want to look for a tally(numbers) table solution.

I'll try to work on one, but I need to be sure that's what you actually need. As Scott said, you don't want to generate all the values if you don't actually need them.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

davidku Ten Centuries Points: 1256 More actions · Answer 7

Hi Luis,

Actually the same table data set generated used by a few reports. First one is chart and then some calculations using those data points. That's why I need a new table populated with all the data set I want.

The solution I had which count the difference between points and populate seems to work for me in this case.

Thanks for your assistance and I will look into the TALLY function to broaden my knowledge.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

If you post your cursor solution, we could help you to convert it to a set based solution. It will run a lot faster if done properly and will change the way you see things. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86908 More actions · Answer 9

Y'all probably gonna think I'm a might touched in the head for coming up with something like this but it does do the work in one pass of the table and it gets the same result that Luis's query does.

SELECT SUM(n*ISNULL(v1, v2))/SUM(n)

FROM

(

-- Calculate weighted averages from the gaps

SELECT n=MAX(c)+(MAX([counter])-MIN([counter]))/5

,v1=MAX(CASE rn2%2 WHEN 0 THEN v END)

,v2=MAX(CASE rn2%2 WHEN 1 THEN v END)

FROM

(

SELECT b.[counter], a.v, a.c

,rn2=ROW_NUMBER() OVER (ORDER BY b.[counter])

FROM

(

-- Calculate islands

SELECT IslandStart=MIN([Counter]), IslandEnd=MAX([counter])

,v=AVG(value1), c=COUNT(*)

FROM

(

SELECT [counter], value1,

rn1=ROW_NUMBER() OVER (ORDER BY [counter])

FROM #Test1

) a

GROUP BY [counter]/5-rn1

) a

CROSS APPLY

(

VALUES (IslandStart-5), (IslandEnd+5)

) b ([counter])

) a

GROUP BY rn2/2

) a;

I reckon I may be over-thinking the problem a bit.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St