query-2be069611589a8ca47155c0f935b36f8

rq turtle/ttl

, and it works smoothly. With a couple of other tweaks, here's the final(?) version, again for Morrison:on their ownIt turns out we can simplify this further - the subqueries currently find all start and end dates, and then throw out the ones which are blank. But we can just ask it to find the start/end dates

Use at

https://query.wikidata.org/sparql

PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX bd: <http://www.bigdata.com/rdf#>
SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start (min(?end) as ?end2) where {
  VALUES ?mp { wd:Q335791 } # set MP here
  # find all seat-party-start pairs for each continuing period of office
  { SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?start
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. 
      ?ps pq:P768 ?seat . ?ps pq:P4100 ?party . ?ps pq:P580 ?start. 
      # find all terms of office with seat and party, and their start date
      filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . 
                          # omit any terms which started at a general election
                          ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
                          ?ps0 pq:P4100 ?party . ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . }
                          # and where the MP served for the same party & seat at dissolution in the previous term
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      # labels inside the queries to catch any oddities where seat item changes, but name does not  
  } ORDER BY (?mp) ?start }
  # and all corresponding seat-party-end pairs
  { SELECT distinct ?mp ?mpLabel ?partyLabel ?seatLabel ?end
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842.
      ?ps pq:P768 ?seat . ?ps pq:P4100 ?party . ?ps pq:P582 ?end.
      # find all terms of office with seat and party, and their end date
      filter not exists { ?ps pq:P1534 wd:Q741182 . 
                          # omit any terms which end at dissolution
                          ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term . 
                          ?ps2 pq:P4100 ?party . ?ps2 pq:P768 ?seat . 
                          ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . }
                          # and where the MP comes back for the same party & seat at the next general election
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
      # labels inside the queries to catch any oddities where seat item changes, but name does not  
  } ORDER BY (?mp) ?end }
  # now take our starts as the key, and match each to its appropriate end - the next one along
  # this is the *smallest* end date which is still *larger* than the start date
  # so filter by larger here, and smallest using min in the SELECT clause
  filter(?end > ?start) . # note > not >= 
} group by ?mp ?mpLabel ?partyLabel ?seatLabel ?start order by ?start

Query found at

https://www.wikidata.org/wiki/Wikidata:Request_a_query/Archive/2020/12

