Hack23/cia

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">

    <changeSet author="party_trends" id="2414872417007-321"
        failOnError="true">
        
        <createView
            viewName="view_riksdagen_party_ballot_support_annual_summary">SELECT EXTRACT(ISOYEAR FROM p1.vote_date) as embedded_id_year, p1.embedded_id_party as party, p2.embedded_id_party as embedded_id_other_party, sum( case when p1.party_approved = p2.party_approved then 1 else 0 end) as agree_count, sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) as disagre_count, (sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) *100 )/ count(*) as disagree_percentage,  count(*) as total_ballots
                                                                        FROM view_riksdagen_vote_data_ballot_party_summary p1
                                                                        JOIN view_riksdagen_vote_data_ballot_party_summary p2 ON p1.embedded_id_party &lt;&gt; p2.embedded_id_party and p1.embedded_id_ballot_id = p2.embedded_id_ballot_id and p1.embedded_id_issue = p2.embedded_id_issue group by p1.embedded_id_party,p2.embedded_id_party,EXTRACT(ISOYEAR FROM p1.vote_date)</createView>

        <createView
            viewName="view_riksdagen_party_coalation_against_annual_summary">select quote_literal(upper(replace(replace(against_proposal_parties,&apos; &apos;,&apos;&apos;),&apos;&quot;&apos;,&apos;&apos;))) as embedded_id_party_group_against, substring(rm from 1 for 4) as embedded_id_year,count(*) as total from committee_proposal_data where decision_type=&apos;röstning&apos; and against_proposal_parties is not null and against_proposal_parties !=&apos;&apos; and char_length(against_proposal_parties) &gt; 10  group by quote_literal(upper(replace(replace(against_proposal_parties,&apos; &apos;,&apos;&apos;),&apos;&quot;&apos;,&apos;&apos;))),rm order by rm</createView>

    </changeSet>


    <changeSet author="party_trends" id="2414872417007-322"
        failOnError="true">
        
        <dropView viewName="view_riksdagen_party_ballot_support_annual_summary" /> <createView
            viewName="view_riksdagen_party_ballot_support_annual_summary">SELECT EXTRACT(ISOYEAR FROM p1.vote_date) as embedded_id_year, p1.embedded_id_party as embedded_id_party, p2.embedded_id_party as embedded_id_other_party, sum( case when p1.party_approved = p2.party_approved then 1 else 0 end) as agree_count, sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) as disagre_count, (sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) *100 )/ count(*) as disagree_percentage,  count(*) as total_ballots
                                                                        FROM view_riksdagen_vote_data_ballot_party_summary p1
                                                                        JOIN view_riksdagen_vote_data_ballot_party_summary p2 ON p1.embedded_id_party &lt;&gt; p2.embedded_id_party and p1.embedded_id_ballot_id = p2.embedded_id_ballot_id and p1.embedded_id_issue = p2.embedded_id_issue group by p1.embedded_id_party,p2.embedded_id_party,EXTRACT(ISOYEAR FROM p1.vote_date)</createView>
    </changeSet>

    <changeSet author="party_trends" id="2414872417007-323"
        failOnError="true">
        
        <dropView viewName="view_riksdagen_party_coalation_against_annual_summary" /> <createView
            viewName="view_riksdagen_party_coalation_against_annual_summary">select quote_literal(upper(replace(replace(against_proposal_parties,&apos; &apos;,&apos;&apos;),&apos;&quot;&apos;,&apos;&apos;))) as embedded_id_group_against, substring(rm from 1 for 4) as embedded_id_year,count(*) as total from committee_proposal_data where decision_type=&apos;röstning&apos; and against_proposal_parties is not null and against_proposal_parties !=&apos;&apos; and char_length(against_proposal_parties) &gt; 10  group by quote_literal(upper(replace(replace(against_proposal_parties,&apos; &apos;,&apos;&apos;),&apos;&quot;&apos;,&apos;&apos;))),rm order by rm</createView>

    </changeSet>


    <changeSet author="party_trends" id="2414872417007-324"
        failOnError="true">
        
        <dropView viewName="view_riksdagen_party_ballot_support_annual_summary" /> <createView
            viewName="view_riksdagen_party_ballot_support_annual_summary">SELECT to_char(date_trunc('month',p1.vote_date),'YYYY-MM-DD') as embedded_id_date, p1.embedded_id_party as embedded_id_party, p2.embedded_id_party as embedded_id_other_party, sum( case when p1.party_approved = p2.party_approved then 1 else 0 end) as agree_count, sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) as disagre_count, (sum( case when p1.party_approved = p2.party_approved then 0 else 1 end) *100 )/ count(*) as disagree_percentage,  count(*) as total_ballots
                                                                        FROM view_riksdagen_vote_data_ballot_party_summary p1
                                                                        JOIN view_riksdagen_vote_data_ballot_party_summary p2 ON p1.embedded_id_party &lt;&gt; p2.embedded_id_party and p1.embedded_id_ballot_id = p2.embedded_id_ballot_id and p1.embedded_id_issue = p2.embedded_id_issue group by p1.embedded_id_party,p2.embedded_id_party,to_char(date_trunc('month',p1.vote_date),'YYYY-MM-DD')</createView>
    </changeSet>


</databaseChangeLog>