USP_DATALIST_CREDITPAYMENTNOTLINKEDTODEPOSITS

CREATE procedure dbo.USP_DATALIST_CREDITPAYMENTNOTLINKEDTODEPOSITS
(
    @TOTAL money = null output
    ,@COUNT integer = null output
)
as
    set nocount on;

    declare @Data table (PAYMENTMETHOD nvarchar(100), AMOUNT money, [COUNT] integer);

    insert into @Data
    select 
        CREDITPAYMENT.PAYMENTMETHOD, 
        SUM(CREDITPAYMENT.AMOUNT),
        COUNT(CREDITPAYMENT.AMOUNT)
    from dbo.CREDITPAYMENT 
    inner join dbo.CREDIT on CREDIT.ID = CREDITPAYMENT.CREDITID 
    left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT LINK on LINK.ID = CREDITPAYMENT.ID 
    inner join dbo.RECONCILIATION on RECONCILIATION.ID = CREDITPAYMENT.RECONCILIATIONID 
    where 
    RECONCILIATION.STATUSCODE = 3 and LINK.DEPOSITID is null
        and not (CREDITPAYMENT.PAYMENTMETHODCODE = 10 and CREDITPAYMENT.OTHERPAYMENTMETHODCODEID in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD))
        and CREDITPAYMENT.ID in (select CREDITGLDISTRIBUTION.CREDITPAYMENTID from CREDITGLDISTRIBUTION inner join dbo.GLTRANSACTION on GLTRANSACTION.ID = CREDITGLDISTRIBUTION.GLTRANSACTIONID where GLTRANSACTION.POSTSTATUSCODE = 1)
    group by CREDITPAYMENT.PAYMENTMETHOD;

    select @TOTAL = SUM(D.AMOUNT)
        ,@COUNT = SUM(D.[COUNT])
    from @Data D;

    select D.PAYMENTMETHOD + ' - ' + convert(nvarchar(30), SUM(D.[COUNT])), SUM(D.AMOUNT)
    from @Data D
    group by D.PAYMENTMETHOD
    order by SUM(D.AMOUNT) / @TOTAL desc;