SQL Query help

[InsomniaCalls]

Hi everyone,

I wonder if you can help me out.

I have a MS SQL table with 2 columns: Amount (int) and Date (date - obviously ).
There may be multiple rows in the table which have the same value within the date column.

I need to perform a SELECT SUM(Amount) for each value within the Date column between two date values.

E.G.

Start date is 01/02/2009.
End date is 07/02/2009.

The values in the table may look like:

3000 31/01/2009
1000 01/02/2009
5000 01/02/2009
2500 02/02/2009
3500 02/02/2009
1100 03/02/2009
6000 04/02/2009
5500 05/02/2009
2700 05/02/2009
1200 06/02/2009
9500 07/02/2009
8500 08/02/2009

I want to look at data between 01/02/2009 and 07/02/2009.
I then want to find the SUM of all amounts whos date value matches the start date, then loop through each date until the end date.

I'm not sure if it's possible or if I might be better getting all the data between the two dates then manipulating it within C#.

Any pointers would be great.

Thanks

Si

 

[SimonCHere]

Select sum(amount), date
from table
where date between convert(smalldatetime,'01/02/2009',103) and convert(smalldatetime,'07/02/2009',103)
group by date

if that's what you mean.
It will not pick up any dates between the 2 dates that don't have [amount] values

 

[InsomniaCalls]

Great - thanks!

I ended up with:

SELECT SUM(Amount), Date from Table where Date between @StartDate and @EndDate group by Date

In a stored procedure.

 

[SimonCHere]

Fair enough.
Be aware that the between take into account the timestamp if there is one.
So if your table had short dates with a time of 00:00 and you @Startdate has a time stamp of 00:01 you will be missing some records.

Simon