need help with doing a sum and selecting records

Question

need help with doing a sum and selecting records

deeomen

Newbie

Points: 7
More actions
June 4, 2010 at 3:47 am

#233329

Posted - 06/04/2010 : 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
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
regards
deeomen

Viewing 4 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table def and sample data in a ready to use format so we can focus on providing a tested solution instead of formatting the sample data.

Furthermore, please include your expected result based on the sample data you provided including the code you've tried so far.

If you need a guideline on how to post the data please read and follow the first link in my signature.

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]

WayneS SSC Guru Points: 95461 More actions · Answer 2

deeomen (6/4/2010)
Posted - 06/04/2010 : 05:34:06
--------------------------------------------------------------------------------
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

I suggest that you read this article by MVP Jeff Moden[/url] for handling running totals.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 3

deeomen (6/4/2010)
Posted - 06/04/2010 : 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
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
regards
deeomen

I'm curious... what did you end up doing on 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)