SELECT
	A.ACQUIRERINSTITUTIONID
	,A.MERCHANTID
	,A.CARDACQID
	,A.CLEARINGINSTITUTIONID
	,C.BANKCODE
	,C.BANKACCOUNT
	,C.BANKACCOUNTTYPE
	,C.BANKACCOUNTPERCENTAGE
	,C.PAYMENTFORM
	,A.CURRENCY
FROM
	INTERCHG A

	JOIN MERCHANT B ON B.[MERCHANTID] = A.[MERCHANTID]

	JOIN BANKACCOUNTSBYMIDCAIDCI C ON
		C.[MERCHANTID] = A.[MERCHANTID]
		AND C.[CARDACQID] = A.[CARDACQID]
		AND C.[CLEARINGINSTITUTIONID] = A.[CLEARINGINSTITUTIONID]

	JOIN PAYMENTCYCLES D ON D.[PAYMENTCYCLECODE] = A.[PAYMENTCYCLECODE]
WHERE

	-- JOINS
	-- A.MERCHANTID=B.MERCHANTID AND
	-- B.PAYMENTCYCLECODE=D.PAYMENTCYCLECODE AND	
	-- A.MERCHANTID=C.MERCHANTID AND
	-- A.CARDACQID=C.CARDACQID AND 
	-- A.CLEARINGINSTITUTIONID=C.CLEARINGINSTITUTIONID AND     

	-- FILTERS
	A.PAYED='0'
	AND A.PRESENTED='1'
	AND A.SETTLED='1'
	AND A.ONHOLD='0'
	AND A.PREPAID='0'
	AND B.PAYMENTGROUPINGLEVEL='2'

	AND A.TRANSACTIONTYPE IN (
		SELECT TRANSACTIONTYPE
		FROM TRANSACTIONTYPES
		WHERE FINANCIALTRANSACTION='1' AND TRANSACTIONTYPEGROUP='S'
		)

	-- THESE ARE PROBLEMS -- THEY CAN'T USE ANY INDEX ON THE RELEVENT
	-- COLUMN
	AND SUBSTRING(ISNULL(A.ADDITIONALDATA,'**'),1,2)<>'AT'
	AND	substring(D.PAYMENTCYCLEDAYS,datepart(dw,'2010-09-30'),1)='1'
GROUP BY
	A.ACQUIRERINSTITUTIONID
	,A.MERCHANTID
	,A.CARDACQID
	,A.CLEARINGINSTITUTIONID
	,C.BANKCODE
	,C.BANKACCOUNT
	,C.BANKACCOUNTTYPE
	,C.BANKACCOUNTPERCENTAGE
	,C.PAYMENTFORM
	,A.CURRENCY
HAVING
	SUM(A.AMOUNTTOTAL) > 0
ORDER BY
	A.ACQUIRERINSTITUTIONID
	,A.MERCHANTID
	,A.CARDACQID
	,A.CLEARINGINSTITUTIONID
	,C.BANKCODE
	,C.BANKACCOUNT
	,C.BANKACCOUNTTYPE
	,C.BANKACCOUNTPERCENTAGE
	,C.PAYMENTFORM
	,A.CURRENCY

AND	substring(D.PAYMENTCYCLEDAYS,datepart(dw,'2010-09-30'),1)='1'

SELECT		ic.AcquirerInstitutionID,
		ic.MerchantID,
		ic.CardAcqID,
		ic.ClearingInstitutionID,
		c.BankCode,
		c.BankAccount,
		c.BankAccountType,
		c.BankAccountPercentage,
		c.PaymentForm,
		ic.Currency
FROM		dbo.Merchant AS m
INNER JOIN	dbo.InterChg AS ic ON ic.MerchantID = m.MerchantID
			AND ic.Payed = 0
			AND ic.Presented = 1
			AND ic.Settled = 1
			AND ic.OnHold = 0
			AND ic.PrePaid = 0
			AND (ic.ADDITIONALDATA NOT LIKE 'AT%' OR ic.ADDITIONALDATA IS NULL)
INNER JOIN	(
			SELECT		TransactionType
			FROM		dbo.TransactionTypes
			WHERE		FinancialTransaction = 1
					AND TransactionTypeGroup = 'S'
			GROUP BY	TransactionType
		) AS x ON x.TransactionType = ic.TransactionType
INNER JOIN	dbo.BankAccountsByMIDCAIDCI AS c ON c.MerchantID = ic.MerchantID
			AND c.CardAcqID = ic.CardAcqID
			AND c.ClearingInstitutionID = ic.ClearingInstitutionID
INNER JOIN	dbo.PaymentCycles AS pc ON pc.PaymentCycleCode = m.PaymentCycleCode
			AND SUBSTRING(pc.PAYMENTCYCLEDAYS, DATEPART(WEEKDAY, '20100930'), 1) = '1'
WHERE		m.PaymentGroupingLevel = 2
GROUP BY	ic.AcquirerInstitutionID,
		ic.MerchantID,
		ic.CardAcqID,
		ic.ClearingInstitutionID,
		c.BankCode,
		c.BankAccount,
		c.BankAccountType,
		c.BankAccountPercentage,
		c.PaymentForm,
		ic.Currency
HAVING		SUM(ic.AmountTotal) > 0
ORDER BY	ic.AcquirerInstitutionID,
		ic.MerchantID,
		ic.CardAcqID