Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
1.8 kB
3
Indexable
Never
select tmp."clientCode", sum(tmp.amount) as amount , tmp."destinationAccountNumber", tmp."destinationAccountType", tmp.cat_origen, tmp.cat_dest, tmp."ticketOrigen", tmp."ticketDest"
				from (
					select distinct cat."clientCode", cat.amount, cat."destinationAccountNumber", cat."destinationAccountType", p_origen."productCategoryId" as cat_origen, p_destination."productCategoryId" as cat_dest, tmp_origen."minTicket" as "ticketOrigen", tmp_dest."minTicket" as "ticketDest"
					from customer_account_transactions cat
					inner join products p_origen on (p_origen.code  = cat."sourceAccountType")
					inner join products p_destination on (p_destination.code  = cat."destinationAccountType")
					inner join (
						select vw."productCategoryId", vw."incrementAmount", vw."minTicket"
						from vw_part_form_by_raffle vw
						where "raffleId" = raffle_id 
						and vw.additional = false
						group by vw."productCategoryId", vw."incrementAmount", vw."minTicket"
					) tmp_origen on (tmp_origen."productCategoryId" = p_origen."productCategoryId") 
					inner join (
						select vw."productCategoryId", vw."incrementAmount", vw."minTicket"
						from vw_part_form_by_raffle vw
						where "raffleId" = raffle_id 
						and vw.additional = false
						group by vw."productCategoryId", vw."incrementAmount", vw."minTicket"
					) tmp_dest on (tmp_dest."productCategoryId" = p_destination."productCategoryId") 
					where cat."clientCode" = client_rec."clientCode"
					-- and tmp_dest."minTicket" > tmp_origen."minTicket" -- Para omitar las transacciones propias sin importar las categorias
				) tmp
				group by tmp."clientCode", 
				tmp."destinationAccountNumber",
				tmp."destinationAccountType",
				tmp.cat_origen, 
				tmp.cat_dest, 
				tmp."ticketOrigen", 
				tmp."ticketDest"