graph TD classDef projected fill:lightgreen; classDef literal fill:orange; classDef iri fill:yellow; v5("?elec") v2("?end"):::projected v13("?end2") v3("?mp"):::projected v9("?party") v4("?ps") v6("?ps0") v11("?ps2") v10("?seat") v1("?start"):::projected v8("?term") v7("?term0") v12("?term2") c2(["wd:Q50393121"]):::iri c11(["wd:Q741182"]):::iri c13(["wd:Q16707842"]):::iri c16(["bd:serviceParam"]):::iri c5(["wd:Q15283424"]):::iri c18(["#91;AUTO_LANGUAGE#93;,en"]):::literal f0[["?end > ?start"]] f0 --> v2 f0 --> v1 bind1[/VALUES ?mp/] bind1-->v3 bind10(["wd:Q335791"]) bind10 --> bind1 f2[["not "]] subgraph f2e0["Exists Clause"] e0v1 --"p:qualifier/P1534"--> e0c2 e0v1("?ps"):::projected e0c2(["wd:Q50393121"]):::iri end f2--EXISTS--> f2e0 f2 --> v4 f2 --> c1 f2 --> c2 v4 --"p:qualifier/P1534"--> c2 f3[["not "]] subgraph f3e1["Exists Clause"] e1v1 --"p:qualifier/P2715"--> e1v2 e1v2 --"p:direct/P31"--> e1c3 e1v3 --"p:P39"--> e1v4 e1v4 --"p:statement/P39"--> e1v5 e1v5 --"p:direct/P156"--> e1v6 e1v4 --"p:qualifier/P4100"--> e1v7 e1v4 --"p:qualifier/P768"--> e1v8 e1v4 --"p:qualifier/P1534"--> e1c10 e1v2("?elec"):::projected e1v3("?mp"):::projected e1v7("?party"):::projected e1v1("?ps"):::projected e1v4("?ps0"):::projected e1v8("?seat"):::projected e1v6("?term"):::projected e1v5("?term0"):::projected e1c10(["wd:Q741182"]):::iri e1c3(["wd:Q15283424"]):::iri end f3--EXISTS--> f3e1 f3 --> v4 f3 --> c3 f3 --> v5 f3 --> c4 f3 --> c5 f3 --> v3 f3 --> c6 f3 --> v6 f3 --> c7 f3 --> v7 f3 --> c8 f3 --> v8 f3 --> c9 f3 --> v9 f3 --> c10 f3 --> v10 f3 --> c1 f3 --> c11 v4 --"p:qualifier/P2715"--> v5 v5 --"p:direct/P31"--> c5 v3 --"p:P39"--> v6 v6 --"p:statement/P39"--> v7 v7 --"p:direct/P156"--> v8 v6 --"p:qualifier/P4100"--> v9 v6 --"p:qualifier/P768"--> v10 v6 --"p:qualifier/P1534"--> c11 v3 --"p:P39"--> v4 v4 --"p:statement/P39"--> v8 v8 --"p:direct/P279"--> c13 v4 --"p:qualifier/P768"--> v10 v4 --"p:qualifier/P4100"--> v9 v4 --"p:qualifier/P580"--> v1 subgraph s1["http://wikiba.se/ontology#label"] style s1 stroke-width:4px; c16 --"wikibase:language"--> c18 end f4[["not "]] subgraph f4e2["Exists Clause"] e2v1 --"p:qualifier/P1534"--> e2c2 e2v1("?ps"):::projected e2c2(["wd:Q50393121"]):::iri end f4--EXISTS--> f4e2 f4 --> v4 f4 --> c1 f4 --> c2 v4 --"p:qualifier/P1534"--> c2 f5[["not "]] subgraph f5e3["Exists Clause"] e3v1 --"p:qualifier/P1534"--> e3c2 e3v2 --"p:P39"--> e3v3 e3v3 --"p:statement/P39"--> e3v4 e3v4 --"p:direct/P155"--> e3v5 e3v3 --"p:qualifier/P4100"--> e3v6 e3v3 --"p:qualifier/P768"--> e3v7 e3v3 --"p:qualifier/P2715"--> e3v8 e3v8 --"p:direct/P31"--> e3c10 e3v8("?elec"):::projected e3v2("?mp"):::projected e3v6("?party"):::projected e3v1("?ps"):::projected e3v3("?ps2"):::projected e3v7("?seat"):::projected e3v5("?term"):::projected e3v4("?term2"):::projected e3c2(["wd:Q741182"]):::iri e3c10(["wd:Q15283424"]):::iri end f5--EXISTS--> f5e3 f5 --> v4 f5 --> c1 f5 --> c11 f5 --> v3 f5 --> c6 f5 --> v11 f5 --> c7 f5 --> v12 f5 --> c19 f5 --> v8 f5 --> c9 f5 --> v9 f5 --> c10 f5 --> v10 f5 --> c3 f5 --> v5 f5 --> c4 f5 --> c5 v4 --"p:qualifier/P1534"--> c11 v3 --"p:P39"--> v11 v11 --"p:statement/P39"--> v12 v12 --"p:direct/P155"--> v8 v11 --"p:qualifier/P4100"--> v9 v11 --"p:qualifier/P768"--> v10 v11 --"p:qualifier/P2715"--> v5 v5 --"p:direct/P31"--> c5 v3 --"p:P39"--> v4 v4 --"p:statement/P39"--> v8 v8 --"p:direct/P279"--> c13 v4 --"p:qualifier/P768"--> v10 v4 --"p:qualifier/P4100"--> v9 v4 --"p:qualifier/P582"--> v2 subgraph s1["http://wikiba.se/ontology#label"] style s1 stroke-width:4px; c16 --"wikibase:language"--> c18 end bind7[/"min(?end)"/] v2 --o bind7 bind7 --as--o v13