# Thread: Breakout records based on UNIT total without cursor

1. Registered User
Join Date
Nov 2003
Posts
39

## Unanswered: Breakout records based on UNIT total without cursor

I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge:

Ex:

Units REVCHG FIELD_A FIELD_B .....
3 3.00 ABCD EFGH

Needs to be converted to:

Units REVCHG FIELD_A FIELD_B .....
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH

The calc is obvious but how can I do this with a cursor but would like to do it without a cursor if possible? Anybody got an idea?

Thanks.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
What's the justification for spliting it out?
Granted, I think storing the Qty and Unit Price would be beter than Qty and Total Price, but Icertainly prefer the before to the after!

3. Registered User
Join Date
Nov 2003
Posts
39
I need to have individual records for median calculations.

EX:

REVCHG/UNIT

\$777.50
\$777.50
\$777.50
\$777.50
\$777.50
\$777.50
\$2,341.00

MEDIAN = \$777.50

OR

\$827.75
\$891.87
\$892.84
\$895.68
\$896.60
\$905.62
\$910.36
\$1,104.11

MEDIAN = \$896.14

My Cursor code works. I'm just trying to improve performance if possible. Right now I am processing about 1 mil+ records and it's taking 'bout an hour which I am sure is due to cursor usage. 9 min to process ~11,700 records from (units > 1) to 1 to 1. (10 units = 10 records @ 1 unit) so approx half the time (36 min) is cursor related.

4. Registered User
Join Date
Feb 2007
Posts
62
You can use something like this:

select
1 as units, revchg/units as revchg, ...
from
table
,
(
select top 30 row_number() over(order by somethingCheapLikePK) as num from table
) as numbers
where num<=units

You might be able to generate the inline view "numbers" more efficiently depending on your situation. Maybe use a list of numbers union all together. Anyway, as it stands you need to use some table that has enough rows to get you to the maximum units and make sure the ORDER BY has a good index for performance reasons.
As for rounding - you're on your own.

5. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Code:
```--Test data
select * into #testdata from(select
6,4665.00 union all select
1,2341.00)rev(units,revchg)

--breakout the records into single records
select id=identity(int,1,1), chu into #t1 from (
select top 100 percent revchg/units chu
from #testdata
join master..spt_values
on type='P' and number<units
order by 1
)v

-- get the median
select sum(chu)/count(*) as median
from #t1
join (
select sum(units)/2+sum(units)%2 point1, sum(units)/2+1 point2 from #testdata
)m on id between point1 and point2

drop table #testdata
drop table #t1
go
----------------------------
--Test data2
select * into #testdata from (select
1, 891.87 union all select
1, 892.84 union all select
1, 895.68 union all select
1, 896.60 union all select
1, 905.62 union all select
1, 910.36 union all select
1, 827.75 union all select -- <<Need sort>>
1,1104.11)rev(units,revchg)

--breakout the records into single records
select id=identity(int,1,1), chu into #t1 from (
select top 100 percent revchg/units chu
from #testdata
join master..spt_values
on type='P' and number<units
order by 1
)v

-- get the median
select sum(chu)/count(*) as median
from #t1
join (
select sum(units)/2+sum(units)%2 point1, sum(units)/2+1 point2 from #testdata
)m on id between point1 and point2

drop table #testdata
drop table #t1```
Last edited by pdreyer; 01-31-08 at 11:02.

6. Registered User
Join Date
Nov 2003
Posts
39

7. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369

## code improvement

Originally Posted by pdreyer
-- get the median
select sum(chu)/count(*) as median
from #t1
join (
select sum(units)/2+sum(units)%2 point1, sum(units)/2+1 point2 from #testdata
)m on id between point1 and point2
To avoid scanning the table a 2nd time use scope_identity()
Code:
```-- get the median
select sum(chu)/count(*) as median
from #t1
join (select convert(int,scope_identity()) i)p
on id between i/2+i%2 and i/2+1```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•