Range Group Summary of particular column

Question

Range Group Summary of particular column

tlrsekar 23727

SSC Veteran

Points: 262
More actions
June 3, 2014 at 12:07 am

#308754

Hi All
I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the runningsummary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000
is there any running summary kind of function which can start sum record with in range of 0-1000
For Ex. My Data stored like this
Dept People Count
CSE 200
IT 250
EEE 312
ECE 214
MEC 337
Batch Grouping
Dept People Count BatchSum
CSE 200 200
IT 250 450
EEE 312 762
ECE 214 976
MEC 337 337(Note here since its crossing 1000, its resetting and starting summary)
I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way. Kindly reply
Rajasekaran T

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

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

How are you ordering the data? It's not clear from the sample.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

tlrsekar 23727 SSC Veteran Points: 262 More actions · Answer 2

Hi

Thanks for reply, Attached Image, The Last column is my target output which is required.

In image in For MEC department starts with fresh summary since previous summary + current value is cross 1000

Hope you are clear now

Rajasekaran

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

tlrsekar 23727 (6/3/2014)
Hi
Thanks for reply, Attached Image, The Last column is my target output which is required.
In image in For MEC department starts with fresh summary since previous summary + current value is cross 1000
Hope you are clear now
Rajasekaran

Well, it would be clear, had you answered my question.

The numbers which you are displaying may vary on any given day unless you include an ORDER BY in your query. Are you saying that you do not care in which order the results (and therefore the running totals) are displayed?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

tlrsekar 23727 SSC Veteran Points: 262 More actions · Answer 4

As of now order is not my concern, it can be of any order

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

tlrsekar 23727 (6/3/2014)
As of now order is not my concern, it can be of any order

You could use modulus/remainder of the number

😎

;WITH TN(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMBERS(N) AS (SELECT TOP (2500) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM TN T1,TN T2,TN T3,TN T4,TN T5,TN T6,TN T7)

SELECT

NM.N

,NM.N % 1000 AS GR_SEQ

FROM NUMBERS NM

tlrsekar 23727 SSC Veteran Points: 262 More actions · Answer 6

Thanks for the reply

But I was looking for range cumulative summary of a column which will start the summary after some range

not on sequence numbering in Range. Pls help

Rajasekaran

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

Eirikur Eiriksson (6/3/2014)
tlrsekar 23727 (6/3/2014)
As of now order is not my concern, it can be of any order
You could use modulus/remainder of the number

Eirikur... it's a self-reseting running total. Do your lag thing for this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VIG SSC-Addicted Points: 488 More actions · Answer 8

;with q1 as

(

select d,c

from (values('cse', 200),('it', 250),('eee', 312),('ece', 214),('mec', 337)) as t(d,c)

),

q2 as

( select ROW_NUMBER()over (order by (select null)) rn, d,c

from q1

),

q3 as

(

select 1 as rownum,d,c, c as sumq

from q2 where rn=1

union all

select prv.rownum +1,cur.d,cur.c,case when prv.sumq + cur.c>1000 then cur.c else prv.sumq + cur.c end

from q3 as prv join q2 as cur on cur.rn=prv.rownum +1

)

select *

from q3