SQL-LIFE Billing Journal q

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