Data completeness

How complete are Charity Commission returns

Owned by david, visibility: Public

SQL query

select case when extract(month from fin_period_end_date) < 4 
	then concat(extract(year from fin_period_end_date)-1,'-',extract(year from fin_period_end_date))
    else concat(extract(year from fin_period_end_date),'-',extract(year from fin_period_end_date)+1)
    end as "Financial year",
    count(*) filter (where extract(month from fin_period_end_date)=4) as "April",
    count(*) filter (where extract(month from fin_period_end_date)=5) as "May",
    count(*) filter (where extract(month from fin_period_end_date)=6) as "June",
    count(*) filter (where extract(month from fin_period_end_date)=7) as "July",
    count(*) filter (where extract(month from fin_period_end_date)=8) as "August",
    count(*) filter (where extract(month from fin_period_end_date)=9) as "September",
    count(*) filter (where extract(month from fin_period_end_date)=10) as "October",
    count(*) filter (where extract(month from fin_period_end_date)=11) as "November",
    count(*) filter (where extract(month from fin_period_end_date)=12) as "December",
    count(*) filter (where extract(month from fin_period_end_date)=1) as "January",
    count(*) filter (where extract(month from fin_period_end_date)=2) as "February",
    count(*) filter (where extract(month from fin_period_end_date)=3) as "March",
count(*) as "All data"
from charity_ccewcharityarparta
where total_gross_income is not null
group by "Financial year"

17 rows

Financial year	April	May	June	July	August	September	October	November	December	January	February	March	All data
2006-2007	0	0	0	0	0	0	0	0	0	6	12	462	480
2007-2008	20	9	25	53	116	23	10	4	120	7	10	470	867
2008-2009	24	8	19	52	138	31	5	5	112	128	99	2,890	3,511
2009-2010	333	102	251	261	651	323	159	76	1,053	109	96	2,806	6,220
2010-2011	302	114	215	238	629	230	110	42	690	57	28	1,523	4,178
2011-2012	102	39	79	92	209	100	43	23	366	45	32	1,322	2,452
2012-2013	114	44	106	111	229	94	56	31	426	460	372	7,375	9,418
2013-2014	1,782	452	978	926	2,583	1,108	549	298	4,486	475	395	7,479	21,511
2014-2015	1,895	463	1,069	976	2,663	1,126	528	283	4,648	495	387	7,343	21,876
2015-2016	1,865	453	1,099	996	2,654	1,109	525	287	4,753	240	183	5,304	19,468
2016-2017	1,053	298	662	737	2,021	703	339	206	3,288	343	244	6,970	16,864
2017-2018	1,735	994	2,844	3,283	8,912	3,708	1,926	1,108	25,079	1,690	1,492	33,156	85,927
2018-2019	7,309	1,962	4,801	4,420	10,965	4,310	2,149	1,116	24,853	1,623	1,475	33,127	98,110
2019-2020	7,323	2,001	4,903	4,463	11,038	4,422	2,193	1,136	25,624	1,699	1,534	33,614	99,950
2020-2021	7,464	2,038	4,969	4,183	9,777	4,263	2,216	1,152	25,765	1,663	1,520	33,090	98,100
2021-2022	7,320	1,920	4,425	3,647	8,695	4,002	2,159	1,124	25,445	1,634	1,460	31,747	93,578
2022-2023	6,868	1,170	2,363	1,401	2,535	840	351	104	1,092	0	0	0	16,724

Copy and export data

