Group By and Dates - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Group By and Dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-03 : 08:30:11

Gene writes "Hi all,

I could use some of your expertise
I am need of accomplishing the following for a stored Procedure I am trying to create.

I have a set of data with the following fields

MemberID
TransDate
Amount

I need to do a Sum(Amount)
For a date range @StartDate >= TransDate
and @EndDate <= TransDate

WITHOUT breaking on each Date. Every time I try to do this I get a break per date in the date range. The final solution is trying to put and order and the SUM(amount) in descending order.

Thanks in advance for all of your help

Gene"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-03 : 08:42:43

show us some sample input data...(insert statements and DDL would be VERY useful)
and matching sample expected outputs...and this might progress faster

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 09:02:31

What exactly are you trying to sum? amounts for each member during the date range?

that would be something like:


Declare @dRangeBegin datetime,
	@dRangeEnd datetime

Set @dRangeBegin = '8/1/2004'
Set @dRangeEnd = '9/1/2004'

Select 
	memberId,
	Amount = sum(Amount)
From myTable
Where transdate between @dRangeBegin and @dRangeEnd
Group By memberId
Order By Amount Desc

The part in bold is the part that you need to add to actually group records where column(s) are the same...

Corey

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-03 : 16:21:27

Don't GROUP BY TransDate
( As Seventhnight said iow )

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources