Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Eliminating Null values within a Pivot

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts
Posted - 2012-11-26 : 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

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2012-11-26 : 05:14:22
Hi masond,


Check this link(this is ur previouse post)....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180864

--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts
Posted - 2012-11-26 : 05:27:23
HI Bandi

Posted back in the orginal thread
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site