USP_DATALIST_PAYMENTSNOTLINKEDTODEPOSITS

CREATE procedure dbo.USP_DATALIST_PAYMENTSNOTLINKEDTODEPOSITS
(
    @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
        REVENUEPAYMENTMETHOD.PAYMENTMETHOD
        ,SUM([REVENUE].[TRANSACTIONAMOUNT]) AMOUNT
        ,COUNT([REVENUE].[TRANSACTIONAMOUNT]) [COUNT]
    from dbo.REVENUE
    left join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
    left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
    left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
    left join dbo.ADJUSTMENT on ADJUSTMENT.REVENUEID = REVENUEPOSTED.ID
    left join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = REVENUE.ID
    left join dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
    left join dbo.RECONCILIATION on SALESORDERPAYMENT.RECONCILIATIONID = RECONCILIATION.ID
    left join dbo.OTHERPAYMENTMETHODDETAIL on OTHERPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
    left join dbo.RESERVATIONSECURITYDEPOSITPAYMENT on RESERVATIONSECURITYDEPOSITPAYMENT.PAYMENTID = REVENUE.ID
    where [BANKACCOUNTDEPOSITPAYMENT].[DEPOSITID] IS NULL AND [REVENUE].[TRANSACTIONTYPECODE] = 0
        and [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] in (0,1,2,10)
        and (SALESORDERPAYMENT.ID is null or RECONCILIATION.STATUSCODE = 3 or (SALESORDERPAYMENT.DONOTRECONCILE = 1 and SALESORDER.STATUSCODE = 1))
        and (NOT exists(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD where ID = OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID))
        and    ((RESERVATIONSECURITYDEPOSITPAYMENT.ID is null) or 
            (RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID is not null and exists 
                (select 1 from dbo.RECONCILIATION REC where REC.STATUSCODE = 3 and REC.ID = RESERVATIONSECURITYDEPOSITPAYMENT.RECONCILIATIONID)
            ))
        and ((REVENUE.DONOTPOST = 0 and REVENUEPOSTED.ID is null) or (ADJUSTMENT.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 1))
    group by REVENUEPAYMENTMETHOD.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;