Financial year	April	May	June	July	August	September	October	November	December	January	February	March	All data
2006-2007	0	0	0	0	0	0	0	0	0	6	12	462	480
2007-2008	20	9	25	53	116	23	10	4	120	7	10	470	867
2008-2009	24	8	19	52	138	31	5	5	112	128	99	2890	3511
2009-2010	333	102	251	261	651	323	159	76	1053	109	96	2806	6220
2010-2011	302	114	215	238	629	230	110	42	690	57	28	1523	4178
2011-2012	102	39	79	92	209	100	43	23	366	45	32	1322	2452
2012-2013	114	44	106	111	229	94	56	31	426	460	372	7375	9418
2013-2014	1782	452	978	926	2583	1108	549	298	4486	475	395	7479	21511
2014-2015	1895	463	1069	976	2663	1126	528	283	4648	495	387	7343	21876
2015-2016	1865	453	1099	996	2654	1109	525	287	4753	240	183	5304	19468
2016-2017	1053	298	662	737	2021	703	339	206	3288	343	244	6970	16864
2017-2018	1735	994	2844	3283	8912	3708	1926	1108	25079	1690	1492	33156	85927
2018-2019	7309	1962	4801	4420	10965	4310	2149	1116	24853	1623	1475	33127	98110
2019-2020	7323	2001	4903	4463	11038	4422	2193	1136	25624	1699	1534	33614	99950
2020-2021	7464	2038	4969	4183	9777	4263	2216	1152	25765	1663	1520	33090	98100
2021-2022	7320	1920	4425	3647	8695	4002	2159	1124	25445	1634	1460	31747	93578
2022-2023	6868	1170	2363	1401	2535	840	351	104	1092	0	0	0	16724

Duration: 810.38ms

SQL query

select case when extract(month from fin_period_end_date) < 4 
	then concat(extract(year from fin_period_end_date)-1,'-',extract(year from fin_period_end_date))
    else concat(extract(year from fin_period_end_date),'-',extract(year from fin_period_end_date)+1)
    end as "Financial year",
    count(*) filter (where extract(month from fin_period_end_date)=4) as "April",
    count(*) filter (where extract(month from fin_period_end_date)=5) as "May",
    count(*) filter (where extract(month from fin_period_end_date)=6) as "June",
    count(*) filter (where extract(month from fin_period_end_date)=7) as "July",
    count(*) filter (where extract(month from fin_period_end_date)=8) as "August",
    count(*) filter (where extract(month from fin_period_end_date)=9) as "September",
    count(*) filter (where extract(month from fin_period_end_date)=10) as "October",
    count(*) filter (where extract(month from fin_period_end_date)=11) as "November",
    count(*) filter (where extract(month from fin_period_end_date)=12) as "December",
    count(*) filter (where extract(month from fin_period_end_date)=1) as "January",
    count(*) filter (where extract(month from fin_period_end_date)=2) as "February",
    count(*) filter (where extract(month from fin_period_end_date)=3) as "March",
count(*) as "All data"
from charity_ccewcharityannualreturnhistory
group by "Financial year"

21 rows

Financial year	April	May	June	July	August	September	October	November	December	January	February	March	All data
2002-2003	0	0	1	0	0	0	0	0	0	0	0	0	1
2003-2004	0	0	0	0	0	0	0	0	2	1,132	683	8,135	9,952
2004-2005	1,558	312	991	980	2,164	2,306	1,472	352	6,448	1,199	715	9,717	28,214
2005-2006	1,933	409	1,011	1,078	2,408	2,435	1,512	360	6,682	1,205	685	9,622	29,340
2006-2007	1,898	423	1,038	1,114	2,451	2,407	1,401	333	5,897	1,082	634	8,535	27,213
2007-2008	1,717	412	973	1,075	2,461	2,372	1,378	363	6,379	983	642	8,890	27,645
2008-2009	1,807	448	1,011	1,150	2,607	2,430	1,362	361	6,264	663	324	8,213	26,640
2009-2010	1,697	393	728	786	1,831	1,093	546	234	4,239	511	276	5,941	18,275
2010-2011	1,205	305	675	704	1,730	957	471	216	3,659	524	322	6,018	16,786
2011-2012	1,237	342	776	747	1,927	1,074	570	277	4,479	635	413	7,434	19,911
2012-2013	1,537	428	922	869	2,084	1,192	586	278	4,115	595	393	7,137	20,136
2013-2014	1,512	443	942	869	2,182	1,127	583	290	4,075	497	424	7,296	20,240
2014-2015	1,563	471	991	894	2,304	1,148	577	294	4,207	506	427	7,353	20,735
2015-2016	1,657	504	1,095	956	2,411	1,205	580	312	4,639	568	439	7,760	22,126
2016-2017	1,959	511	1,150	1,014	2,646	1,217	617	326	4,733	619	425	8,030	23,247
2017-2018	2,079	1,693	4,915	5,110	14,079	6,787	3,497	1,898	40,830	3,414	2,905	54,829	142,036
2018-2019	13,506	3,453	8,204	7,103	18,191	8,435	4,215	2,115	43,611	3,455	2,909	54,862	170,059
2019-2020	13,565	3,483	8,337	7,136	18,181	8,435	4,175	2,146	43,841	3,490	2,923	54,748	170,460
2020-2021	13,591	3,524	8,502	7,188	18,284	8,424	4,221	2,182	44,165	3,468	2,933	54,705	171,187
2021-2022	13,529	3,554	8,526	7,175	18,146	8,364	4,225	2,182	44,504	3,476	2,930	54,844	171,455
2022-2023	13,575	2,026	3,848	2,237	4,508	1,723	759	247	2,619	0	0	0	31,542

