need help with doing running sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help with doing running sum

Author

Topic

Deeomen
Starting Member

3 Posts

Posted - 2010-06-04 : 05:34:06

hi all

very new to sql
i have one table, with 7 fields
reg_no name, tickets, date,
i need to sum the tickets
but when the sum reaches 250 i need to select the people in those records and email them.

any ideas how to do this. help need badly

thanks for any help in advance

regards
deeomen

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 05:52:16

Sum for each person or the whole sum.
Please post some sample output.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 06:00:24

It seems OP meant running sum

Madhivanan

Failing to plan is Planning to fail

Deeomen
Starting Member

3 Posts

Posted - 2010-06-04 : 06:02:38

hi

i need
reg_no name, email, contact_no, tickets_required
1 dee dfs 23 5
2 john dsf 24234 4

need to sum tickets requireds but when the sum hits 250 i need all the records that make up the 250

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 06:16:17

I still dont get it.What do you mean "all the records that make up 250"?
If I it guess right the there will be repepeating records for each name.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-04 : 06:18:48

We need table structure, sample data and wanted output as always...

No, you're never too old to Yak'n'Roll if you're too young to die.

Deeomen
Starting Member

3 Posts

Posted - 2010-06-04 : 06:26:12

table
Registration_no int
Name varchar(MAX)
Department varchar(50)
Contact_No varchar(50)
Email varchar(MAX)
Date date
No_of_TicketsReq int
Collected bit
No_of_TicketsBought int

Sample data
Registration_no Name Department Contact_No Email Date No_of_TicketsReq

1 dee edede dede ded 01/06/2010 2 null NULL
2 john test ict 3423 234324 01/06/2010 10 NULL NULL
3 dee3 efrwer ewrwe werw 01/06/2010 1 NULL NULL
4 gj gj ghj hgj 01/06/2010 5 NULL NULL

wanted output
when the running sum of no_of_ticketsreq reaches 250 i need all the records that make up that 250 by date order or by registration no

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 06:53:38

Just to give u an idea how it can be done.


declare @tbl as table(r_no int identity,ticketno int)
insert into @tbl
select 10 union all
select 23 union all
select 54 union all
select 45 union all
select 56

select * from @tbl

select t1.r_no,t1.ticketno,SUM(t2.ticketno) from @tbl t1
cross join @tbl t2
where t1.r_no>=t2.r_no
group by t1.r_no,t1.ticketno
having SUM(t2.ticketno)>100
order by r_no

I am using the threshold as 100

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources