El interes diario

Se realizo el Stored Procedure del interés diario el cual fue prácticamente copiar y pegar del examen ya que Vinicio tuvo una nota perfecta en esta parte simplemente se le hicieron algunos ajustes para que fuera mas eficiente y para que funcionara con las nuevas especificaciones del profesor:

create PROCEDURE [dbo].[SMSP_DailyInterest]
-- Add the parameters for the stored procedure here
@ProcDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    BEGIN TRY
DECLARE @DailyInterests TABLE (ID INT PRIMARY KEY IDENTITY(1,1), FK_SavingsAccount INT NOT NULL, Amount MONEY);
DECLARE @PunishedAccounts TABLE(ID INT PRIMARY KEY IDENTITY(1,1),FK_SavingsAccount INT NOT NULL, MinBalanceByMonth MONEY, PenaltyFee MONEY);

INSERT INTO @DailyInterests(FK_SavingsAccount,Amount)
SELECT dbo.SMFN_CalculateInterest(SA.Balance, SAT.Interest_Rate),SA.Balance FROM SavingsAccount SA INNER JOIN SavingsAccountType SAT ON SAT.ID = SA.Fk_SavingAccountType
UPDATE SavingsAccount SET Balance = SA.MinBalance FROM SavingsAccount SA INNER JOIN MinBalanceByMonth MBPM ON SA.ID = MBPM.FK_SavingAccount
WHERE SA.Balance < MBPM.MinBalance

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @ProcDate = dbo.SMFN_Deadline()
BEGIN
INSERT INTO BalanceMov(FK_SavingAccount,FK_MovType,Date,Amount)
SELECT SA.ID,0,@ProcDate,SA.MounthAccountInterest FROM SavingsAccount SA
UPDATE SavingsAccount SET Balance = Balance + SA.MounthAccountInterest
FROM SavingsAccount SA INNER JOIN BalanceMov BM ON SA.ID = BM.FK_SavingAccount
INSERT INTO InterestMov(FK_SavingAccount,FK_InterestMovType,Date,Amount)
SELECT SA.ID, 1, @ProcDate, SA.MounthAccountInterest FROM SavingsAccount SA
UPDATE SavingsAccount SET MounthAccountInterest = 0 FROM SavingsAccount SA
INNER JOIN InterestMov IM ON SA.ID = IM.FK_SavingAccount
INSERT INTO @PunishedAccounts(FK_SavingsAccount,MinBalanceByMonth,PenaltyFee)
SELECT SA.ID, MBM.MinBalance, SAT.MB_PunishAmount
FROM SavingsAccount SA
INNER JOIN MinBalanceByMonth MBM ON SA.ID = MBM.FK_SavingAccount
INNER JOIN SavingsAccountType SAT ON SAT.ID = SA.Fk_SavingAccountType
WHERE MBM.MinBalance < SAT.MonthServiceCost
INSERT INTO BalanceMov(FK_SavingAccount, FK_MovType, Date, Amount)
SELECT PA.FK_SavingsAccount,1,@ProcDate,PA.PenaltyFee
FROM @PunishedAccounts PA
UPDATE SavingsAccount SET Balance = Balance  - PA.PenaltyFee
FROM @PunishedAccounts PA INNER JOIN SavingsAccount S ON S.ID = PA.FK_SavingsAccount
INSERT INTO MinBalanceByMonth(FK_SavingAccount,YearMonth,MinBalance)
SELECT S.ID,@ProcDate,S.Balance
FROM SavingsAccount S
END

INSERT INTO InterestMov(FK_SavingAccount,FK_InterestMovType,Date,Amount)
SELECT DI.FK_SavingsAccount,0,@ProcDate,DI.Amount
FROM @DailyInterests DI
UPDATE SavingsAccount SET MounthAccountInterest = DI.Amount + MounthAccountInterest FROM SavingsAccount SA INNER JOIN @DailyInterests DI ON SA.ID = DI.FK_SavingsAccount
COMMIT
RETURN 1
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
RETURN 0
END CATCH
END

GO