Copy and export data

Financial year	April	May	June	July	August	September	October	November	December	January	February	March	All data
2002-2003	0	0	1	0	0	0	0	0	0	0	0	0	1
2003-2004	0	0	0	0	0	0	0	0	2	1132	683	8135	9952
2004-2005	1558	312	991	980	2164	2306	1472	352	6448	1199	715	9717	28214
2005-2006	1933	409	1011	1078	2408	2435	1512	360	6682	1205	685	9622	29340
2006-2007	1898	423	1038	1114	2451	2407	1401	333	5897	1082	634	8535	27213
2007-2008	1717	412	973	1075	2461	2372	1378	363	6379	983	642	8890	27645
2008-2009	1807	448	1011	1150	2607	2430	1362	361	6264	663	324	8213	26640
2009-2010	1697	393	728	786	1831	1093	546	234	4239	511	276	5941	18275
2010-2011	1205	305	675	704	1730	957	471	216	3659	524	322	6018	16786
2011-2012	1237	342	776	747	1927	1074	570	277	4479	635	413	7434	19911
2012-2013	1537	428	922	869	2084	1192	586	278	4115	595	393	7137	20136
2013-2014	1512	443	942	869	2182	1127	583	290	4075	497	424	7296	20240
2014-2015	1563	471	991	894	2304	1148	577	294	4207	506	427	7353	20735
2015-2016	1657	504	1095	956	2411	1205	580	312	4639	568	439	7760	22126
2016-2017	1959	511	1150	1014	2646	1217	617	326	4733	619	425	8030	23247
2017-2018	2079	1693	4915	5110	14079	6787	3497	1898	40830	3414	2905	54829	142036
2018-2019	13506	3453	8204	7103	18191	8435	4215	2115	43611	3455	2909	54862	170059
2019-2020	13565	3483	8337	7136	18181	8435	4175	2146	43841	3490	2923	54748	170460
2020-2021	13591	3524	8502	7188	18284	8424	4221	2182	44165	3468	2933	54705	171187
2021-2022	13529	3554	8526	7175	18146	8364	4225	2182	44504	3476	2930	54844	171455
2022-2023	13575	2026	3848	2237	4508	1723	759	247	2619	0	0	0	31542

Duration: 1162.38ms

SQL query

select extract(year from fin_period_end_date) as "Calendar year",
    count(*) filter (where extract(month from fin_period_end_date)=1) as "January",
    count(*) filter (where extract(month from fin_period_end_date)=2) as "February",
    count(*) filter (where extract(month from fin_period_end_date)=3) as "March",
    count(*) filter (where extract(month from fin_period_end_date)=4) as "April",
    count(*) filter (where extract(month from fin_period_end_date)=5) as "May",
    count(*) filter (where extract(month from fin_period_end_date)=6) as "June",
    count(*) filter (where extract(month from fin_period_end_date)=7) as "July",
    count(*) filter (where extract(month from fin_period_end_date)=8) as "August",
    count(*) filter (where extract(month from fin_period_end_date)=9) as "September",
    count(*) filter (where extract(month from fin_period_end_date)=10) as "October",
    count(*) filter (where extract(month from fin_period_end_date)=11) as "November",
    count(*) filter (where extract(month from fin_period_end_date)=12) as "December",
count(*) as "All data"
from charity_ccewcharityannualreturnhistory
group by "Calendar year"

21 rows

