24.5 - Arbeidsintensiteit PIL & PNIL

Concepten

• Financiële entiteit
• Grootboekpost

Relaties

• deel van
• gaat over
• heeft eigenschap of kenmerk
• heeft eigenschapswaarde

Eigenschappen

• heeft datawaarde
• heeft datum

SPARQL query

Code gekopieerd
...
Kopieer naar klembord
1# Indicator: Zorgkantoren 18.2.0
2# Parameters: $(start_periode), $(eind_periode)
3# Ontologie: versie 2.0.0 of nieuwer
4
5PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
6PREFIX onz-fin: <http://purl.org/ozo/onz-fin#>
7PREFIX onz-g: <http://purl.org/ozo/onz-g#>
8
9
10SELECT *
11WHERE {
12    {
13		SELECT ("Arbeidsintensiteit PIL & PNIL" AS ?Kental ) (IF(sum(?bedrag_sum_P )!= 0,(sum(?bedrag_sum_Q_II)+sum(?bedrag_sum_Q_III)+sum(?bedrag_sum_Q_IV)+sum(?bedrag_sum_Q_V))/sum(?bedrag_sum_P),'ongedefineerd') AS ?waarde)
14		WHERE{
15		    ?grootboek_post 
16		        a onz-fin:Grootboekpost ;
17		        onz-g:partOf ?rubriek ;
18		        onz-g:hasDate ?datum ; 
19		        onz-g:isAbout [
20		            a onz-g:FinancialEntity ;
21		            onz-g:hasQuality / onz-g:hasQualityValue / onz-g:hasDataValue ?geld_bedrag 
22		        ] .
23		    BIND($(start_periode) AS ?start_periode)
24    		BIND($(eind_periode) AS ?eind_periode)
25    		FILTER (?datum >= ?start_periode)
26    		FILTER (?datum <= ?eind_periode)
27		    # P Som der bedrijfsopbrengsten
28		    BIND(IF(?rubriek IN ( 
29		            # P.I Netto omzet
30		            onz-fin:811000,
31		    
32		            # P.II, P.II leeg
33		            # P.IV Overige bedrijfsopbrengsten
34		            onz-fin:821000, onz-fin:822000, onz-fin:825000, onz-fin:826000, onz-fin:827000, onz-fin:828000, onz-fin:829000,
35		            onz-fin:831000, onz-fin:832000, onz-fin:833000, onz-fin:835000, 
36		            onz-fin:891000, onz-fin:919000, onz-fin:920000, onz-fin:930000
37		        ), ?geld_bedrag, 0) AS ?bedrag_sum_P)
38		
39		    #Q.II Kosten uitbesteed werk en andere externe kosten
40		    BIND(IF(?rubriek IN (
41		            onz-fin:417000,
42		            onz-fin:418000, onz-fin:418100, onz-fin:418200
43		        ), ?geld_bedrag, 0) AS ?bedrag_sum_Q_II)
44		    
45		    #Q.III Lonen en salarissen
46		    BIND(IF(?rubriek IN (
47		            onz-fin:411000, onz-fin:411100, onz-fin:411200, onz-fin:411300, onz-fin:411400, onz-fin:411500, onz-fin:411600, onz-fin:411700, 
48		            onz-fin:412000, onz-fin:412100, onz-fin:412200, onz-fin:412300, onz-fin:412400, onz-fin:412500, 
49		            onz-fin:413000, onz-fin:413100, onz-fin:413200, onz-fin:413300, onz-fin:413400, onz-fin:413500, onz-fin:413600, onz-fin:413610, 
50		                onz-fin:413620, onz-fin:413700, onz-fin:414000, onz-fin:414100, onz-fin:414101, onz-fin:414102, onz-fin:414103, onz-fin:414104, 
51		                onz-fin:414110, onz-fin:414111, onz-fin:414112, onz-fin:414113, onz-fin:414114, onz-fin:414200, onz-fin:414202, onz-fin:414203, 
52		                onz-fin:414204, onz-fin:414300, onz-fin:414500, onz-fin:414503, onz-fin:414504, onz-fin:414700, onz-fin:414800, 
53		            onz-fin:415000, onz-fin:416000, 
54		            onz-fin:419000, onz-fin:419100, onz-fin:419101, onz-fin:419200, onz-fin:419500
55		        ), ?geld_bedrag, 0) AS ?bedrag_sum_Q_III)
56		
57		    #Q.IV Sociale lasten
58		    BIND(IF(?rubriek IN (
59		            onz-fin:420000, onz-fin:420100, onz-fin:422100, onz-fin:422300, onz-fin:422400, onz-fin:422410, onz-fin:422500, onz-fin:422900
60		        ), ?geld_bedrag, 0) AS ?bedrag_sum_Q_IV)
61		
62		    #Q.V Pensioenlasten
63		    BIND(IF(?rubriek IN (onz-fin:422600), ?geld_bedrag, 0) AS ?bedrag_sum_Q_V)
64		}
65    }
66    UNION
67    {
68    	SELECT ("Arbeidsintensiteit PIL & PNIL%" AS ?Kental ) (IF(sum(?bedrag_sum_P )!= 0,100*((sum(?bedrag_sum_Q_II)+sum(?bedrag_sum_Q_III)+sum(?bedrag_sum_Q_IV)+sum(?bedrag_sum_Q_V))/sum(?bedrag_sum_P)),'ongedefineerd') AS ?waarde)
69		WHERE{
70			?grootboek_post 
71			    a onz-fin:Grootboekpost ;
72			    onz-g:partOf ?rubriek ;
73			    onz-g:hasDate ?datum ; 
74			    onz-g:isAbout [
75			        a onz-g:FinancialEntity ;
76			        onz-g:hasQuality / onz-g:hasQualityValue / onz-g:hasDataValue ?geld_bedrag 
77			    ] .
78		    BIND($(start_periode) AS ?start_periode)
79    		BIND($(eind_periode) AS ?eind_periode)
80    		FILTER (?datum >= ?start_periode)
81    		FILTER (?datum <= ?eind_periode)
82			# P Som der bedrijfsopbrengsten
83			BIND(IF(?rubriek IN ( 
84			        # P.I Netto omzet
85			        onz-fin:811000,
86			
87			        # P.II, P.II leeg
88			        # P.IV Overige bedrijfsopbrengsten
89			        onz-fin:821000, onz-fin:822000, onz-fin:825000, onz-fin:826000, onz-fin:827000, onz-fin:828000, onz-fin:829000,
90			        onz-fin:831000, onz-fin:832000, onz-fin:833000, onz-fin:835000, 
91			        onz-fin:891000, onz-fin:919000, onz-fin:920000, onz-fin:930000
92			    ), ?geld_bedrag, 0) AS ?bedrag_sum_P)
93			
94			#Q.II Kosten uitbesteed werk en andere externe kosten
95			BIND(IF(?rubriek IN (
96			        onz-fin:417000,
97			        onz-fin:418000, onz-fin:418100, onz-fin:418200
98			    ), ?geld_bedrag, 0) AS ?bedrag_sum_Q_II)
99			
100			#Q.III Lonen en salarissen
101			BIND(IF(?rubriek IN (
102			        onz-fin:411000, onz-fin:411100, onz-fin:411200, onz-fin:411300, onz-fin:411400, onz-fin:411500, onz-fin:411600, onz-fin:411700, 
103			        onz-fin:412000, onz-fin:412100, onz-fin:412200, onz-fin:412300, onz-fin:412400, onz-fin:412500, 
104			        onz-fin:413000, onz-fin:413100, onz-fin:413200, onz-fin:413300, onz-fin:413400, onz-fin:413500, onz-fin:413600, onz-fin:413610, 
105			            onz-fin:413620, onz-fin:413700, onz-fin:414000, onz-fin:414100, onz-fin:414101, onz-fin:414102, onz-fin:414103, onz-fin:414104, 
106			            onz-fin:414110, onz-fin:414111, onz-fin:414112, onz-fin:414113, onz-fin:414114, onz-fin:414200, onz-fin:414202, onz-fin:414203, 
107			            onz-fin:414204, onz-fin:414300, onz-fin:414500, onz-fin:414503, onz-fin:414504, onz-fin:414700, onz-fin:414800, 
108			        onz-fin:415000, onz-fin:416000, 
109			        onz-fin:419000, onz-fin:419100, onz-fin:419101, onz-fin:419200, onz-fin:419500
110			    ), ?geld_bedrag, 0) AS ?bedrag_sum_Q_III)
111			
112			#Q.IV Sociale lasten
113			BIND(IF(?rubriek IN (
114			        onz-fin:420000, onz-fin:420100, onz-fin:422100, onz-fin:422300, onz-fin:422400, onz-fin:422410, onz-fin:422500, onz-fin:422900
115			    ), ?geld_bedrag, 0) AS ?bedrag_sum_Q_IV)
116			
117			#Q.V Pensioenlasten
118			BIND(IF(?rubriek IN (onz-fin:422600), ?geld_bedrag, 0) AS ?bedrag_sum_Q_V)
119		}
120	}
121}