ES Monthly cc/pp/dd Payment existing>0

PHOTO

Fri Jul 08 2022 12:34:55 GMT+0000 (Coordinated Universal Time)

Saved by @Matt_Stone #gigkpi

select Transaction_Allocation__c.Close_Date__c,COUNT_DISTINCT(Opportunity__r.Id) The_Count,sum(Transaction_Allocation__c.Amount__c) amount,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 

from Transaction_Allocation__c

where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND
Transaction_Allocation__c.Amount__c <0
AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  < 2022-04-01
AND
Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly')
OR

(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = True))

AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal')
AND
Opportunity__r.CampaignId = '7014J000000MQ63QAG'


Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c , Opportunity__r.CurrencyIsoCode

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

Non Monthly DD income

select Transaction_Allocation__c.Close_Date__c,sum(Transaction_Allocation__c.Amount__c) amount,COUNT_DISTINCT(Opportunity__r.Id) The_Count,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 
from Transaction_Allocation__c

where 
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 

AND ((Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal')

AND
Opportunity__r.CiWF_Payment_Method__c = 'Direct Debit'
     
AND
Transaction_Allocation__c.GL_Code__c IN ('50030', '50090'))
AND
((Opportunity__r.RecordTypeId ='012w00000006jGy'
AND
Opportunity__r.Recurring_Donation_Installment_Period__c != 'Monthly')
 
OR 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A')
AND
Opportunity__r.CiWF_Payment_Method__c != 'Paypal')))



Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode

ES Monthly cc/pp/dd Payments existing

select Transaction_Allocation__c.Close_Date__c,COUNT_DISTINCT(Opportunity__r.Id) The_Count,sum(Transaction_Allocation__c.Amount__c) amount,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 

from Transaction_Allocation__c

where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 

AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  < 2022-04-01
AND
Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly')
OR

(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = True))

AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal')
AND
Opportunity__r.CampaignId = '7014J000000MQ63QAG'


Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c , Opportunity__r.CurrencyIsoCode

ES Monthly cc/pp/dd Payment existing>0

select Transaction_Allocation__c.Close_Date__c,COUNT_DISTINCT(Opportunity__r.Id) The_Count,sum(Transaction_Allocation__c.Amount__c) amount,  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 

from Transaction_Allocation__c

where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND
Transaction_Allocation__c.Amount__c <0
AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  < 2022-04-01
AND
Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly')
OR

(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = True))

AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal')
AND
Opportunity__r.CampaignId = '7014J000000MQ63QAG'


Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c , Opportunity__r.CurrencyIsoCode

*Monthly regular gift payments (CC/PP/DD)

select Transaction_Allocation__c.Close_Date__c,
  COUNT_DISTINCT(Opportunity__r.Id) The_Count,
  sum(Transaction_Allocation__c.Amount__c) amount,  
  Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode 
 
from Transaction_Allocation__c
 
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND 
((Opportunity__r.RecordTypeId IN ('012w00000006jGy')
And
Opportunity__r.Date_Recurring_Donation_Established__c  < 2022-04-01
AND
Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly')
OR
 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A') 
AND
Opportunity__r.Direct_Regular_Donor_Start_of_FY__c = True))
 
AND 
Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 Opportunity__r.CiWF_Payment_Method__c IN ('Credit/Debit Card','Direct Debit','Paypal')
 
 
Group by Transaction_Allocation__c.Close_Date__c,Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c , Opportunity__r.CurrencyIsoCode

*GBP International team

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode 

 
from Transaction_Allocation__c
 
where 
Transaction_Allocation__c.GL_Code__c IN ('50140','50180','50181','50270','50260','510141') 
AND
Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged','Posted','Refunded','Failed','Refund','Reversal') 
AND
 Opportunity__r.RecordTypeId NOT IN ('012w00000006jGy','0122X000000ie1A')

AND
((Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 
AND
(Opportunity__r.Pledge_Date__c > 2022-03-31
OR
Opportunity__r.Pledge_Date__c =null))
OR
(Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged') 
AND
Opportunity__r.Pledge_Date__c > 2022-03-31))

 
Group by 
Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

GBP International team 2

select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode 
 
 
from Transaction_Allocation__c
 
where 
Transaction_Allocation__c.GL_Code__c IN ('50140','50180','50181','50270','50260','50141') 
AND
Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged','Posted','Refunded','Failed','Refund','Reversal') 

AND
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 

Group by 
Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode

INCOME AND SPEND SUMMARY: Monthly regular gift income (DD, CC, PayPal)

select
sum(Transaction_Allocation__c.Amount__c) amount, count_Distinct(Opportunity__r.Id) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND Transaction_Allocation__c.GL_Code__c IN ('50030','50090')) 
AND
 
((Opportunity__r.Recurring_Donation_Installment_Period__c = 'Monthly'
 
AND Opportunity__r.RecordTypeId IN ('012w00000006jGy'))
 
OR
 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A')
 
AND Opportunity__r.CiWF_Payment_Method__c IN ('Paypal')))
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SO AGENCT AND PAYROLL

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
where
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
AND
Opportunity__r.CiWF_Payment_Method__c IN ('Standing Order','Agency','Payroll Giving'))
AND
Transaction_Allocation__c.GL_Code__c IN ('50030', '50090')
 AND
   
 Opportunity__r.RecordTypeId IN ('012w00000006jGy','0122X000000ie1A')
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND Non Monthly DD income

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 
where 
Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1 
 
AND ((Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal')
 
AND
Opportunity__r.CiWF_Payment_Method__c = 'Direct Debit'
     
AND
Transaction_Allocation__c.GL_Code__c IN ('50030', '50090'))
AND
((Opportunity__r.RecordTypeId ='012w00000006jGy'
AND
Opportunity__r.Recurring_Donation_Installment_Period__c != 'Monthly')
 
OR 
(Opportunity__r.RecordTypeId IN ('0122X000000ie1A')
AND
Opportunity__r.CiWF_Payment_Method__c != 'Paypal')))
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY Donation (50020)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn','012w00000006jGo')
 
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'Donation (50020)'
)
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Income and Spend Summary Appeal (50050)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn')
 
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'Appeal (50050)'
)
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY:FAV Donation (50060)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn')
 
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'FAV Donation (50060)'
)
 
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY: In Memory Of (IMO) (50170)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
 where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn','012w00000006jGe','0122X000000ie1F')
  
AND
 
Transaction_Allocation__c.TA_GL_Category__c = 'In Memory Of (IMO) (50170)'
)
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND 
 
Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn','012w00000006jGo')
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c IN ('Fundraised Sales (51040)','Book Sales (51050)'
))
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

INCOME AND SPEND SUMMARY: Single Fundraised Sales (51040)+Books(51050)

select
sum(Transaction_Allocation__c.Amount__c) amount, count(Transaction_Allocation__c.Amount__c) The_Count, Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c, Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c) Months,  CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c) Years
 
from Transaction_Allocation__c
where 
(Transaction_Allocation__c.Close_Date__c  > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c  < NEXT_N_DAYS:1) 
AND
(Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal') 
 
AND 
 
Opportunity__r.RecordTypeId IN ('012w0000000MQol','012w0000000hbgn')
 
AND
 
Transaction_Allocation__c.TA_GL_Category__c IN ('Fundraised Sales (51040)','Book Sales (51050)'
))
 
Group by Opportunity__r.CiWF_Payment_Method__c, Opportunity__r.Country_of_Ownership__c,Opportunity__r.CurrencyIsoCode, CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)

Browse more snippets >>