get values at difffrent periods

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

SQL Server Development (2000)

get values at difffrent periods

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2003-04-05 : 09:25:58

MyTable
dateLoaned curPrincipal dblRate curInterest
1/01/2003 100 .10 10
1/13/2003 200 .10 20
2/01/2003 150 .05 7.5
2/28/2003 300 .20 60
3/18/2003 100 .10 10
3/31/2003 200 .20 40

I want to get the sum (as sum1) of curInterest from 1/1/2003 to 2/28/2003 and the sum (as sum2) of curInterest from 1/1/2003 to 3/31/2003. How do I write the query?

Thanks for your help.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-05 : 10:00:54

select sum1 = sum(case when dateLoaned between '20030101' and '20030228' then curInterest else 0 end) ,
sum2 = sum(case when dateLoaned between '20030101' and '20030331' then curInterest else 0 end)
from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-05 : 10:59:14

Your requirement states "to 2/28/2003", you might want to change the between in Nigel's solution to:

dateLoaned between '20030101' and '20030301'

if you want to include entries that occur on Feb 28th (or 29th on leap year)

Sam

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-04-06 : 15:24:00

dateLoaned >= '20030101' and dateLoaned < '20030301'

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-06 : 17:27:04

I was wondering if anyone would note the 1 tick in '20030301' that would match the between criteria.