|
masond
Constraint Violating Yak Guru
256 Posts |
 Posted - 11/26/2012 : 04:57:59
|
Hey
I know i asked this question last week but i have lost the thread: @ grr
I want to eliminate any columns in the final pivot if that column contains null values
My pivot is as follows
SELECT DISTINCT
FDMSAccountNo as FDMSAccountNo,
[REFUND TXN CHARGE],
[AUTHORISATION REQUEST],
[POLLING FEE],
[CHARGEBACK FEE],
[ANNUAL MEMBERSHIP FEE],
[MMV MONTHLY ACCESS FEE],
[CASHBACK FEE],
[TERMINAL FEE],
[MMV MONTHLY FEE],
[MONTHLY STATEMENT FEE],
[FDMS LINK MONTHLY FEE],
[POS PARTNER],
[SOFTWARE UPGRADE],
[MC AUTO BILLING UPD MTH FEE],
[MONTHLY CLIENTLINE FEE],
[3-TIER MONTHLY SERVICE CHARGE],
[MONTHLY MAINTENANCE FEE],
[JOINING FEE],
[PCI DSS MANAGEMENT FEE],
[PCI DSS MANAGEMENT PM],
[PCI DSS INACTIVITY CHARGE],
[PCI DSS NON COMPLIANCE],
[MC DEBIT INTERCHANGE PLUS],
[MC DEBIT IC PLUS SALES],
[MC DEBIT CHIP INTERCHANGE PLUS],
[MC DEBIT CHIP IC PLUS SALES],
[MAESTRO SALES IC FEE],
[MAESTRO CHIP SALES IC FEE],
[MAESTRO INTERCHANGE FEE],
[MAESTRO CHIP INTERCHANGE FEE],
[SOLO INTERCHANGE FEE],
[SOLO CHIP INTERCHANGE FEE],
[SOLO SALES IC FEE],
[SOLO CHIP SALES IC FEE],
[VISA SALES IC FEE],
[VISA ELECTRON SALES IC FEE],
[VISA CHIP SALES IC FEE],
[VISA PURCH CARD SALES IC FEE],
[VISA DEBIT SALES IC FEE],
[VISA DEBIT CHIP SALES IC FEE],
[VISA INTERCHANGE FEE],
[VISA ELECTRON INTERCHANGE FEE],
[VISA CHIP INTERCHANGE FEE],
[VISA PURCH CARD INTRCHG FEE],
[VISA DEBIT INTERCHANGE FEE],
[VISA DEBIT CHIP INTERCHG FEE],
[MASTERCARD SALES IC FEE],
[MASTERCARD INTERCHANGE FEE],
[MC INTERCHANGE],
[MC CHIP INTERCHANGE FEE],
[MC PURCH CARD INTERCHANGE FEE],
[MC CHIP SALES IC FEE],
[MC PURCH CARD SALES IC FEE],
[DINERS INTERCHANGE FEE],
[M/C+MAESTRO EU ACCEPTANCE FEE],
[M/C INT. RETAIL ACCEPTANCE FEE],
[MAESTRO INT. ACCEPTANCE FEE],
[MC DEBIT SERVICE CHARGE],
[MC DEBIT REFUNDS],
[MC DEBIT SALES TRANSACTION FEE],
[MC DEBIT REFUND TRANSACTION],
[MC DEBIT NQ SERVICE CHARGE],
[MC DEBIT NQ REFUNDS],
[MC DEBIT NQ SALES TRANS FEE],
[MC DEBIT NQ REFUND TRANSACTION],
[MC DEBIT CHIP SERVICE CHARGE],
[MC DEBIT CHIP SALES TRANS FEE],
[MC DEBIT CHIP REFUND TRANS FEE],
[MC DEBIT CHIP NQ SERVICE CHRG],
[MC DBT CHP NQ SALES TRANS],
[VISA NON-QUAL SALES TRANS FEE],
[VISA NON-QUAL REFUNDS TRAN FEE],
[VISA DEBIT NQ SALES TRANS FEE],
[VISA DEBIT NQ REFUND TRANS FEE],
[VISA ELECT DR NQ SALE TRAN FEE],
[VISA ELECT DR NQ RFD TRANS FEE],
[MAESTRO NON-QUAL SLS TRANS FEE],
[SOLO NON-QUAL SALES TRANS FEE],
[VISA CHIP NQ SALES TRANS FEE],
[VISA CHIP NQ REFUNDS TRANS FEE],
[VISA DR CHIP NQ SALES TRAN FEE],
[VISA DR CHIP NQ RFND TRANS FEE],
[MAESTRO CHIP NQ SALES TRANS FE],
[SOLO CHIP NQ SALES TRANS FEE],
[VISA PURCH CARD NQ RFND T/FEE],
[VISA NON-QUAL SERVICE CHARGE],
[VISA NON-QUAL REFUNDS],
[VISA NQ DEBIT SERVICE CHARGE],
[VISA NON-QUAL DEBIT REFUNDS],
[VISA ELECTRON NQ DR SERV CHG],
[VISA ELECTRON NQ DR REFUNDS],
[VISA CHIP NQ SERVICE CHARGE],
[VISA CHIP NON-QUAL REFUNDS],
[VISA DEBIT CHIP NQ SERVICE CHG],
[MAESTRO NON-QUAL SERVICE CHG],
[INTL MSTRO NQ SERVICE CHARGE],
[VISA PURCHASE CARD NQ SRV CHG],
[MASTERCARD NQ SERVICE CHARGE],
[MASTERCARD NON-QUAL REFUNDS],
[MASTERCARD CHIP NQ SRV CHRG],
[MASTERCARD CHIP NQ REFUNDS],
[MC PURCHASE CARD NQ SRV CHRG],
[MASTERCARD NQ SALES TRANS FEE],
[MASTERCARD NQ RFND TRANS FEE],
[MASTERCARD CHIP NQ RFND T/FEE],
[VISA PRCH NQ SRVCHG(EX BUS DR)],
[DINERS SALES TRANS FEE],
[VISA BUS DR CARD NQ SALE T/FEE],
[VISA BUS DR CARD NQ RFND T/FEE],
[VISA PRCH NQ REF TX(EX BUS DR)],
[VISA BUS DR CARD NQ SRV CHG],
[MC NQ SERVICE CHARGE (PREM)],
[MC NON-QUAL REFUNDS (PREM)],
[MC CHIP NQ SRV CHRG (PREM)],
[MONTHLY INTERNET ACCESS FEE],
[VAT INT MTH ACCESS FEE 17.5%],
[TERMINAL RENTAL FEE],
[VAT TERMINAL RENTAL FEE 17.5%],
[VIRTUAL TERM TRANSACTION FEE],
[VAT VIRT TERM TRAN FEE 15.0%],
[MIN VIRTUAL TERMINAL TRAN FEE],
[VAT MIN VIR TERM TRAN FEE15.0%],
[VISA NQSERVICE CHG (CHCD)],
[VISA NON.QUALREFUNDS(CHCD)],
[VISA CHIP NQ SERVICE CHG(CHCD)],
[VISA NON-QUAL SRV CHG EX.CHCD],
[VISA NON-QUAL REFUNDS EX.CHCD],
[VISA CHIP NQ SRV CHG EX.CHCD],
[MC NQ SERVICE CHARGE EX. PREM],
[MC NON-QUAL REFUNDS EX. PREM],
[MC CHIP NQ SRV CHRG EX. PREM],
[MC CHIP NQ REFUNDS EX. PREM],
[VISA SERVICE CHARGE],
[VISA REFUNDS],
[VISA DEBIT SERVICE CHARGE],
[VISA DEBIT REFUNDS],
[VISA ELECTRON DR SERV CHARGE],
[VISA ELECTRON DR REFUNDS],
[MAESTRO SERVICE CHARGE],
[MAESTRO REFUNDS],
[SOLO SERVICE CHARGE],
[SOLO REFUNDS],
[VISA CHIP SERVICE CHARGE],
[VISA CHIP REFUNDS],
[VISA DEBIT CHIP SERVICE CHARGE],
[MAESTRO CHIP SERVICE CHARGE],
[SOLO CHIP SERVICE CHARGE],
--[VISA PURCHASE CARD SERV CHRG],
--[VISA PURCHASE CARD REFUNDS],
[VISA COMMERCIAL CARD SERV CHRG],
[VISA COMMERCIAL CARD REFUNDS],
[VISA SALES TRANS FEE],
[VISA REFUNDS TRANS FEE],
[VISA DEBIT SALES TRANS FEE],
[VISA DEBIT REFUNDS TRANS FEE],
[VISA ELECTRON DR SALE TRAN FEE],
[VISA ELECTRON DR RFD TRANS FEE],
[MAESTRO SALES TRANS FEE],
[MAESTRO REFUNDS TRANS FEE],
[SOLO SALES TRANS FEE],
[SOLO REFUNDS TRANS FEE],
[VISA CHIP SALES TRANS FEE],
[VISA CHIP REFUNDS TRANS FEE],
[VISA DR CHIP SALE TRANS FEE],
[VISA DR CHIP REFUNDS TRANS FEE],
[VISA COMM CARD RFND T/FEE],
[MAESTRO CHIP SALES TRANS FEE],
[MAESTRO CHIP REFUNDS TRANS FEE],
[SOLO CHIP SALES TRANS FEE],
[SOLO CHIP REFUNDS TRANS FEE],
[VISA PURCHASE CARD SALE T/FEE],
[VISA PURCHASE CARD RFND T/FEE],
[INTL MSTRO RFND TRN CNT-ED],
[MASTERCARD SERVICE CHARGE],
[MASTERCARD REFUNDS],
[MASTERCARD CHIP SERVICE CHARGE],
[MASTERCARD CHIP REFUNDS],
[MC PURCHASE CARD SERV CHRG],
[MC PURCHASE CARD REFUNDS],
[MASTERCARD SALES TRANS FEE],
[MASTERCARD REFUNDS TRANS FEE],
[MASTERCARD CHIP SALE TRANS FEE],
[MASTERCARD CHIP RFND TRANS FEE],
[MC PURCHASE CARD SALE T/FEE],
[MC PURCHASE CARD RFND T/FEE],
[MC COMMERCIAL CARD RFND T/FEE],
[MC COMMERCIAL CARD SERV CHRG],
[MC COMMERCIAL CARD REFUNDS],
[VISA PRCH SRV CHG(EX BUS DR)],
[VISA PRCH REFUND (EX BUS DR)],
[VISA BUS DR CARD SERV CHRG],
[VISA SERVICE CHARGE EX.CHCD],
[VISA REFUNDS EX.CHCD],
[VISA CHIP SERVICE CHG EX.CHCD],
[VISA CHIP REFUNDS EX.CHCD],
[VISA BUS DR CARD SALE T/FEE],
[VISA BUS DR CARD RFND T/FEE],
[VISA PRCH SLS T/FEE(EX BUS DR)],
[VISA PRCH REF T/FEE(EX BUS DR)],
[MC SERVICE CHARGE (PREM)],
[MC REFUNDS (PREM)],
[MC CHIP SERVICE CHARGE (PREM)],
[MC CHIP REFUNDS (PREM)],
[MC REFUNDS TRANS FEE (PREM)],
[MC CHIP RFND TRANS FEE (PREM)],
[VISA SERVICE CHARGE(CHCD)],
[VISA SERVICE CHARGE(CHCD) RFND],
[VISA CHIP SERVICE CHARGE(CHCD)],
[VISA PREM RFND T/FEE],
[VISA REFUNDS TRANS FEE EX.CHCD],
[VISA CHIP REFUND T/FEE EX.CHCD],
[MC SERVICE CHARGE EX. PREM],
[MC REFUNDS EX. PREM],
[MC CHIP SERVICE CHG EX. PREM],
[MC CHIP REFUNDS EX. PREM],
[MC REFUNDS TRANS FEE EX. PREM],
[MC CHIP REF TRANS FEE EX.PREM],
[VISA COMM REF T/FEE(EX BUS DR)],
[VISA COMM SERV CHRG(EX BUS DR)],
[VISA COMM REFUNDS (EX BUS DR)],
[MINIMUM MONTHLY CHARGE],
[BOARDING FEE],
[Authorisation Wholesale Fee],
[AMEX],
[CANCELLATION FEE],
[FUNDING ADJUSTMENT],
[DEBIT CARD DEPOSITS],
[INTRCHNG REJ RESOLVED TO MERCH],
[REPRESENTED DD],
[TERMINAL BILLING],
[FDGL Clawback],
[FDGL CONFIG FEE],
[FDGL Commission],
[HIGH RISK UNDERWRITING FEE],
[MSC ADJUSTMENT],
[CREDIT ADJUSTMENT],
[EBT CREDIT ADJUSTMENT],
[MAESTRO CHARGEBACK REVERSAL CR],
[MAESTRO CHARGEBACK CREDIT],
[MAESTRO CHARGEBACK DEBIT],
[MINIMUM ANNUAL SHORT FALL],
[PAYMENT 1],
[RECLAIMED FUNDS],
[PAYMENT 3],
[CHARGEBACK SUSPENSE],
[SUSPENSE CLEARANCE],
[ADJUSTMENT],
[MASTERCARD ADJUSTMENTS],
[TBA],
[LETTER FEE],
[NON-PAYMENT OF DIRECT DEBIT],
[EXCESSIVE CHARGEBACK FEE],
[OPERATIONAL GUIDANCE FEE],
[COLLECTIONS FEE],
[MANAGEMENT FEE],
[TERMINATION FEE],
[VAT TERMINAL FEE ADJUSTMENT],
[TERMINAL FEE ADJUSTMENT],
[MAESTRO ADJ. CUSTOMER INQUIRY],
[LEGAL ENTITY ADMIN FEE],
[CARD NOT PRESENT ADMIN FEE],
[ADJUSTMENT],
[MISC ADJUSTMENT],
[PENDING INVESTIGATION],
[MONTHLY RESIDENCE FEE],
[Refund Wholesale fee],
ISNULL([REFUND TXN CHARGE],0)+
ISNULL([AUTHORISATION REQUEST],0)+
ISNULL([POLLING FEE],0)+
ISNULL([CHARGEBACK FEE],0)+
ISNULL([ANNUAL MEMBERSHIP FEE],0)+
ISNULL([MMV MONTHLY ACCESS FEE],0)+
ISNULL([CASHBACK FEE],0)+
ISNULL([TERMINAL FEE],0)+
ISNULL([MMV MONTHLY FEE],0)+
ISNULL([MONTHLY STATEMENT FEE],0)+
ISNULL([FDMS LINK MONTHLY FEE],0)+
ISNULL([POS PARTNER],0)+
ISNULL([SOFTWARE UPGRADE],0)+
ISNULL([MC AUTO BILLING UPD MTH FEE],0)+
ISNULL([MONTHLY CLIENTLINE FEE],0)+
ISNULL([3-TIER MONTHLY SERVICE CHARGE],0)+
ISNULL([MONTHLY MAINTENANCE FEE],0)+
ISNULL([JOINING FEE],0)+
ISNULL([PCI DSS MANAGEMENT FEE],0)+
ISNULL([PCI DSS MANAGEMENT PM],0)+
ISNULL([PCI DSS INACTIVITY CHARGE],0)+
ISNULL([PCI DSS NON COMPLIANCE],0)+
ISNULL([MC DEBIT INTERCHANGE PLUS],0)+
ISNULL([MC DEBIT IC PLUS SALES],0)+
ISNULL([MC DEBIT CHIP INTERCHANGE PLUS],0)+
ISNULL([MC DEBIT CHIP IC PLUS SALES],0)+
ISNULL([MAESTRO SALES IC FEE],0)+
ISNULL([MAESTRO CHIP SALES IC FEE],0)+
ISNULL([MAESTRO INTERCHANGE FEE],0)+
ISNULL([MAESTRO CHIP INTERCHANGE FEE],0)+
ISNULL([SOLO INTERCHANGE FEE],0)+
ISNULL([SOLO CHIP INTERCHANGE FEE],0)+
ISNULL([SOLO SALES IC FEE],0)+
ISNULL([SOLO CHIP SALES IC FEE],0)+
ISNULL([VISA SALES IC FEE],0)+
ISNULL([VISA ELECTRON SALES IC FEE],0)+
ISNULL([VISA CHIP SALES IC FEE],0)+
ISNULL([VISA PURCH CARD SALES IC FEE],0)+
ISNULL([VISA DEBIT SALES IC FEE],0)+
ISNULL([VISA DEBIT CHIP SALES IC FEE],0)+
ISNULL([VISA INTERCHANGE FEE],0)+
ISNULL([VISA ELECTRON INTERCHANGE FEE],0)+
ISNULL([VISA CHIP INTERCHANGE FEE],0)+
ISNULL([VISA PURCH CARD INTRCHG FEE],0)+
ISNULL([VISA DEBIT INTERCHANGE FEE],0)+
ISNULL([VISA DEBIT CHIP INTERCHG FEE],0)+
ISNULL([MASTERCARD SALES IC FEE],0)+
ISNULL([MASTERCARD INTERCHANGE FEE],0)+
ISNULL([MC INTERCHANGE],0)+
ISNULL([MC CHIP INTERCHANGE FEE],0)+
ISNULL([MC PURCH CARD INTERCHANGE FEE],0)+
ISNULL([MC CHIP SALES IC FEE],0)+
ISNULL([MC PURCH CARD SALES IC FEE],0)+
ISNULL([DINERS INTERCHANGE FEE],0)+
ISNULL([M/C+MAESTRO EU ACCEPTANCE FEE],0)+
ISNULL([M/C INT. RETAIL ACCEPTANCE FEE],0)+
ISNULL([MAESTRO INT. ACCEPTANCE FEE],0)+
ISNULL([MC DEBIT SERVICE CHARGE],0)+
ISNULL([MC DEBIT REFUNDS],0)+
ISNULL([MC DEBIT SALES TRANSACTION FEE],0)+
ISNULL([MC DEBIT REFUND TRANSACTION],0)+
ISNULL([MC DEBIT NQ SERVICE CHARGE],0)+
ISNULL([MC DEBIT NQ REFUNDS],0)+
ISNULL([MC DEBIT NQ SALES TRANS FEE],0)+
ISNULL([MC DEBIT NQ REFUND TRANSACTION],0)+
ISNULL([MC DEBIT CHIP SERVICE CHARGE],0)+
ISNULL([MC DEBIT CHIP SALES TRANS FEE],0)+
ISNULL([MC DEBIT CHIP REFUND TRANS FEE],0)+
ISNULL([MC DEBIT CHIP NQ SERVICE CHRG],0)+
ISNULL([MC DBT CHP NQ SALES TRANS],0)+
ISNULL([VISA NON-QUAL SALES TRANS FEE],0)+
ISNULL([VISA NON-QUAL REFUNDS TRAN FEE],0)+
ISNULL([VISA DEBIT NQ SALES TRANS FEE],0)+
ISNULL([VISA DEBIT NQ REFUND TRANS FEE],0)+
ISNULL([VISA ELECT DR NQ SALE TRAN FEE],0)+
ISNULL([VISA ELECT DR NQ RFD TRANS FEE],0)+
ISNULL([MAESTRO NON-QUAL SLS TRANS FEE],0)+
ISNULL([SOLO NON-QUAL SALES TRANS FEE],0)+
ISNULL([VISA CHIP NQ SALES TRANS FEE],0)+
ISNULL([VISA CHIP NQ REFUNDS TRANS FEE],0)+
ISNULL([VISA DR CHIP NQ SALES TRAN FEE],0)+
ISNULL([VISA DR CHIP NQ RFND TRANS FEE],0)+
ISNULL([MAESTRO CHIP NQ SALES TRANS FE],0)+
ISNULL([SOLO CHIP NQ SALES TRANS FEE],0)+
ISNULL([VISA PURCH CARD NQ RFND T/FEE],0)+
ISNULL([VISA NON-QUAL SERVICE CHARGE],0)+
ISNULL([VISA NON-QUAL REFUNDS],0)+
ISNULL([VISA NQ DEBIT SERVICE CHARGE],0)+
ISNULL([VISA NON-QUAL DEBIT REFUNDS],0)+
ISNULL([VISA ELECTRON NQ DR SERV CHG],0)+
ISNULL([VISA ELECTRON NQ DR REFUNDS],0)+
ISNULL([VISA CHIP NQ SERVICE CHARGE],0)+
ISNULL([VISA CHIP NON-QUAL REFUNDS],0)+
ISNULL([VISA DEBIT CHIP NQ SERVICE CHG],0)+
ISNULL([MAESTRO NON-QUAL SERVICE CHG],0)+
ISNULL([INTL MSTRO NQ SERVICE CHARGE],0)+
ISNULL([VISA PURCHASE CARD NQ SRV CHG],0)+
ISNULL([MASTERCARD NQ SERVICE CHARGE],0)+
ISNULL([MASTERCARD NON-QUAL REFUNDS],0)+
ISNULL([MASTERCARD CHIP NQ SRV CHRG],0)+
ISNULL([MASTERCARD CHIP NQ REFUNDS],0)+
ISNULL([MC PURCHASE CARD NQ SRV CHRG],0)+
ISNULL([MASTERCARD NQ SALES TRANS FEE],0)+
ISNULL([MASTERCARD NQ RFND TRANS FEE],0)+
ISNULL([MASTERCARD CHIP NQ RFND T/FEE],0)+
ISNULL([VISA PRCH NQ SRVCHG(EX BUS DR)],0)+
ISNULL([DINERS SALES TRANS FEE],0)+
ISNULL([VISA BUS DR CARD NQ SALE T/FEE],0)+
ISNULL([VISA BUS DR CARD NQ RFND T/FEE],0)+
ISNULL([VISA PRCH NQ REF TX(EX BUS DR)],0)+
ISNULL([VISA BUS DR CARD NQ SRV CHG],0)+
ISNULL([MC NQ SERVICE CHARGE (PREM)],0)+
ISNULL([MC NON-QUAL REFUNDS (PREM)],0)+
ISNULL([MC CHIP NQ SRV CHRG (PREM)],0)+
ISNULL([MONTHLY INTERNET ACCESS FEE],0)+
ISNULL([VAT INT MTH ACCESS FEE 17.5%],0)+
ISNULL([TERMINAL RENTAL FEE],0)+
ISNULL([VAT TERMINAL RENTAL FEE 17.5%],0)+
ISNULL([VIRTUAL TERM TRANSACTION FEE],0)+
ISNULL([VAT VIRT TERM TRAN FEE 15.0%],0)+
ISNULL([MIN VIRTUAL TERMINAL TRAN FEE],0)+
ISNULL([VAT MIN VIR TERM TRAN FEE15.0%],0)+
ISNULL([VISA NQSERVICE CHG (CHCD)],0)+
ISNULL([VISA NON.QUALREFUNDS(CHCD)],0)+
ISNULL([VISA CHIP NQ SERVICE CHG(CHCD)],0)+
ISNULL([VISA NON-QUAL SRV CHG EX.CHCD],0)+
ISNULL([VISA NON-QUAL REFUNDS EX.CHCD],0)+
ISNULL([VISA CHIP NQ SRV CHG EX.CHCD],0)+
ISNULL([MC NQ SERVICE CHARGE EX. PREM],0)+
ISNULL([MC NON-QUAL REFUNDS EX. PREM],0)+
ISNULL([MC CHIP NQ SRV CHRG EX. PREM],0)+
ISNULL([MC CHIP NQ REFUNDS EX. PREM],0)+
ISNULL([VISA SERVICE CHARGE],0)+
ISNULL([VISA REFUNDS],0)+
ISNULL([VISA DEBIT SERVICE CHARGE],0)+
ISNULL([VISA DEBIT REFUNDS],0)+
ISNULL([VISA ELECTRON DR SERV CHARGE],0)+
ISNULL([VISA ELECTRON DR REFUNDS],0)+
ISNULL([MAESTRO SERVICE CHARGE],0)+
ISNULL([MAESTRO REFUNDS],0)+
ISNULL([SOLO SERVICE CHARGE],0)+
ISNULL([SOLO REFUNDS],0)+
ISNULL([VISA CHIP SERVICE CHARGE],0)+
ISNULL([VISA CHIP REFUNDS],0)+
ISNULL([VISA DEBIT CHIP SERVICE CHARGE],0)+
ISNULL([MAESTRO CHIP SERVICE CHARGE],0)+
ISNULL([SOLO CHIP SERVICE CHARGE],0)+
--ISNULL([VISA PURCHASE CARD SERV CHRG],0)+
--ISNULL([VISA PURCHASE CARD REFUNDS],0)+
ISNULL([VISA COMMERCIAL CARD SERV CHRG],0)+
ISNULL([VISA COMMERCIAL CARD REFUNDS],0)+
ISNULL([VISA SALES TRANS FEE],0)+
ISNULL([VISA REFUNDS TRANS FEE],0)+
ISNULL([VISA DEBIT SALES TRANS FEE],0)+
ISNULL([VISA DEBIT REFUNDS TRANS FEE],0)+
ISNULL([VISA ELECTRON DR SALE TRAN FEE],0)+
ISNULL([VISA ELECTRON DR RFD TRANS FEE],0)+
ISNULL([MAESTRO SALES TRANS FEE],0)+
ISNULL([MAESTRO REFUNDS TRANS FEE],0)+
ISNULL([SOLO SALES TRANS FEE],0)+
ISNULL([SOLO REFUNDS TRANS FEE],0)+
ISNULL([VISA CHIP SALES TRANS FEE],0)+
ISNULL([VISA CHIP REFUNDS TRANS FEE],0)+
ISNULL([VISA DR CHIP SALE TRANS FEE],0)+
ISNULL([VISA DR CHIP REFUNDS TRANS FEE],0)+
ISNULL([VISA COMM CARD RFND T/FEE],0)+
ISNULL([MAESTRO CHIP SALES TRANS FEE],0)+
ISNULL([MAESTRO CHIP REFUNDS TRANS FEE],0)+
ISNULL([SOLO CHIP SALES TRANS FEE],0)+
ISNULL([SOLO CHIP REFUNDS TRANS FEE],0)+
ISNULL([VISA PURCHASE CARD SALE T/FEE],0)+
ISNULL([VISA PURCHASE CARD RFND T/FEE],0)+
ISNULL([INTL MSTRO RFND TRN CNT-ED],0)+
ISNULL([MASTERCARD SERVICE CHARGE],0)+
ISNULL([MASTERCARD REFUNDS],0)+
ISNULL([MASTERCARD CHIP SERVICE CHARGE],0)+
ISNULL([MASTERCARD CHIP REFUNDS],0)+
ISNULL([MC PURCHASE CARD SERV CHRG],0)+
ISNULL([MC PURCHASE CARD REFUNDS],0)+
ISNULL([MASTERCARD SALES TRANS FEE],0)+
ISNULL([MASTERCARD REFUNDS TRANS FEE],0)+
ISNULL([MASTERCARD CHIP SALE TRANS FEE],0)+
ISNULL([MASTERCARD CHIP RFND TRANS FEE],0)+
ISNULL([MC PURCHASE CARD SALE T/FEE],0)+
ISNULL([MC PURCHASE CARD RFND T/FEE],0)+
ISNULL([MC COMMERCIAL CARD RFND T/FEE],0)+
ISNULL([MC COMMERCIAL CARD SERV CHRG],0)+
ISNULL([MC COMMERCIAL CARD REFUNDS],0)+
ISNULL([VISA PRCH SRV CHG(EX BUS DR)],0)+
ISNULL([VISA PRCH REFUND (EX BUS DR)],0)+
ISNULL([VISA BUS DR CARD SERV CHRG],0)+
ISNULL([VISA SERVICE CHARGE EX.CHCD],0)+
ISNULL([VISA REFUNDS EX.CHCD],0)+
ISNULL([VISA CHIP SERVICE CHG EX.CHCD],0)+
ISNULL([VISA CHIP REFUNDS EX.CHCD],0)+
ISNULL([VISA BUS DR CARD SALE T/FEE],0)+
ISNULL([VISA BUS DR CARD RFND T/FEE],0)+
ISNULL([VISA PRCH SLS T/FEE(EX BUS DR)],0)+
ISNULL([VISA PRCH REF T/FEE(EX BUS DR)],0)+
ISNULL([MC SERVICE CHARGE (PREM)],0)+
ISNULL([MC REFUNDS (PREM)],0)+
ISNULL([MC CHIP SERVICE CHARGE (PREM)],0)+
ISNULL([MC CHIP REFUNDS (PREM)],0)+
ISNULL([MC REFUNDS TRANS FEE (PREM)],0)+
ISNULL([MC CHIP RFND TRANS FEE (PREM)],0)+
ISNULL([VISA SERVICE CHARGE(CHCD)],0)+
ISNULL([VISA SERVICE CHARGE(CHCD) RFND],0)+
ISNULL([VISA CHIP SERVICE CHARGE(CHCD)],0)+
ISNULL([VISA PREM RFND T/FEE],0)+
ISNULL([VISA REFUNDS TRANS FEE EX.CHCD],0)+
ISNULL([VISA CHIP REFUND T/FEE EX.CHCD],0)+
ISNULL([MC SERVICE CHARGE EX. PREM],0)+
ISNULL([MC REFUNDS EX. PREM],0)+
ISNULL([MC CHIP SERVICE CHG EX. PREM],0)+
ISNULL([MC CHIP REFUNDS EX. PREM],0)+
ISNULL([MC REFUNDS TRANS FEE EX. PREM],0)+
ISNULL([MC CHIP REF TRANS FEE EX.PREM],0)+
ISNULL([VISA COMM REF T/FEE(EX BUS DR)],0)+
ISNULL([VISA COMM SERV CHRG(EX BUS DR)],0)+
ISNULL([VISA COMM REFUNDS (EX BUS DR)],0)+
ISNULL([MINIMUM MONTHLY CHARGE],0)+
ISNULL([BOARDING FEE],0)+
ISNULL([Authorisation Wholesale Fee],0)+
ISNULL([AMEX],0)+
ISNULL([CANCELLATION FEE],0)+
ISNULL([FUNDING ADJUSTMENT],0)+
ISNULL([DEBIT CARD DEPOSITS],0)+
ISNULL([INTRCHNG REJ RESOLVED TO MERCH],0)+
ISNULL([REPRESENTED DD],0)+
ISNULL([TERMINAL BILLING],0)+
ISNULL([FDGL Clawback],0)+
ISNULL([FDGL CONFIG FEE],0)+
ISNULL([FDGL Commission],0)+
ISNULL([HIGH RISK UNDERWRITING FEE],0)+
ISNULL([MSC ADJUSTMENT],0)+
ISNULL([CREDIT ADJUSTMENT],0)+
ISNULL([EBT CREDIT ADJUSTMENT],0)+
ISNULL([MAESTRO CHARGEBACK REVERSAL CR],0)+
ISNULL([MAESTRO CHARGEBACK CREDIT],0)+
ISNULL([MAESTRO CHARGEBACK DEBIT],0)+
ISNULL([MINIMUM ANNUAL SHORT FALL],0)+
ISNULL([PAYMENT 1],0)+
ISNULL([RECLAIMED FUNDS],0)+
ISNULL([PAYMENT 3],0)+
ISNULL([CHARGEBACK SUSPENSE],0)+
ISNULL([SUSPENSE CLEARANCE],0)+
ISNULL([ADJUSTMENT],0)+
ISNULL([MASTERCARD ADJUSTMENTS],0)+
ISNULL([TBA],0)+
ISNULL([LETTER FEE],0)+
ISNULL([NON-PAYMENT OF DIRECT DEBIT],0)+
ISNULL([EXCESSIVE CHARGEBACK FEE],0)+
ISNULL([OPERATIONAL GUIDANCE FEE],0)+
ISNULL([COLLECTIONS FEE],0)+
ISNULL([MANAGEMENT FEE],0)+
ISNULL([TERMINATION FEE],0)+
ISNULL([VAT TERMINAL FEE ADJUSTMENT],0)+
ISNULL([TERMINAL FEE ADJUSTMENT],0)+
ISNULL([MAESTRO ADJ. CUSTOMER INQUIRY],0)+
ISNULL([LEGAL ENTITY ADMIN FEE],0)+
ISNULL([CARD NOT PRESENT ADMIN FEE],0)+
ISNULL([ADJUSTMENT],0)+
ISNULL([MISC ADJUSTMENT],0)+
ISNULL([PENDING INVESTIGATION],0)+
ISNULL([MONTHLY RESIDENCE FEE],0)+
ISNULL([Refund Wholesale fee],0) as 'Grand Total'
FROM
(SELECT Fact_Fee_History_2.FDMSAccountNo,
Dim_Fee_Codes.Description,
SUM(Fact_Fee_History_2.Retail_amount_R12) AS [sales]
FROM Dim_Fee_Codes INNER JOIN Fact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_Number
WHERE FDMSAccountNo in (select FDMSAccountNo from #LBG)
and (Dim_Fee_Codes.Description) IS NOT NULL
GROUP BY
Dim_Fee_Codes.Description,
Fact_Fee_History_2.FDMSAccountNo
) as p
PIVOT (MAX([sales]) FOR Description IN (
[REFUND TXN CHARGE],
[AUTHORISATION REQUEST],
[POLLING FEE],
[CHARGEBACK FEE],
[ANNUAL MEMBERSHIP FEE],
[MMV MONTHLY ACCESS FEE],
[CASHBACK FEE],
[TERMINAL FEE],
[MMV MONTHLY FEE],
[MONTHLY STATEMENT FEE],
[FDMS LINK MONTHLY FEE],
[POS PARTNER],
[SOFTWARE UPGRADE],
[MC AUTO BILLING UPD MTH FEE],
[MONTHLY CLIENTLINE FEE],
[3-TIER MONTHLY SERVICE CHARGE],
[MONTHLY MAINTENANCE FEE],
[JOINING FEE],
[PCI DSS MANAGEMENT FEE],
[PCI DSS MANAGEMENT PM],
[PCI DSS INACTIVITY CHARGE],
[PCI DSS NON COMPLIANCE],
[MC DEBIT INTERCHANGE PLUS],
[MC DEBIT IC PLUS SALES],
[MC DEBIT CHIP INTERCHANGE PLUS],
[MC DEBIT CHIP IC PLUS SALES],
[MAESTRO SALES IC FEE],
[MAESTRO CHIP SALES IC FEE],
[MAESTRO INTERCHANGE FEE],
[MAESTRO CHIP INTERCHANGE FEE],
[SOLO INTERCHANGE FEE],
[SOLO CHIP INTERCHANGE FEE],
[SOLO SALES IC FEE],
[SOLO CHIP SALES IC FEE],
[VISA SALES IC FEE],
[VISA ELECTRON SALES IC FEE],
[VISA CHIP SALES IC FEE],
[VISA PURCH CARD SALES IC FEE],
[VISA DEBIT SALES IC FEE],
[VISA DEBIT CHIP SALES IC FEE],
[VISA INTERCHANGE FEE],
[VISA ELECTRON INTERCHANGE FEE],
[VISA CHIP INTERCHANGE FEE],
[VISA PURCH CARD INTRCHG FEE],
[VISA DEBIT INTERCHANGE FEE],
[VISA DEBIT CHIP INTERCHG FEE],
[MASTERCARD SALES IC FEE],
[MASTERCARD INTERCHANGE FEE],
[MC INTERCHANGE],
[MC CHIP INTERCHANGE FEE],
[MC PURCH CARD INTERCHANGE FEE],
[MC CHIP SALES IC FEE],
[MC PURCH CARD SALES IC FEE],
[DINERS INTERCHANGE FEE],
[M/C+MAESTRO EU ACCEPTANCE FEE],
[M/C INT. RETAIL ACCEPTANCE FEE],
[MAESTRO INT. ACCEPTANCE FEE],
[MC DEBIT SERVICE CHARGE],
[MC DEBIT REFUNDS],
[MC DEBIT SALES TRANSACTION FEE],
[MC DEBIT REFUND TRANSACTION],
[MC DEBIT NQ SERVICE CHARGE],
[MC DEBIT NQ REFUNDS],
[MC DEBIT NQ SALES TRANS FEE],
[MC DEBIT NQ REFUND TRANSACTION],
[MC DEBIT CHIP SERVICE CHARGE],
[MC DEBIT CHIP SALES TRANS FEE],
[MC DEBIT CHIP REFUND TRANS FEE],
[MC DEBIT CHIP NQ SERVICE CHRG],
[MC DBT CHP NQ SALES TRANS],
[VISA NON-QUAL SALES TRANS FEE],
[VISA NON-QUAL REFUNDS TRAN FEE],
[VISA DEBIT NQ SALES TRANS FEE],
[VISA DEBIT NQ REFUND TRANS FEE],
[VISA ELECT DR NQ SALE TRAN FEE],
[VISA ELECT DR NQ RFD TRANS FEE],
[MAESTRO NON-QUAL SLS TRANS FEE],
[SOLO NON-QUAL SALES TRANS FEE],
[VISA CHIP NQ SALES TRANS FEE],
[VISA CHIP NQ REFUNDS TRANS FEE],
[VISA DR CHIP NQ SALES TRAN FEE],
[VISA DR CHIP NQ RFND TRANS FEE],
[MAESTRO CHIP NQ SALES TRANS FE],
[SOLO CHIP NQ SALES TRANS FEE],
[VISA PURCH CARD NQ RFND T/FEE],
[VISA NON-QUAL SERVICE CHARGE],
[VISA NON-QUAL REFUNDS],
[VISA NQ DEBIT SERVICE CHARGE],
[VISA NON-QUAL DEBIT REFUNDS],
[VISA ELECTRON NQ DR SERV CHG],
[VISA ELECTRON NQ DR REFUNDS],
[VISA CHIP NQ SERVICE CHARGE],
[VISA CHIP NON-QUAL REFUNDS],
[VISA DEBIT CHIP NQ SERVICE CHG],
[MAESTRO NON-QUAL SERVICE CHG],
[INTL MSTRO NQ SERVICE CHARGE],
[VISA PURCHASE CARD NQ SRV CHG],
[MASTERCARD NQ SERVICE CHARGE],
[MASTERCARD NON-QUAL REFUNDS],
[MASTERCARD CHIP NQ SRV CHRG],
[MASTERCARD CHIP NQ REFUNDS],
[MC PURCHASE CARD NQ SRV CHRG],
[MASTERCARD NQ SALES TRANS FEE],
[MASTERCARD NQ RFND TRANS FEE],
[MASTERCARD CHIP NQ RFND T/FEE],
[VISA PRCH NQ SRVCHG(EX BUS DR)],
[DINERS SALES TRANS FEE],
[VISA BUS DR CARD NQ SALE T/FEE],
[VISA BUS DR CARD NQ RFND T/FEE],
[VISA PRCH NQ REF TX(EX BUS DR)],
[VISA BUS DR CARD NQ SRV CHG],
[MC NQ SERVICE CHARGE (PREM)],
[MC NON-QUAL REFUNDS (PREM)],
[MC CHIP NQ SRV CHRG (PREM)],
[MONTHLY INTERNET ACCESS FEE],
[VAT INT MTH ACCESS FEE 17.5%],
[TERMINAL RENTAL FEE],
[VAT TERMINAL RENTAL FEE 17.5%],
[VIRTUAL TERM TRANSACTION FEE],
[VAT VIRT TERM TRAN FEE 15.0%],
[MIN VIRTUAL TERMINAL TRAN FEE],
[VAT MIN VIR TERM TRAN FEE15.0%],
[VISA NQSERVICE CHG (CHCD)],
[VISA NON.QUALREFUNDS(CHCD)],
[VISA CHIP NQ SERVICE CHG(CHCD)],
[VISA NON-QUAL SRV CHG EX.CHCD],
[VISA NON-QUAL REFUNDS EX.CHCD],
[VISA CHIP NQ SRV CHG EX.CHCD],
[MC NQ SERVICE CHARGE EX. PREM],
[MC NON-QUAL REFUNDS EX. PREM],
[MC CHIP NQ SRV CHRG EX. PREM],
[MC CHIP NQ REFUNDS EX. PREM],
[VISA SERVICE CHARGE],
[VISA REFUNDS],
[VISA DEBIT SERVICE CHARGE],
[VISA DEBIT REFUNDS],
[VISA ELECTRON DR SERV CHARGE],
[VISA ELECTRON DR REFUNDS],
[MAESTRO SERVICE CHARGE],
[MAESTRO REFUNDS],
[SOLO SERVICE CHARGE],
[SOLO REFUNDS],
[VISA CHIP SERVICE CHARGE],
[VISA CHIP REFUNDS],
[VISA DEBIT CHIP SERVICE CHARGE],
[MAESTRO CHIP SERVICE CHARGE],
[SOLO CHIP SERVICE CHARGE],
[VISA PURCHASE CARD SERV CHRG
[VISA PURCHASE CARD REFUNDS],
[VISA COMMERCIAL CARD SERV CHRG],
[VISA COMMERCIAL CARD REFUNDS],
[VISA SALES TRANS FEE],
[VISA REFUNDS TRANS FEE],
[VISA DEBIT SALES TRANS FEE],
[VISA DEBIT REFUNDS TRANS FEE],
[VISA ELECTRON DR SALE TRAN FEE],
[VISA ELECTRON DR RFD TRANS FEE],
[MAESTRO SALES TRANS FEE],
[MAESTRO REFUNDS TRANS FEE],
[SOLO SALES TRANS FEE],
[SOLO REFUNDS TRANS FEE],
[VISA CHIP SALES TRANS FEE],
[VISA CHIP REFUNDS TRANS FEE],
[VISA DR CHIP SALE TRANS FEE],
[VISA DR CHIP REFUNDS TRANS FEE],
[VISA COMM CARD RFND T/FEE],
[MAESTRO CHIP SALES TRANS FEE],
[MAESTRO CHIP REFUNDS TRANS FEE],
[SOLO CHIP SALES TRANS FEE],
[SOLO CHIP REFUNDS TRANS FEE],
[VISA PURCHASE CARD SALE T/FEE],
[VISA PURCHASE CARD RFND T/FEE],
[INTL MSTRO RFND TRN CNT-ED],
[MASTERCARD SERVICE CHARGE],
[MASTERCARD REFUNDS],
[MASTERCARD CHIP SERVICE CHARGE],
[MASTERCARD CHIP REFUNDS],
[MC PURCHASE CARD SERV CHRG],
[MC PURCHASE CARD REFUNDS],
[MASTERCARD SALES TRANS FEE],
[MASTERCARD REFUNDS TRANS FEE],
[MASTERCARD CHIP SALE TRANS FEE],
[MASTERCARD CHIP RFND TRANS FEE],
[MC PURCHASE CARD SALE T/FEE],
[MC PURCHASE CARD RFND T/FEE],
[MC COMMERCIAL CARD RFND T/FEE],
[MC COMMERCIAL CARD SERV CHRG],
[MC COMMERCIAL CARD REFUNDS],
[VISA PRCH SRV CHG(EX BUS DR)],
[VISA PRCH REFUND (EX BUS DR)],
[VISA BUS DR CARD SERV CHRG],
[VISA SERVICE CHARGE EX.CHCD],
[VISA REFUNDS EX.CHCD],
[VISA CHIP SERVICE CHG EX.CHCD],
[VISA CHIP REFUNDS EX.CHCD],
[VISA BUS DR CARD SALE T/FEE],
[VISA BUS DR CARD RFND T/FEE],
[VISA PRCH SLS T/FEE(EX BUS DR)],
[VISA PRCH REF T/FEE(EX BUS DR)],
[MC SERVICE CHARGE (PREM)],
[MC REFUNDS (PREM)],
[MC CHIP SERVICE CHARGE (PREM)],
[MC CHIP REFUNDS (PREM)],
[MC REFUNDS TRANS FEE (PREM)],
[MC CHIP RFND TRANS FEE (PREM)],
[VISA SERVICE CHARGE(CHCD)],
[VISA SERVICE CHARGE(CHCD) RFND],
[VISA CHIP SERVICE CHARGE(CHCD)],
[VISA PREM RFND T/FEE],
[VISA REFUNDS TRANS FEE EX.CHCD],
[VISA CHIP REFUND T/FEE EX.CHCD],
[MC SERVICE CHARGE EX. PREM],
[MC REFUNDS EX. PREM],
[MC CHIP SERVICE CHG EX. PREM],
[MC CHIP REFUNDS EX. PREM],
[MC REFUNDS TRANS FEE EX. PREM],
[MC CHIP REF TRANS FEE EX.PREM],
[VISA COMM REF T/FEE(EX BUS DR)],
[VISA COMM SERV CHRG(EX BUS DR)],
[VISA COMM REFUNDS (EX BUS DR)],
[MINIMUM MONTHLY CHARGE],
[BOARDING FEE],
[Authorisation Wholesale Fee],
[AMEX],
[CANCELLATION FEE],
[FUNDING ADJUSTMENT],
[DEBIT CARD DEPOSITS],
[INTRCHNG REJ RESOLVED TO MERCH],
[REPRESENTED DD],
[TERMINAL BILLING],
[FDGL Clawback],
[FDGL CONFIG FEE],
[FDGL Commission],
[HIGH RISK UNDERWRITING FEE],
[MSC ADJUSTMENT],
[CREDIT ADJUSTMENT],
[EBT CREDIT ADJUSTMENT],
[MAESTRO CHARGEBACK REVERSAL CR],
[MAESTRO CHARGEBACK CREDIT],
[MAESTRO CHARGEBACK DEBIT],
[MINIMUM ANNUAL SHORT FALL],
[PAYMENT 1],
[RECLAIMED FUNDS],
[PAYMENT 3],
[CHARGEBACK SUSPENSE],
[SUSPENSE CLEARANCE],
[ADJUSTMENT],
[MASTERCARD ADJUSTMENTS],
[TBA],
[LETTER FEE],
[NON-PAYMENT OF DIRECT DEBIT],
[EXCESSIVE CHARGEBACK FEE],
[OPERATIONAL GUIDANCE FEE],
[COLLECTIONS FEE],
[MANAGEMENT FEE],
[TERMINATION FEE],
[VAT TERMINAL FEE ADJUSTMENT],
[TERMINAL FEE ADJUSTMENT],
[MAESTRO ADJ. CUSTOMER INQUIRY],
[LEGAL ENTITY ADMIN FEE],
[CARD NOT PRESENT ADMIN FEE],
[MISC ADJUSTMENT],
[PENDING INVESTIGATION],
[MONTHLY RESIDENCE FEE],
[Refund Wholesale fee]) ) as pvt
|
|
Eliminating Null values within a Pivot
