Database query for final passage vote

[I’m working out of the postgres db restored from a recent pg_dump]

What is the best way to identify the vote id for the final passage vote for all bills (that made it to a floor vote)? Here is the query I originally used:

select * from opencivicdata_voteevent where motion_classification='{passage}'

However, I’m showing over 50,000 bills that have multiple vote events with a classification of ‘passage’. I can usually identify the right id if I manually look at the motion_text column, but this isn’t scalable for my research.

[I’m writing completely from memory; check everything!]

I believe you should see a “passage” vote for both chambers, if the first chamber passed a bill.

I haven’t researched it, but consider picking the last such motion for each bill, chronologically, for each chamber. If you need help with the query, I can.

On the other hand, research might lead you to decide that you want to include all of these votes, even when there are more than 2.

By “research”, I mean sampling a few of the problematic bills, going to the state website, and studying the bill’s history, in order to get a sense of the universe of reasons why multiple “passage” votes could happen. Reasons I can think of (possibly incorrect!):

  • rarely, a chamber votes to reconsider, then votes for passage again
  • commonly the second chamber to vote on a bill amends it, requiring either a conference committee and second “passage” votes in both chambers, or just a second “passage” vote in the first chamber on the amended bill

It’s quite possible there are some scraper bugs in some states leading to incorrect classification, but I’d guess that most of the multiple-passage bills are legitimate.

Thanks for the reply! That’s a good point about the chambers, but I’m seeing some bills with hundreds of voteevents classified as “passage.”

I took your advice and investigated further and it looks like “passage” is being applied to amendment votes, which makes sense why there are so many. In fact, that looks to be the standard treatment for amendments as the “amendment” classification is only used on 738 bills in the entire database.

I tried your suggestion about chronological by sorting on the start_date column. The problem is that some of the data is in a datetime format and others are just dates. The votes that only have dates screw things up, because most of the votes on a bill and its amendments all happen on the same day.

I tried to recover the ordering by joining to opencivicdata_billaction since that actually enumerates the ordering in which each event took place. However, very very few bills in opencivicdata_voteevent have an action id to join on.

Can you provide id’s for a few nasty bills?