sql server programming: Hello,

I have the following syntax in my routine where I am trying to select based
on criteria to match records and then set the variables to the original
amounts - but it appears to be going to the last record and putting that
amount in the variable - my question is - am I missing some parenthesis or
something to make it stop and read those amounts into the variables before it
goes on to the next record?


Else IF @Trans_Type = (@Retruned_Check)
Begin
Select @Original_Pymt_Amt = Pymt_Recvd,@Original_FF_Amt = Stage_Rec_FF
From Stage_Fire_Fee
Where Pay_Dist_ID = @PayID
And Trans_Type = 'Payment' or Trans_type = 'Cash With App'

SET @Recvd_Premium = (abs(@Original_Pymt_Amt) - @Original_FF_Amt)
SET @Recvd_Premium = (@Recvd_Premium * - 1)
SET @Recvd_Fire_Fee = (@Original_FF_Amt * -1)
SET @Last_Fire_Fee = abs(@Recvd_Fire_Fee)

End

I do an update statement to the table after each transaction type before it
goes onto the next set of records, yet if I have just one related set of
records (with a set of 10 records or so) in the table, the above works, once
I put multiple sets of records in the table, the above fails to work.
Hope I'm explaining this well, I realize you can't look at my whole program,
I'm just wondering if anything jumps out at you here as to why it would be

CREATE TABLE [STAGE_FIRE_FEE] (
[PolicyKeyID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Policy_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Policy_Date_Time] [datetime] NULL ,
[Billed_Premium] [decimal](10, 2) NULL ,
[Billed_Fire_Fee] [decimal](10, 2) NULL ,
[Pymt_Recvd] [decimal](10, 2) NULL ,
[Trans_Type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TransCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pay_Dist_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Stage_Premium_Bal] [decimal](10, 2) NULL ,
[Stage_Fire_Fee_Bal] [decimal](10, 2) NULL ,
[Stage_Premium_Recvd] [decimal](10, 2) NULL ,
[Stage_Fire_Fee_Recvd] [decimal](10, 2) NULL ,
[Stage_Last_Fire_Fee] [decimal](10, 2) NULL ,
[Stage_Last_FF_RetCK] [decimal](10, 2) NULL ,
[Stage_Pay_Dist] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Orig_Pymt] [decimal](10, 2) NULL ,
[Orig_FF] [decimal](10, 2) NULL ,
[Stage_Ret_Ck_FF] [decimal](10, 2) NULL
) ON [PRIMARY]
GO

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','1/25/2001 4:28:54
PM','775','2.66',NULL,'New','1','Billed Premium',Null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','1/25/2001 4:28:58 PM',null,null,'-194','Cash With
App','8','Payment','35515')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','2/6/2001 11:32:08 AM',null,null,'194','Returned
Check','B','Payment','35515')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','3/23/2001 2:47:08
PM',null,null,'388.83','Payment','7','Payment','42858')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','3/30/2001 8:32:08
AM','-626','-2.15',null,'Cancellation','3','Billed Premium',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','3/30/2001 8:32:013 AM',null,null,'239.32','Cancel
Disbursement','Y','Payment',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','4/4/2001 3:17:58
PM','626','2.15',null,'Change','2','Billed Premium',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','4/6/2001 3:47:21
PM',null,null,'-239.32','Payment','7','Payment','44561')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','4/13/2001 11:32:08 AM',null,null,'388.83','Returned
Check','B','Payment','42858')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','5/2/2001 11:32:08
AM',null,null,'-484','Payment','7','Payment','47645')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0001215','10/4/2001 11:32:08
AM','-236','-0.81',null,'Cancellation','3','Billed Premium',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0003892','4/22/2003 11:51:29
AM','2907','15.28',null,'New','1','Billed Premium',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0003892','4/22/2003 11:51:33 AM',null,null,'-3100','Cash With
App','8','Payment','234901')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0003892','4/22/2003 4:16:29
PM',null,null,'177.72','Disbursement','G','Payment',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0003892','2/16/2004 11:51:29
AM','3152','16.01',null,'Renewal','6','Billed Premium',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0003892','3/17/2004 11:51:29
AM',null,null,'-3168.01','Payment','7','Payment','388303')

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0003892','2/19/2005 11:51:29
AM','3281','16.78',null,'Renewal','6','Billed Premium',null)

Insert Into Stage_Fire_Fee
(Policy_Number,Policy_Date_Time,Billed_Premium,Billed_Fire_Fee,Pymt_Recvd,Trans_Type,TransCode,Type,Pay_Dist_ID)
Values ('BP 0003892','3/19/2005 11:51:29
AM',null,null,'-3297.78','Payment','7','Payment','573275')













[quoted text, click to view]