:-) - 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

SQL Server Development (2000)

:-)

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-03-25 : 18:10:02

you're right Sam!

and thanks! Tara

. Made a little change

CREATE PROCEDURE proc_resv_change1(@begindate DATETIME, @enddate DATETIME ) AS

Begin
Declare @totnetchange decimal(10,2)
select
@totnetchange = sum(loss_amt)
from
claim, location_xref, loss, clmxref, location
WHERE claim.ref_location=location_xref.location_key AND
claim.claim_key=clmxref.xref_key AND
location_xref.ref_loc2=location.location_key AND
location_xref.ref_loc1=32138 AND
claim.claim_key=loss.loss_claim AND
loss.loss_type='R' AND
loss.loss_enterdate BETWEEN @begindate AND @enddate

END

Begin
SELECT
@totnetchange as totnetchanged,
@begindate as begindate,
@enddate as enddate,
clm_incdate,
clm_nbr,
clm_clmt,
MAX(location.name) AS Dealership,
MAX(clmxref.xref_totres + clmxref.xref_tottobe ) as TotRes,
MAX(clmxref.xref_totpmt - clmxref.xref_totreim) AS TotPaid,
MAX(clmxref.xref_totinc) AS TotInc,
SUM(loss_amt) AS ResvChange,
MAX(loss_enterdate) AS LastChngDate
FROM claim, location_xref, loss, clmxref, location
WHERE claim.ref_location=location_xref.location_key AND
claim.claim_key=clmxref.xref_key AND
location_xref.ref_loc2=location.location_key AND
location_xref.ref_loc1=32138 AND
claim.claim_key=loss.loss_claim AND
loss.loss_type='R' AND
loss.loss_enterdate BETWEEN @begindate AND @enddate
GROUP BY clm_nbr, clm_clmt, clm_incdate
HAVING ABS(SUM(loss_amt))>=10000
ORDER BY clm_clmt, clm_incdate

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-25 : 18:15:13

Glad to help!

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-25 : 19:02:45

Why are you using BEGIN and END when they aren't needed? Just curious. You use BEGIN and END to "Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed. BEGIN and END are control-of-flow language keywords."

For example,
IF @var1 = 0
BEGIN
SELECT @var2 = @var1
PRINT @var2
END
ELSE
PRINT @var3

You use BEGIN...END so that both statements are run as part of the IF. You don't need the BEGIN...END for the ELSE statement (in this example) because you only want to run one statement.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources