Running Total and then every 20

Question

Post reply

Running Total and then every 20

brad.mason5

Hall of Fame

Points: 3299
More actions
June 21, 2011 at 9:35 pm

#240248

Hello SQL Server Community,
I am trying to come up with a sql statement that will return only the records that next 20 of the current running total. I am trying Recursive CTE but am having no luck.
The data set I generated. I first created a temp table and now have the running total. The running total is the number of weeks between term start date and term end date.
RowNumRunningTotalUniqueStudentIDStudentLevelTermStartDateTermEndDate
17123Undergraduate Level12/4/071/21/08
212123Undergraduate Level1/22/082/25/08
317123Undergraduate Level2/26/083/31/08
422123Undergraduate Level4/1/085/5/08This record would be selected (at 20)
527123Undergraduate Level5/6/086/9/08
632123Undergraduate Level6/10/087/14/08
737123Undergraduate Level7/29/089/1/08
842123Undergraduate Level9/2/0810/6/08This record would be selected (first record after adding 20)
947123Undergraduate Level10/7/0811/10/08
1052123Undergraduate Level11/11/0812/15/08
1159123Undergraduate Level12/16/082/2/09
1264123Undergraduate Level2/3/093/9/09This record would be selected(first record after adding 20)
1369123Undergraduate Level5/19/096/22/09
1474123Undergraduate Level7/21/098/24/09
1579123Undergraduate Level8/25/099/28/09
1684123Undergraduate Level9/29/0911/2/09This record would be selected(first record after adding 20)
1789123Undergraduate Level11/3/0912/7/09
1896123Undergraduate Level12/8/091/25/10
Row Number 4, 8, 12, 16 would be selected. The trouble I am haveing is keeping the next min(running total) and then appending 20 to it to find the next min(running total >=20).
THank you for you time and help if possible. I will continue to work on this.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

declare @table table (

Does this work for you

id int ,

runningtot int ,

studid int ,

chardata varchar(25))

insert into @table

select 1,7,123,'Undergraduate' union all

select 2,12,123,'Undergraduate' union all

select 3,17,123,'Undergraduate' union all

select 4,22,123,'Undergraduate' union all

select 5,27,123,'Undergraduate' union all

select 6,32,123,'Undergraduate' union all

select 7,37,123,'Undergraduate' union all

select 8,42,123,'Undergraduate' union all

select 9,47,123,'Undergraduate' union all

select 10,52,123,'Undergraduate' union all

select 11,59,123,'Undergraduate' union all

select 12,64,123,'Undergraduate' union all

select 13,69,123,'Undergraduate' union all

select 14,74,123,'Undergraduate' union all

select 15,79,123,'Undergraduate' union all

select 16,84,123,'Undergraduate' union all

select 17,89,123,'Undergraduate' union all

select 18,96,123,'Undergraduate'

;

with cte as (

select row_number () over( PARTITION BY runningtot/20 order by runningtot/20 , runningtot%20 ) as minmod,id from @table

where runningtot/20 >= 1 )

select a.* from @table a

join cte c

on c.id = a.id

and c.minmod = 1

Jayanth Kurup[/url]

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 2

Thank you very much Right There. Your solution does work for the data set I provided, however I added two additional records that cause this to not work. I ended up using a while loop and looping through each record for the student, that was the only way I could think of to get the Prev Record + 20. I know this solution is not optimal and would love to get it set based. I will try and work with what you provided and see if there is another way. If you have another idea I will be appreciative.

Thanks again for your time.

declare @table table (

--Does this work for you

id int ,

runningtot int ,

studid int ,

chardata varchar(25))

insert into @table

select 1,7,123,'Undergraduate' union all

select 2,12,123,'Undergraduate' union all

select 3,17,123,'Undergraduate' union all

select 4,22,123,'Undergraduate' union all

select 5,27,123,'Undergraduate' union all

select 6,32,123,'Undergraduate' union all

select 7,37,123,'Undergraduate' union all

select 8,42,123,'Undergraduate' union all

select 9,47,123,'Undergraduate' union all

select 10,52,123,'Undergraduate' union all

select 11,59,123,'Undergraduate' union all

select 12,64,123,'Undergraduate' union all

select 13,69,123,'Undergraduate' union all

select 14,74,123,'Undergraduate' union all

select 15,79,123,'Undergraduate' union all

select 16,84,123,'Undergraduate' union all

select 17,89,123,'Undergraduate' union all

select 18,96,123,'Undergraduate' union all

select 19,101,123,'Undergraduate' union all

select 20,106,123,'Undergraduate' --This one should be selected as 84+20 = 104, (106 is next min num)

;

with cte as (

select row_number () over( PARTITION BY runningtot/20 order by runningtot/20 , runningtot%20 ) as minmod,id from @table

where runningtot/20 >= 1 )

select a.* from @table a

join cte c

on c.id = a.id

and c.minmod = 1

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

Looks like I misunderstood the question , sorry about that. I assumed your working looking for the running total with a value closest to intervals of 20.

Let me see if i can do a better job and get back to you.

Jayanth Kurup[/url]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

How is your original data table populated?

Have you seen this article yet? http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

LutzM SSC Guru Points: 107049 More actions · Answer 5

Following opc.three's line of thought:

Why don't you populate the "flag" together with the running total calculation?

Since the "flag" value depends on the previous "flag" value, it's just another running total scenario.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]