While loop in stored procedure

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)

While loop in stored procedure

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-08-25 : 11:52:19

Good morning! all,

Here is the current Stored procedure that generate the total claim count and total loss amount by asofdate.

Create procedure totalchange(@asofdate datetime)
as
select convert(varchar(10),@asofdate, 101) as asofdate,sum(a.clmcount) as totclaimcount, sum(a.totloss)as totincurred
from
(select count(claim_key) as clmcount,clm_nbr,claim_key,clm_status,sum(loss_amt) as totloss

from loss,claim,location_xref
where
claim.ref_location = location_xref.location_key and
claim.claim_key = loss.loss_claim and
loss.loss_date <= @asofdate and
loss_type = 'R' and
location_xref.ref_loc2 = 1069
and
clm_incdate between '20010221' and '20010731 11:59:59'

group by clm_nbr,claim_key,clm_status--, loss_amt,claim_key, clm_status
) as a

I would like to see the same result by month from asofdate 07/31/01 through 07/31/03 at one time ( I would like to see how does the value have changed for 25 months).

The output should look like:


Asofdate    Total_claim     Total_lossamount
07/01/01     2234             13000000.00
08/01/01     2400             13456789.00
09/01/01     2480             14098756.98
:
:
:
01/01/02     2800             19887273.75
:
:
07/01/03     4700             34566778.77

What would be the most efficent solution?

Any comments or suggestions would be appreciated..

Thanks
Jung

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-08-25 : 12:07:41

Read Jeff Smith's article on Detecting Runs/Streaks In Your Data, it's still on the SQLTeam home page after Using Metadata. You need a variation of it- calculating a running summary. Or check out this article http://www.sqlteam.com/item.asp?ItemID=3856, an article by Garth on calculating running totals.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources