Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-22 : 04:19:03
|
Hey guys sorry to bother you so much lately,I am having a little problem with a case statementThis is my query SELECT f.FDMSAccountNo,[Month_end_date] as hst_date_processed,Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'Y' then 'Repeatable'When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'N' then 'Non Repeatable'When Fee_Code in ( 'M4I', '427' ) then 'Non Repeatable'when Fee_Code IN ('999','D76','D85','M02','M05','M10','M13','M15','M16','M18','M1S','M1T','M24','M2H','M2K','M2L','M2N','M4J','M74','XM27') Then 'M Adjustment'When SalesMI_Group1 = 'MSC' then 'MSC'when Fee_Code = 'M01' Then 'M01' end as 'Description',Sum([Retail_amount]) As AmountFROM FDMS.dbo.Fact_Fee_History f INNER JOIN FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Codewhere FDMSAccountNo in (select FDMSAccountNo from #LBG)Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1, d.Repeat_MSCThis query produces the figures i want, however there are thousands of rows due to the month_end_date, what i want to do is have a one-liner per FDMSaccountno where it sums everything up. For instance Fdmsaccountno PCI Repeatable NonRepeatable mAdjustments M01Test 1 2 3 4 5Test2 2 3 4 5 6Can anybody help ? |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-22 : 05:26:20
|
with your query You are getting following resultsetFDMSAccountNo hst_date_processed Description Amount Now You want this format result, right?? Fdmsaccountno PCI Repeatable NonRepeatable mAdjustments M01SELECT Fdmsaccountno, [PCI], [Repeatable], [NonRepeatable], [mAdjustments], [M01]FROM ( Your query ) AS pPIVOT ( sum(amount) FOR [Description] IN ([PCI], [Repeatable], [NonRepeatable], [mAdjustments], [M01]) as pvt Is it your requirement? If not, explore your requirement--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-22 : 05:44:07
|
Hey Bandi Thanks for your help as soon as i posted i realised i could pivot it and thats what i am working on now :) Thanks for the prompt, Il post query up once completed |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-22 : 05:50:42
|
quote: Originally posted by masond Hey Bandi Thanks for your help as soon as i posted i realised i could pivot it and thats what i am working on now :) Thanks for the prompt, Il post query up once completed
Welcome--Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-22 : 06:00:12
|
Hi Bandi this is an adaption of the query i posted, but it provides me with the correct results my pivot is 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 JOINFact_Fee_History_2 ON Dim_Fee_Codes.Fee_Code = Fact_Fee_History_2.Fee_Sequence_NumberWHERE FDMSAccountNo in (select FDMSAccountNo from #LBG)GROUP BY Dim_Fee_Codes.Description,Fact_Fee_History_2.FDMSAccountNo) as pPIVOT (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 |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2012-11-22 : 06:02:25
|
Ps if i want to put this into a temp table, where would i put my into# ? |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-22 : 06:52:12
|
quote: Originally posted by masond Ps if i want to put this into a temp table, where would i put my into# ?
SELECT column_listINTO #tempTableFROM............ --Chandu |
|
|
|
|
|