SQL-LIFE Billing Journal q
Jump to navigation
Jump to search
Lists a set of clients journaled into a specific DO file.
/* * * * * * * * * * * * * * * * * * *
Created : Marc Gammon, Feb 2013
Purpouse : Gather billing details for debit order run file export
Modified : Marc Gammon May 2013
Purpose : can only have one entry in each file for each applicant.
Needed to add "Group by" and Sum(RandValue)
* * * * * * * * * * * * * * * * * * * */
CREATE PROCEDURE dbo.LIFE_Billing_Journal_q
@EFTRunID int
AS
BEGIN
SELECT sum(RandValue) as TotalValue, BankName, BranchName, BranchCode,
AccType, AccNumber, AccHolderName, DeductDay, LBJ.ApplicationID,
convert(varchar,LR.TransactionDate,111) as TransDate, count(*) as Coverages
FROM t_Life_Billing_Journal LBJ inner join t_Life_EFT_Run LR on LBJ.LifeEFTRunID = LR.ID
inner join t_Life_Banking LB on LBJ.LifeBankingID = LB.ID
inner join t_Application A on LBJ.ApplicationID = A.ID
WHERE LifeEFTRunID = @EFTRunID
AND RandValue > 0
GROUP By BankName, BranchName, BranchCode, AccType, AccNumber, AccHolderName,
DeductDay, LBJ.ApplicationID, convert(varchar,LR.TransactionDate,111)
END
GO