Results 1 to 7 of 7
  1. #1
    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. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  3. #3
    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. #4
    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. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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. #6
    Join Date
    Nov 2003
    Posts
    39
    Thanks! That was helpful.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1

    code improvement

    Quote 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
  •