Calendar year	January	February	March	April	May	June	July	August	September	October	November	December	All data
2002	0	0	0	0	0	1	0	0	0	0	0	0	1
2003	0	0	0	0	0	0	0	0	0	0	0	2	2
2004	1,132	683	8,135	1,558	312	991	980	2,164	2,306	1,472	352	6,448	26,533
2005	1,199	715	9,717	1,933	409	1,011	1,078	2,408	2,435	1,512	360	6,682	29,459
2006	1,205	685	9,622	1,898	423	1,038	1,114	2,451	2,407	1,401	333	5,897	28,474
2007	1,082	634	8,535	1,717	412	973	1,075	2,461	2,372	1,378	363	6,379	27,381
2008	983	642	8,890	1,807	448	1,011	1,150	2,607	2,430	1,362	361	6,264	27,955
2009	663	324	8,213	1,697	393	728	786	1,831	1,093	546	234	4,239	20,747
2010	511	276	5,941	1,205	305	675	704	1,730	957	471	216	3,659	16,650
2011	524	322	6,018	1,237	342	776	747	1,927	1,074	570	277	4,479	18,293
2012	635	413	7,434	1,537	428	922	869	2,084	1,192	586	278	4,115	20,493
2013	595	393	7,137	1,512	443	942	869	2,182	1,127	583	290	4,075	20,148
2014	497	424	7,296	1,563	471	991	894	2,304	1,148	577	294	4,207	20,666
2015	506	427	7,353	1,657	504	1,095	956	2,411	1,205	580	312	4,639	21,645
2016	568	439	7,760	1,959	511	1,150	1,014	2,646	1,217	617	326	4,733	22,940
2017	619	425	8,030	2,079	1,693	4,915	5,110	14,079	6,787	3,497	1,898	40,830	89,962
2018	3,414	2,905	54,829	13,506	3,453	8,204	7,103	18,191	8,435	4,215	2,115	43,611	169,981
2019	3,455	2,909	54,862	13,565	3,483	8,337	7,136	18,181	8,435	4,175	2,146	43,841	170,525
2020	3,490	2,923	54,748	13,591	3,524	8,502	7,188	18,284	8,424	4,221	2,182	44,165	171,242
2021	3,468	2,933	54,705	13,529	3,554	8,526	7,175	18,146	8,364	4,225	2,182	44,504	171,311
2022	3,476	2,930	54,844	13,575	2,026	3,848	2,237	4,508	1,723	759	247	2,619	92,792

Copy and export data

Calendar year	January	February	March	April	May	June	July	August	September	October	November	December	All data
2002	0	0	0	0	0	1	0	0	0	0	0	0	1
2003	0	0	0	0	0	0	0	0	0	0	0	2	2
2004	1132	683	8135	1558	312	991	980	2164	2306	1472	352	6448	26533
2005	1199	715	9717	1933	409	1011	1078	2408	2435	1512	360	6682	29459
2006	1205	685	9622	1898	423	1038	1114	2451	2407	1401	333	5897	28474
2007	1082	634	8535	1717	412	973	1075	2461	2372	1378	363	6379	27381
2008	983	642	8890	1807	448	1011	1150	2607	2430	1362	361	6264	27955
2009	663	324	8213	1697	393	728	786	1831	1093	546	234	4239	20747
2010	511	276	5941	1205	305	675	704	1730	957	471	216	3659	16650
2011	524	322	6018	1237	342	776	747	1927	1074	570	277	4479	18293
2012	635	413	7434	1537	428	922	869	2084	1192	586	278	4115	20493
2013	595	393	7137	1512	443	942	869	2182	1127	583	290	4075	20148
2014	497	424	7296	1563	471	991	894	2304	1148	577	294	4207	20666
2015	506	427	7353	1657	504	1095	956	2411	1205	580	312	4639	21645
2016	568	439	7760	1959	511	1150	1014	2646	1217	617	326	4733	22940
2017	619	425	8030	2079	1693	4915	5110	14079	6787	3497	1898	40830	89962
2018	3414	2905	54829	13506	3453	8204	7103	18191	8435	4215	2115	43611	169981
2019	3455	2909	54862	13565	3483	8337	7136	18181	8435	4175	2146	43841	170525
2020	3490	2923	54748	13591	3524	8502	7188	18284	8424	4221	2182	44165	171242
2021	3468	2933	54705	13529	3554	8526	7175	18146	8364	4225	2182	44504	171311
2022	3476	2930	54844	13575	2026	3848	2237	4508	1723	759	247	2619	92792

Duration: 1024.34ms