CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
TonyP
TonyP

Reputation: 5873

t-sql grouping rows between ranges

I have this query that returns Quantity based discount list

with cte([item code],price,discount,rngLow,rngHigh,id) as

    (
    select 'xxx-xxxxxxx' as [item code],l.t$pric,l.t$disc,lqanp=l.t$qanp,hqanp=h.t$qanp,id = row_number() over(partition by h.t$qanp order by h.t$qanp) from EdiCatalog l
    join ediCatalog h on l.comno=h.comno and l.t$cpls=h.t$cpls and  l.t$cuno=h.t$cuno and h.t$item=l.t$item  and l.t$qanp < h.t$qanp 

     where l.comno=@comno and l.t$cpls=@cpls and l.t$cuno=@cuno  
    )
    select * from cte

returning result set

enter image description here

How do I transform the result set to this

enter image description here

Upvotes: 0

Views: 64

Answers (2)

TonyP
TonyP

Reputation: 5873

@Frazz, Here is what I have now enter image description here

with little alteration to what you suggested..

SELECT rngLow=case rngLow 
                                        when 1 then rnglow
                                        else rnglow+1 end,rngHigh,discount,id
FROM cte a
WHERE rngHigh=(
  SELECT MIN(rngHigh)
  FROM cte b
  WHERE b.rngLow=a.rngLow
)

Upvotes: 0

Frazz
Frazz

Reputation: 3043

You can start with this:

SELECT *
FROM cte a
WHERE rngHigh=(
  SELECT MIN(rngHigh)
  FROM cte b
  WHERE b.rngLow=a.rngLow
)

Which will give you this result set:

discount    rngLow  rngHigh
40            1       9
68            9      23
73           23      47
75           47     299

Upvotes: 1

Related Questions