Revision history for BreakTranscript
Additions:
====Speech QC Queries====
//**Interview** - A query that finds the speeches of type "interview", but the title does not reflect our formatting rules://
//**Interview** - A query that finds speeches from a long time ago with the location as the news show on which they appeared. It's likely these errors will have to be corrected manually. Most are marked as speeches as well://
//**Text** - A query that finds speeches where click here (regardless of capitalization) appears in the speechtext://
//**URL** - A query that finds speeches lacking a URL://
//**URL** - A query that finds speeches lacking a URL, but has one mentioned in the speech text (could be the source)://
//**Dates** - A query that finds speeches lacking a speechdate://
//**Dates** - A query that displays all speeches created in the future (with title and speech text)//
WHERE s.created > NOW()
//**Missing State of the States** - A query that finds speeches by governors between Jan 01 YYYY and March 15 YYYY (in search of State of the State speeches)://
//**Cats and Tags** - A query that finds speeches with a certain tag, but not a certain category (originally used to find speeches with 'sanctions-on-x' without Trade as a category)://
//**Interview** - A query that finds the speeches of type "interview", but the title does not reflect our formatting rules://
//**Interview** - A query that finds speeches from a long time ago with the location as the news show on which they appeared. It's likely these errors will have to be corrected manually. Most are marked as speeches as well://
//**Text** - A query that finds speeches where click here (regardless of capitalization) appears in the speechtext://
//**URL** - A query that finds speeches lacking a URL://
//**URL** - A query that finds speeches lacking a URL, but has one mentioned in the speech text (could be the source)://
//**Dates** - A query that finds speeches lacking a speechdate://
//**Dates** - A query that displays all speeches created in the future (with title and speech text)//
WHERE s.created > NOW()
//**Missing State of the States** - A query that finds speeches by governors between Jan 01 YYYY and March 15 YYYY (in search of State of the State speeches)://
//**Cats and Tags** - A query that finds speeches with a certain tag, but not a certain category (originally used to find speeches with 'sanctions-on-x' without Trade as a category)://
Deletions:
//**Interview** - A query that finds the speeches of type "intervie", but the title does not reflect our formatting rules://
//**Interview** - A query that finds speeches from a long time ago with the location as the news show on which they appeared. It's likely these errors will have to be corrected manually). Most are marked as speeches as well://
//A query that finds speeches where click here (regardless of capitalization) appears in the speechtext://
//A query that finds speeches lacking a URL://
//A query that finds speeches lacking a URL, but has one mentioned in the speech text (could be the source)://
//A query that finds speeches lacking a speechdate://
//A query that finds speeches by governors between Jan 01 YYYY and March 15 YYYY (in search of State of the State speeches)://
//A query that finds speeches with a certain tag, but not a certain category (originally used to find speeches with 'sanctions-on-x' without Trade as a category)://
Additions:
//**Titles** - A query that displays all speeches created after a certain date (with title and speech text)//
Deletions:
Additions:
//**Titles** - A query that displays all speeches after a certain date (with title and speech text)//
SELECT s.speech_id, c.candidate_id, s.title, s.created, s.speechtext
FROM speech s JOIN speech_candidate sc USING (speech_id) JOIN candidate c USING (candidate_id)
WHERE s.created >= '06-15-2015'
ORDER BY s.created DESC
SELECT s.speech_id, c.candidate_id, s.title, s.created, s.speechtext
FROM speech s JOIN speech_candidate sc USING (speech_id) JOIN candidate c USING (candidate_id)
WHERE s.created >= '06-15-2015'
ORDER BY s.created DESC
Additions:
IT can perform global changes if we provide a .csv with zero false positives or other errors. If IT does a global change and we identify errors later, they will be next to impossible to isolate and fix so it's better to be 100% sure before hand.
%%(language-ref)
SELECT s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
FROM speech s
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.url, s.speechdate
%%
//**Thomas** - A query that finds the speeches with thomas.loc.gov URLs, but do not have the type Floor Speech//
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.url
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.location
%%
//**Hearing** - A query that finds speeches of type hearing, but the title does not reflect our formatting rules://
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
//**Interview** - A query that finds speeches from a long time ago with the location as the news show on which they appeared. It's likely these errors will have to be corrected manually). Most are marked as speeches as well://
%%(language-ref)
SELECT speech_id, title, location, speechdate
WHERE location LIKE '%Meet the Press%' OR location LIKE '%60%' OR location LIKE '%FOX%' OR location LIKE '%Hardball%' OR location LIKE '%Morning%' OR location LIKE '%ABC%' OR location LIKE '%CNN%' OR location LIKE '%Larry King%' OR location LIKE '%Late Edition%' OR location LIKE '%This Week%' OR location LIKE '%Hannity%' OR location LIKE '%MSNBC%' OR location LIKE '%CBS%'
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.location, c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.speechdate, c.candidate_id
%%
%%(language-ref)
SELECT s.speechdate, c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id
%%
//A query that finds speeches by governors between Jan 01 YYYY and March 15 YYYY (in search of State of the State speeches)://
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
WHERE o.officetype_id = 'G' AND s.speechtype_id = 1 AND s.speechdate BETWEEN 'YYYY-01-01' AND 'YYYY-03-15'
ORDER BY s.location
%%
%%(language-ref)
SELECT DISTINCT a.speech_id, a.candidate_id, a.title, a.speechdate
WHERE st.tag_id = ########) a
WHERE sc.category_id = ####### AND st.tag_id = #######)
ORDER BY a.speech_id
%%
%%(language-ref)
SELECT s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
FROM speech s
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.url, s.speechdate
%%
//**Thomas** - A query that finds the speeches with thomas.loc.gov URLs, but do not have the type Floor Speech//
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.url
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.location
%%
//**Hearing** - A query that finds speeches of type hearing, but the title does not reflect our formatting rules://
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
//**Interview** - A query that finds speeches from a long time ago with the location as the news show on which they appeared. It's likely these errors will have to be corrected manually). Most are marked as speeches as well://
%%(language-ref)
SELECT speech_id, title, location, speechdate
WHERE location LIKE '%Meet the Press%' OR location LIKE '%60%' OR location LIKE '%FOX%' OR location LIKE '%Hardball%' OR location LIKE '%Morning%' OR location LIKE '%ABC%' OR location LIKE '%CNN%' OR location LIKE '%Larry King%' OR location LIKE '%Late Edition%' OR location LIKE '%This Week%' OR location LIKE '%Hannity%' OR location LIKE '%MSNBC%' OR location LIKE '%CBS%'
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.location, c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.speechdate, c.candidate_id
%%
%%(language-ref)
SELECT s.speechdate, c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.speechdate
%%
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id
%%
//A query that finds speeches by governors between Jan 01 YYYY and March 15 YYYY (in search of State of the State speeches)://
%%(language-ref)
SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
WHERE o.officetype_id = 'G' AND s.speechtype_id = 1 AND s.speechdate BETWEEN 'YYYY-01-01' AND 'YYYY-03-15'
ORDER BY s.location
%%
%%(language-ref)
SELECT DISTINCT a.speech_id, a.candidate_id, a.title, a.speechdate
WHERE st.tag_id = ########) a
WHERE sc.category_id = ####### AND st.tag_id = #######)
ORDER BY a.speech_id
%%
Deletions:
%%(language-ref)SELECT s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.url, s.speechdate%%
//**Thomas** - A query that finds the speeches with thomas.loc.gov URLs, but do not have the type Floor Speech
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.url%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.location%%
//**Hearing** - A query that finds speeches of type hearing, but the title does not reflect our formatting rules:// **Resolved 10/17/2013**
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
//**Interview** - A query that finds speeches from a long time ago with the location as the news show they appeared on (will have to be done manually), most are marked as speeches as well.://
%%(language-ref)SELECT speech_id, title, location, speechdate
WHERE location LIKE '%Meet the Press%' OR location LIKE '%60%' OR location LIKE '%FOX%' OR location LIKE '%Hardball%' OR location LIKE '%Morning%' OR location LIKE '%ABC%' OR location LIKE '%CNN%' OR location LIKE '%Larry King%' OR location LIKE '%Late Edition%' OR location LIKE '%This Week%' OR location LIKE '%Hannity%' OR location LIKE '%MSNBC%' OR location LIKE '%CBS%'%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.location, c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.speechdate, c.candidate_id%%
%%SELECT s.speechdate, c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.speechdate%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id%%
//A query that finds speeches by governors between Jan 01 2013 and March 15 2013 (in search of State of the State speeches)://
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
WHERE o.officetype_id = 'G' AND s.speechtype_id = 1 AND s.speechdate BETWEEN '2013-01-01' AND '2013-03-15'
ORDER BY s.location%%
%%SELECT DISTINCT a.speech_id, a.candidate_id, a.title, a.speechdate
WHERE st.tag_id = Y) a
WHERE sc.category_id = X AND st.tag_id = Y)
ORDER BY a.speech_id%%
Additions:
FROM speech s JOIN speech_candidate sc using (speech_id) JOIN candidate c using (candidate_id)
FROM speech s JOIN speech_candidate sc USING (speech_id) JOIN candidate c USING (candidate_id) JOIN office_candidate oc USING (candidate_id) JOIN office o USING (office_id)
FROM speech s JOIN speech_candidate sc USING (speech_id) JOIN candidate c USING (candidate_id) JOIN office_candidate oc USING (candidate_id) JOIN office o USING (office_id)
Deletions:
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) JOIN office_candidate oc ON (oc.candidate_id = c.candidate_id) JOIN office o ON (o.office_id = oc.office_id)
Additions:
WHERE location LIKE '%Meet the Press%' OR location LIKE '%60%' OR location LIKE '%FOX%' OR location LIKE '%Hardball%' OR location LIKE '%Morning%' OR location LIKE '%ABC%' OR location LIKE '%CNN%' OR location LIKE '%Larry King%' OR location LIKE '%Late Edition%' OR location LIKE '%This Week%' OR location LIKE '%Hannity%' OR location LIKE '%MSNBC%' OR location LIKE '%CBS%'%%
Deletions:
Additions:
//**Interview** - A query that finds speeches from a long time ago with the location as the news show they appeared on (will have to be done manually), most are marked as speeches as well.://
%%(language-ref)SELECT speech_id, title, location, speechdate
FROM speech
WHERE location LIKE '%Meet the Press%' OR location LIKE '%60%' OR location LIKE '%FOX%' OR location LIKE '%Hardball%' OR location LIKE '%Morning%' OR location LIKE '%ABC%' OR location LIKE '%CNN%' OR location LIKE '%Larry King%' OR location LIKE '%Late Edition%' OR location LIKE '%This Week%' OR location LIKE '%Hannity%' OR location LIKE '%MSNBC%' OR location LIKE '%CBS%'
%%(language-ref)SELECT speech_id, title, location, speechdate
FROM speech
WHERE location LIKE '%Meet the Press%' OR location LIKE '%60%' OR location LIKE '%FOX%' OR location LIKE '%Hardball%' OR location LIKE '%Morning%' OR location LIKE '%ABC%' OR location LIKE '%CNN%' OR location LIKE '%Larry King%' OR location LIKE '%Late Edition%' OR location LIKE '%This Week%' OR location LIKE '%Hannity%' OR location LIKE '%MSNBC%' OR location LIKE '%CBS%'
Additions:
Bear in mind some of these queries will return intimidatingly large numbers. **Beware of false positives.** Out of almost 900,000 speeches, errors are to be expected. Depending on the error, it may be possible to give the query to IT who can fix some errors automatically. Others will have to be done manually.
Changes can only be done automatically if there are definitely 0 false positives, otherwise they will be next to impossible to isolate and fix.
//**Interview** - A query that finds the speeches of type "intervie", but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words Issue Position, but the type is not "Issue Position"://
//A query that finds speeches lacking a URL, but has one mentioned in the speech text (could be the source)://
Changes can only be done automatically if there are definitely 0 false positives, otherwise they will be next to impossible to isolate and fix.
//**Interview** - A query that finds the speeches of type "intervie", but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words Issue Position, but the type is not "Issue Position"://
//A query that finds speeches lacking a URL, but has one mentioned in the speech text (could be the source)://
Deletions:
//**Interview** - A query that finds the speeches of type “interviewâ€, but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words Issue Position, but the type is not “Issue Positionâ€://
//A query that finds speeches lacking a URL://, but has one mentioned in the speech text (could be the source)
Revision [11356]
Edited on 2014-08-19 17:03:22 by LeishaD [added lacking a URL, but has URL in text]Additions:
//A query that finds speeches lacking a URL://, but has one mentioned in the speech text (could be the source)
%%SELECT s.speechdate, c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
WHERE s.url = '' AND s.speechtext LIKE '%http://%'
ORDER BY s.speechdate%%
%%SELECT s.speechdate, c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
WHERE s.url = '' AND s.speechtext LIKE '%http://%'
ORDER BY s.speechdate%%
Revision [11355]
Edited on 2014-08-19 16:51:16 by LeishaD [Added speeches w/ a tag, but not a certain cat]Additions:
//A query that finds speeches with a certain tag, but not a certain category (originally used to find speeches with 'sanctions-on-x' without Trade as a category)://
%%SELECT DISTINCT a.speech_id, a.candidate_id, a.title, a.speechdate
FROM
(Select s.speech_id, scan.candidate_id, s.title, s.speechdate
FROM speech_category sc JOIN speech s USING (speech_id) JOIN speech_tag st USING (speech_id) JOIN speech_candidate scan USING (speech_id)
WHERE st.tag_id = Y) a
WHERE a.speech_id NOT IN
(Select s.speech_id
FROM speech_category sc JOIN speech s USING (speech_id) JOIN speech_tag st USING (speech_id)
WHERE sc.category_id = X AND st.tag_id = Y)
ORDER BY a.speech_id%%
%%SELECT DISTINCT a.speech_id, a.candidate_id, a.title, a.speechdate
FROM
(Select s.speech_id, scan.candidate_id, s.title, s.speechdate
FROM speech_category sc JOIN speech s USING (speech_id) JOIN speech_tag st USING (speech_id) JOIN speech_candidate scan USING (speech_id)
WHERE st.tag_id = Y) a
WHERE a.speech_id NOT IN
(Select s.speech_id
FROM speech_category sc JOIN speech s USING (speech_id) JOIN speech_tag st USING (speech_id)
WHERE sc.category_id = X AND st.tag_id = Y)
ORDER BY a.speech_id%%
Revision [11143]
Edited on 2014-07-01 11:59:55 by LeishaD [Added speeches w/ a tag, but not a certain cat]Additions:
//A query that finds speeches where click here (regardless of capitalization) appears in the speechtext://
Deletions:
Additions:
//**Thomas** - A query that finds the speeches with thomas.loc.gov URLs, but do not have the type Floor Speech
//**Thomas** - A query that finds the speeches with thomas.loc.gov URLs, but do not have Washington, DC as the location://
//**Hearing** - A query that finds speeches of type hearing, but the title does not reflect our formatting rules:// **Resolved 10/17/2013**
//**Hearing** - A query that finds speeches with Hearing of the in the title, but the type is not hearing://
//**Interview** - A query that finds the speeches with Transcript in the title, but the type is not interview://
//**Letter** - A query that finds speeches of type letter, but the the title does not reflect our formatting rules://
//**Letter** - A query that finds speeches where the title includes the words letter to, but the type is not letter://
//**Issue Position** - A query that finds speeches of type Issue Position, but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words Issue Position, but the type is not “Issue Positionâ€://
//**Washington, DC** - A query that finds some of the incorrect variations of Washington, DC in the speeches table://
//**Spanish** - A query that finds speeches which include the words prensa, declaracion, discurso that is, probably written in Spanish:// **Alex Coffman went through the results and fixed 95% of the errors found by this query.**
//A query that finds speeches where click here†(regardless of capitalization) appears in the speechtext://
//**Thomas** - A query that finds the speeches with thomas.loc.gov URLs, but do not have Washington, DC as the location://
//**Hearing** - A query that finds speeches of type hearing, but the title does not reflect our formatting rules:// **Resolved 10/17/2013**
//**Hearing** - A query that finds speeches with Hearing of the in the title, but the type is not hearing://
//**Interview** - A query that finds the speeches with Transcript in the title, but the type is not interview://
//**Letter** - A query that finds speeches of type letter, but the the title does not reflect our formatting rules://
//**Letter** - A query that finds speeches where the title includes the words letter to, but the type is not letter://
//**Issue Position** - A query that finds speeches of type Issue Position, but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words Issue Position, but the type is not “Issue Positionâ€://
//**Washington, DC** - A query that finds some of the incorrect variations of Washington, DC in the speeches table://
//**Spanish** - A query that finds speeches which include the words prensa, declaracion, discurso that is, probably written in Spanish:// **Alex Coffman went through the results and fixed 95% of the errors found by this query.**
//A query that finds speeches where click here†(regardless of capitalization) appears in the speechtext://
Deletions:
//**Thomas** - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have “Washington, DC†as the location://
//**Hearing** - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules:// **Resolved 10/17/2013**
//**Hearing** - A query that finds speeches with “Hearing of the†in the title, but the type is not “hearingâ€://
//**Interview** - A query that finds the speeches with “Transcript†in the title, but the type is not “interviewâ€://
//**Letter** - A query that finds speeches of type “letterâ€, but the the title does not reflect our formatting rules://
//**Letter** - A query that finds speeches where the title includes the words “letter toâ€, but the type is not “letterâ€://
//**Issue Position** - A query that finds speeches of type “Issue Positionâ€, but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words “Issue Positionâ€, but the type is not “Issue Positionâ€://
//**Washington, DC** - A query that finds some of the incorrect variations of “Washington, DC†in the speeches table://
//**Spanish** - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, probably written in Spanish:// **Alex Coffman went through the results and fixed 95% of the errors found by this query.**
//A query that finds speeches where “click here†(regardless of capitalization) appears in the speechtext://
Additions:
//**Thomas** - A query that finds speeches with Page or Time breaks://
Deletions:
Additions:
//A query that finds speeches where “click here†(regardless of capitalization) appears in the speechtext://
Deletions:
Additions:
//A query that finds speeches where “click here†(regardless of capitolization) appears in the speechtext://
WHERE s.speechtext ILIKE '%click here%'
WHERE s.speechtext ILIKE '%click here%'
Deletions:
WHERE s.speechtext LIKE '%XXXXXXXXXXXXXX%'
Additions:
//**Hearing** - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules:// **Resolved 10/17/2013**
Deletions:
**Ready to Correct**
**Ready to Correct**
//**Hearing** - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules://
Additions:
WHERE s.url LIKE '%thomas.loc.gov%' AND s.location != 'Washington, DC'
Deletions:
Additions:
**Ready to Correct**
WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.url NOT LIKE '%juan%' AND s.url NOT LIKE '%governor%' AND s.speechtype_id != 14
**Ready to Correct**
WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.url NOT LIKE '%juan%' AND s.url NOT LIKE '%governor%' AND s.speechtype_id != 14
**Ready to Correct**
Deletions:
Additions:
WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.url NOT LIKE '%juan%' AND s.url NOT LIKE '%governor%' AND s.url != 'http://thomas.loc.gov'
Deletions:
Additions:
WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.url NOT LIKE '%juan%' AND s.url NOT LIKE '%barbara%' AND s.location != 'Washington, DC'
Deletions:
Additions:
Bear in mind some of these queries will return intimidatingly large numbers. **Beware of false positives.** Out of almost 800,000 speeches, errors are to be expected. Depending on the error, it may be possible to give the query to IT who can fix some errors automatically. Others will have to be done manually.
Deletions:
Additions:
//**Thomas** - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have the type “Floor Speechâ€://
WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.speechtype_id != 14
ORDER BY s.url%%
WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.speechtype_id != 14
ORDER BY s.url%%
Deletions:
WHERE s.url LIKE '%thomas.loc.gov%' AND s.speechtype_id != 1
Additions:
WHERE s.url LIKE '%homas.%' AND s.url NOT LIKE '%vote%' AND s.url NOT LIKE '%juan%' AND s.url != 'http://thomas.loc.gov'
Deletions:
Additions:
//**Pledges** - A query that finds speeches with the type: "pledge"://
%%(language-ref)SELECT s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.title%%
%%(language-ref)SELECT s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY s.title%%
Deletions:
ORDER BY s.title
Additions:
SELECT s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
WHERE s.speechtype_id = 11
GROUP BY s.speech_id
ORDER BY s.title
WHERE s.speechtype_id = 11
GROUP BY s.speech_id
ORDER BY s.title
Additions:
WHERE s.title NOT LIKE 'Letter to%' AND s.speechtype_id = 2
WHERE s.title LIKE 'Letter to%' AND s.speechtype_id != 2
WHERE s.title LIKE 'Letter to%' AND s.speechtype_id != 2
Deletions:
WHERE s.title LIKE '%Letter to%' AND s.speechtype_id != 2
Additions:
//**Spanish** - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, probably written in Spanish:// **Alex Coffman went through the results and fixed 95% of the errors found by this query.**
Deletions:
Additions:
//**Spanish** - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, probably written in Spanish://**Alex Coffman went through the results and fixed 95% of the errors found by this query.**
Deletions:
**Alex Coffman went through the results and fixed 95% of the errors found by this query.**
Additions:
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
ORDER BY c.candidate_id%%
%%(language-ref)SELECT c.candidate_id, s.speech_id, s.speechdate, s.title, s.url, s.speechtype_id, s.location
Deletions:
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.url, s.location
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.location
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
%%(language-ref)SELECT s.speech_id, c.lastname, s.speechdate, s.title, s.location
Additions:
WHERE s.url LIKE '%thomas.%' AND s.url NOT LIKE '%vote%' AND s.url != 'http://thomas.loc.gov'
ORDER BY s.url, s.speechdate%%
**Alex Coffman went through the results and fixed 95% of the errors found by this query.**
ORDER BY s.url, s.speechdate%%
**Alex Coffman went through the results and fixed 95% of the errors found by this query.**
Deletions:
**As of 08/16/2013 this query has been run, and the errors were corrected by IT. Thanks Ed and Clinton.**
Additions:
//A query that finds speeches lacking a speechdate://
WHERE s.speechdate IS NULL AND s.speechtype_id != 7
WHERE s.speechdate IS NULL AND s.speechtype_id != 7
Additions:
//A query that finds speeches lacking a URL://
WHERE s.url = ''
ORDER BY s.speechdate, c.candidate_id%%
WHERE s.url = ''
ORDER BY s.speechdate, c.candidate_id%%
Additions:
**As of 08/16/2013 this query has been run, and the errors were corrected by IT. Thanks Ed and Clinton.**
Additions:
WHERE s.url LIKE '%omas.l%' AND s.url != 'http://thomas.loc.gov'
Deletions:
Additions:
//A query that finds speeches by governors between Jan 01 2013 and March 15 2013 (in search of State of the State speeches)://
%%(language-ref)SELECT s.speech_id, c.lastname, s.speechdate, s.title, s.location
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) JOIN office_candidate oc ON (oc.candidate_id = c.candidate_id) JOIN office o ON (o.office_id = oc.office_id)
WHERE o.officetype_id = 'G' AND s.speechtype_id = 1 AND s.speechdate BETWEEN '2013-01-01' AND '2013-03-15'
ORDER BY s.location%%
%%(language-ref)SELECT s.speech_id, c.lastname, s.speechdate, s.title, s.location
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id) JOIN office_candidate oc ON (oc.candidate_id = c.candidate_id) JOIN office o ON (o.office_id = oc.office_id)
WHERE o.officetype_id = 'G' AND s.speechtype_id = 1 AND s.speechdate BETWEEN '2013-01-01' AND '2013-03-15'
ORDER BY s.location%%
Additions:
//**Issue Position** - A query that finds speeches where the title includes the words “Issue Positionâ€, but the type is not “Issue Positionâ€://
WHERE s.speechtype_id != 7 AND s.title LIKE '%Issue Position:%'
WHERE s.speechtype_id != 7 AND s.title LIKE '%Issue Position:%'
Deletions:
Additions:
//**Letter** - A query that finds speeches of type “letterâ€, but the the title does not reflect our formatting rules://
Deletions:
Deletions:
WHERE s.speechdate < 'YYYY-MM-DD'
Additions:
//A query that finds speeches where “XXXXXXXXXXXXXX†appears in the speechtext://
Deletions:
Additions:
Bear in mind some of these queries will return intimidatingly large numbers. **Beware of false positives.** Out of over 700,000 speeches, errors are to be expected. Depending on the error, it may be possible to give the query to IT who can fix some errors automatically. Others will have to be done manually.
Deletions:
Additions:
Bear in mind, some of these queries will return intimidatingly large numbers. **Beware of false positives.** Out of over 700,000 speeches, mistakes are to be expected. Depending on the error, it may be possible to give the query to IT who can fix some errors automatically. Others will have to be done manually.
Deletions:
Additions:
//**Thomas** - A query that finds speeches where “XXXXXXXXXXXXXX†appears in the speechtext://
WHERE s.speechtext LIKE '%XXXXXXXXXXXXXX%'
WHERE s.speechtext LIKE '%XXXXXXXXXXXXXX%'
Deletions:
WHERE s.speechtext LIKE '%thomas.loc.gov%'
//**Thomas** - A query that finds speeches where “ORDER OF BUSINESS†appears in the speechtext://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%ORDER OF BUSINESS%'
//**Thomas** - A query that finds speeches where “Is there objection to the request†appears in the speechtext://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%Is there objection to the request%'
Additions:
//**Thomas** - A query that finds speeches where “Is there objection to the request†appears in the speechtext://
Additions:
WHERE s.speechtext LIKE '%Is there objection to the request%'
Additions:
WHERE s.speechtext LIKE '%ORDER OF BUSINESS%'
Deletions:
Additions:
//**Thomas** - A query that finds speeches where “ORDER OF BUSINESS†appears in the speechtext://
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%ORDER OF BUSINESS%%'
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%ORDER OF BUSINESS%%'
Additions:
WHERE s.speechdate < 'YYYY-MM-DD'
Deletions:
Additions:
====Speech Error Queries====
Bear in mind, some of these queries will return intimidatingly large numbers. **Beware of false positives.** Out of over 700,000 speeches, that is to be expected. Depending on the error, it may be possible to give the query to IT who can fix errors automatically. Others will have to be done manually.
Bear in mind, some of these queries will return intimidatingly large numbers. **Beware of false positives.** Out of over 700,000 speeches, that is to be expected. Depending on the error, it may be possible to give the query to IT who can fix errors automatically. Others will have to be done manually.
Deletions:
Bear in mind, some of these queries will return intimidatingly large numbers. Out of over 700,000 speeches, that is to be expected. Depending on the error, it may be possible to give the query to IT who can fix errors automatically. Others will have to be done manually. **Beware of false positives.**
Additions:
SpeechesGuide
[[QueriesForSpeeches Speech Research Queries]]
[[QueriesForSpeeches Speech Research Queries]]
Additions:
[[QueriesForSpeeches Speech Research Queries]]>>This is a list of queries to be run from time to time to analyze the consistency of our data, i.e. to correct errors and standardize formatting. Notice that the SELECT, FROM, and ORDER BY clauses are all roughly the same. The goal is to display specific information in a specific order so that we can systematically identify and correct public statements containing errors.
Deletions:
Additions:
[[QueriesForSpeeches Speeches Research Queries]]>>This is a list of queries to be run from time to time to analyze the consistency of our data, i.e. to correct errors and standardize formatting. Notice that the SELECT, FROM, and ORDER BY clauses are all roughly the same. The goal is to display specific information in a specific order so that we can systematically identify and correct public statements containing errors.
Deletions:
Additions:
[[QuerriesForSpeeches Speeches Research Queries]]>>This is a list of queries to be run from time to time to analyze the consistency of our data, i.e. to correct errors and standardize formatting. Notice that the SELECT, FROM, and ORDER BY clauses are all roughly the same. The goal is to display specific information in a specific order so that we can systematically identify and correct public statements containing errors.
Deletions:
Additions:
>>SpeechesGuide
[[UsefulResearchQueries Useful Research Queries]]
[[BreakTranscript Speeches Error Queries]]>>This is a list of queries to be run from time to time to analyze the consistency of our data, i.e. to correct errors and standardize formatting. Notice that the SELECT, FROM, and ORDER BY clauses are all roughly the same. The goal is to display specific information in a specific order so that we can systematically identify and correct public statements containing errors.
[[UsefulResearchQueries Useful Research Queries]]
[[BreakTranscript Speeches Error Queries]]>>This is a list of queries to be run from time to time to analyze the consistency of our data, i.e. to correct errors and standardize formatting. Notice that the SELECT, FROM, and ORDER BY clauses are all roughly the same. The goal is to display specific information in a specific order so that we can systematically identify and correct public statements containing errors.
Deletions:
No Differences
Deletions:
Deletions:
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
Additions:
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
**...there will definitely be false positives**
Deletions:
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
No Differences
Additions:
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.url, s.location
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.location
ORDER BY s.location, c.candidate_id, s.speechdate%%
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.url, s.location
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.location
ORDER BY s.location, c.candidate_id, s.speechdate%%
Deletions:
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.url, s.location
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.location
ORDER BY s.location, c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
ORDER BY c.candidate_id, s.speechdate
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
ORDER BY c.candidate_id, s.speechdate
Additions:
%%(language-ref)SELECT c.candidate_id, c.lastname, s.speechdate, s.title
ORDER BY c.candidate_id, s.speechdate%%
ORDER BY c.candidate_id, s.speechdate%%
Additions:
====Speeches Error Queries====
Additions:
Bear in mind, some of these queries will return intimidatingly large numbers. Out of over 700,000 speeches, that is to be expected. Depending on the error, it may be possible to give the query to IT who can fix errors automatically. Others will have to be done manually. **Beware of false positives.**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
**there will definitely be false positives**
Deletions:
but there will definitely be false positives
but there will definitely be false positives
but there will definitely be false positives
but there will definitely be false positives
still returns false positives
Additions:
//**Thomas** - A query that finds speeches with “page†or “time†breaks://
//**Thomas** - A query that finds the thomas speeches with incorrect URLs://
//**Thomas** - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have the type “speechâ€://
//**Thomas** - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have “Washington, DC†as the location://
//**Thomas** - A query that finds speeches where “thomas.loc.gov†appears in the speechtext://
//**Hearing** - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules://
//**Hearing** - A query that finds speeches with “Hearing of the†in the title, but the type is not “hearingâ€://
//**Interview** - A query that finds the speeches of type “interviewâ€, but the title does not reflect our formatting rules://
//**Interview** - A query that finds the speeches with “Transcript†in the title, but the type is not “interviewâ€://
//**Letter** - A query that finds speeches of type“letterâ€, but the the title does not reflect our formatting rules://
//**Letter** - A query that finds speeches where the title includes the words “letter toâ€, but the type is not “letterâ€://
//**Issue Position** - A query that finds speeches of type “Issue Positionâ€, but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words “Issue Positionâ€, but the type is not “Issues Positionâ€://
//**Washington, DC** - A query that finds some of the incorrect variations of “Washington, DC†in the speeches table://
//**Speechdate** - A query that finds some speeches with unusual “speechdatesâ€://
//**Spanish** - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, probably written in Spanish://
//**Thomas** - A query that finds the thomas speeches with incorrect URLs://
//**Thomas** - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have the type “speechâ€://
//**Thomas** - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have “Washington, DC†as the location://
//**Thomas** - A query that finds speeches where “thomas.loc.gov†appears in the speechtext://
//**Hearing** - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules://
//**Hearing** - A query that finds speeches with “Hearing of the†in the title, but the type is not “hearingâ€://
//**Interview** - A query that finds the speeches of type “interviewâ€, but the title does not reflect our formatting rules://
//**Interview** - A query that finds the speeches with “Transcript†in the title, but the type is not “interviewâ€://
//**Letter** - A query that finds speeches of type“letterâ€, but the the title does not reflect our formatting rules://
//**Letter** - A query that finds speeches where the title includes the words “letter toâ€, but the type is not “letterâ€://
//**Issue Position** - A query that finds speeches of type “Issue Positionâ€, but the title does not reflect our formatting rules://
//**Issue Position** - A query that finds speeches where the title includes the words “Issue Positionâ€, but the type is not “Issues Positionâ€://
//**Washington, DC** - A query that finds some of the incorrect variations of “Washington, DC†in the speeches table://
//**Speechdate** - A query that finds some speeches with unusual “speechdatesâ€://
//**Spanish** - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, probably written in Spanish://
Deletions:
Thomas - A query that finds the thomas speeches with incorrect URLs:
Thomas - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have the type “speechâ€:
Thomas - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have “Washington, DC†as the location:
Thomas - A query that finds speeches where “thomas.loc.gov†appears in the speechtext:
Hearing - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules:
Hearing - A query that finds speeches with “Hearing of the†in the title, but the type is not “hearingâ€:
Interview - A query that finds the speeches of type “interviewâ€, but the title does not reflect our formatting rules:
Interview - A query that finds the speeches with “Transcript†in the title, but the type is not “interviewâ€:
Letter - A query that finds speeches of type“letterâ€, but the the title does not reflect our formatting rules:
Letter - A query that finds speeches where the title includes the words “letter toâ€, but the type is not “letterâ€:
Issue Position - A query that finds speeches of type “Issue Positionâ€, but the title does not reflect our formatting rules.
Issue Position - A query that finds speeches where the title includes the words “Issue Positionâ€, but the type is not “Issues Positionâ€:
Washington, DC - A query that finds some of the incorrect variations of “Washington, DC†in the speeches table:
Speechdate - A query that finds some speeches with unusual “speechdatesâ€:
Spanish - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, they’re probably written in Spanish:
Additions:
This is a list of queries to be run from time to time to analyze the consistency of our data, i.e. to correct errors and standardize formatting. Notice that the SELECT, FROM, and ORDER BY clauses are all roughly the same. The goal is to display specific information in a specific order so that we can systematically identify and correct public statements containing errors.
Bear in mind, some of these queries will return intimidatingly large numbers. Out of over 700,000 speeches, that is to be expected. Depending on the error, it may be possible to give the query to IT who can fix errors automatically. Others will have to be done manually. Beware of false positives.
If you can think of other queries to improve our data or ways to improve these queries which will improve our data, please send them to me. You don’t even have to write the query yourself, you can just send a suggestion in the form: “we need a query that finds ______ errors in ______ statements.â€
Thomas - A query that finds speeches with “page†or “time†breaks:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%[Page:%' OR s.speechtext LIKE '%[Time:%'
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds the thomas speeches with incorrect URLs:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.url LIKE '%thomas.loc.gov%' AND s.url != 'http://thomas.loc.gov' AND s.url != 'http://thomas.loc.gov/'
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have the type “speechâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.url LIKE '%thomas.loc.gov%' AND s.speechtype_id != 1
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have “Washington, DC†as the location:
SELECT c.candidate_id, c.lastname, s.speechdate, s.url, s.location
FROM speech s JOIN speech_candidate sc USING (speech_id) JOIN candidate c USING (candidate_id)
WHERE s.url LIKE '%thomas.loc.gov%' AND s.location != 'Washington, DC'
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds speeches where “thomas.loc.gov†appears in the speechtext:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%thomas.loc.gov%'
ORDER BY c.candidate_id, s.speechdate
Hearing - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE title NOT LIKE '%Hearing of the%' AND speechtype_id = 13
ORDER BY c.candidate_id, s.speechdate
Hearing - A query that finds speeches with “Hearing of the†in the title, but the type is not “hearingâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE title LIKE '%Hearing of the%' AND speechtype_id != 13
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Interview - A query that finds the speeches of type “interviewâ€, but the title does not reflect our formatting rules:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title NOT LIKE '%Transcript%' AND s.speechtype_id = 3
ORDER BY c.candidate_id, s.speechdate
Interview - A query that finds the speeches with “Transcript†in the title, but the type is not “interviewâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title LIKE '%Transcript%' AND s.speechtype_id != 3
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Letter - A query that finds speeches of type“letterâ€, but the the title does not reflect our formatting rules:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title NOT LIKE '%Letter to%' AND s.speechtype_id = 2
ORDER BY c.candidate_id, s.speechdate
Letter - A query that finds speeches where the title includes the words “letter toâ€, but the type is not “letterâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title LIKE '%Letter to%' AND s.speechtype_id != 2
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Issue Position - A query that finds speeches of type “Issue Positionâ€, but the title does not reflect our formatting rules.
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtype_id = 7 AND s.title NOT LIKE '%Issue Position:%'
ORDER BY c.candidate_id, s.speechdate
Issue Position - A query that finds speeches where the title includes the words “Issue Positionâ€, but the type is not “Issues Positionâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtype_id = 7 AND s.title NOT LIKE '%Issue Position:%'
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Washington, DC - A query that finds some of the incorrect variations of “Washington, DC†in the speeches table:
SELECT c.candidate_id, c.lastname, s.speechdate, s.location
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.location LIKE '%ashingto%' AND s.location LIKE '%D%' AND s.location != 'Washington, DC'
ORDER BY s.location, c.candidate_id, s.speechdate
still returns false positives
Speechdate - A query that finds some speeches with unusual “speechdatesâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechdate < '2000-01-01'
ORDER BY c.candidate_id, s.speechdate
Spanish - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, they’re probably written in Spanish:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%discurso%' OR s.speechtext LIKE '%prensa%' OR s.speechtext LIKE '%declaracion%'
ORDER BY c.candidate_id, s.speechdate
Bear in mind, some of these queries will return intimidatingly large numbers. Out of over 700,000 speeches, that is to be expected. Depending on the error, it may be possible to give the query to IT who can fix errors automatically. Others will have to be done manually. Beware of false positives.
If you can think of other queries to improve our data or ways to improve these queries which will improve our data, please send them to me. You don’t even have to write the query yourself, you can just send a suggestion in the form: “we need a query that finds ______ errors in ______ statements.â€
Thomas - A query that finds speeches with “page†or “time†breaks:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%[Page:%' OR s.speechtext LIKE '%[Time:%'
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds the thomas speeches with incorrect URLs:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.url LIKE '%thomas.loc.gov%' AND s.url != 'http://thomas.loc.gov' AND s.url != 'http://thomas.loc.gov/'
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have the type “speechâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title, s.url, s.speechtype_id
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.url LIKE '%thomas.loc.gov%' AND s.speechtype_id != 1
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds the speeches with “thomas.loc.gov†URLs, but do not have “Washington, DC†as the location:
SELECT c.candidate_id, c.lastname, s.speechdate, s.url, s.location
FROM speech s JOIN speech_candidate sc USING (speech_id) JOIN candidate c USING (candidate_id)
WHERE s.url LIKE '%thomas.loc.gov%' AND s.location != 'Washington, DC'
ORDER BY c.candidate_id, s.speechdate
Thomas - A query that finds speeches where “thomas.loc.gov†appears in the speechtext:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%thomas.loc.gov%'
ORDER BY c.candidate_id, s.speechdate
Hearing - A query that finds speeches of type “hearingâ€, but the title does not reflect our formatting rules:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE title NOT LIKE '%Hearing of the%' AND speechtype_id = 13
ORDER BY c.candidate_id, s.speechdate
Hearing - A query that finds speeches with “Hearing of the†in the title, but the type is not “hearingâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE title LIKE '%Hearing of the%' AND speechtype_id != 13
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Interview - A query that finds the speeches of type “interviewâ€, but the title does not reflect our formatting rules:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title NOT LIKE '%Transcript%' AND s.speechtype_id = 3
ORDER BY c.candidate_id, s.speechdate
Interview - A query that finds the speeches with “Transcript†in the title, but the type is not “interviewâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title LIKE '%Transcript%' AND s.speechtype_id != 3
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Letter - A query that finds speeches of type“letterâ€, but the the title does not reflect our formatting rules:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title NOT LIKE '%Letter to%' AND s.speechtype_id = 2
ORDER BY c.candidate_id, s.speechdate
Letter - A query that finds speeches where the title includes the words “letter toâ€, but the type is not “letterâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.title LIKE '%Letter to%' AND s.speechtype_id != 2
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Issue Position - A query that finds speeches of type “Issue Positionâ€, but the title does not reflect our formatting rules.
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtype_id = 7 AND s.title NOT LIKE '%Issue Position:%'
ORDER BY c.candidate_id, s.speechdate
Issue Position - A query that finds speeches where the title includes the words “Issue Positionâ€, but the type is not “Issues Positionâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.speechtype_id, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtype_id = 7 AND s.title NOT LIKE '%Issue Position:%'
ORDER BY c.candidate_id, s.speechdate
but there will definitely be false positives
Washington, DC - A query that finds some of the incorrect variations of “Washington, DC†in the speeches table:
SELECT c.candidate_id, c.lastname, s.speechdate, s.location
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.location LIKE '%ashingto%' AND s.location LIKE '%D%' AND s.location != 'Washington, DC'
ORDER BY s.location, c.candidate_id, s.speechdate
still returns false positives
Speechdate - A query that finds some speeches with unusual “speechdatesâ€:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechdate < '2000-01-01'
ORDER BY c.candidate_id, s.speechdate
Spanish - A query that finds speeches which include the words “prensa, declaracion, discurso†that is, they’re probably written in Spanish:
SELECT c.candidate_id, c.lastname, s.speechdate, s.title
FROM speech s JOIN speech_candidate sc ON (s.speech_id = sc.speech_id) JOIN candidate c ON (c.candidate_id = sc.candidate_id)
WHERE s.speechtext LIKE '%discurso%' OR s.speechtext LIKE '%prensa%' OR s.speechtext LIKE '%declaracion%'
ORDER BY c.candidate_id, s.speechdate
Deletions:
Additions:
The contents of this page have been moved to the field boxes page. This page is empty and can be used for something else. MichaelM 05/04/2013
Deletions:
Additions:
This page is empty and can be used for something else. MichaelM 05/04/2013
Deletions:
We do not always take full transcripts. If you are not taking a full transcript, use BREAK IN TRANSCRIPT at the beginning of the entry, between statements, and at the end of the entry if the official/candidate does not have the final word. If a press release includes a very long list which disrupts the flow of the official's words and does not help clarify the speaker's words, use BREAK IN TRANSCRIPT.
There should be one space after the official/candidate's final statement and then BREAK IN TRANSCRIPT. If the official/candidate goes on to speak much later in the transcript, there should be one space after the BREAK IN TRANSCRIPT and then the text.
~~Text
~~//space//
~~BREAK IN TRANSCRIPT
~~//space//
~~TEXT
Revision [4064]
Edited on 2010-05-26 11:35:09 by DanielKollar [Info on entering title into text box removed]Deletions:
Revision [1669]
Edited on 2008-12-02 15:53:50 by CassieHerbert [Info on entering title into text box removed]Additions:
There should be one space after the official/candidate's final statement and then BREAK IN TRANSCRIPT. If the official/candidate goes on to speak much later in the transcript, there should be one space after the BREAK IN TRANSCRIPT and then the text.
Deletions:
Revision [1621]
Edited on 2008-12-01 16:20:03 by CassieHerbert [Info on entering title into text box removed]Additions:
~~Title
~~//space//
~~Text
~~//space//
~~BREAK IN TRANSCRIPT
~~//space//
~~TEXT
~~//space//
~~Text
~~//space//
~~BREAK IN TRANSCRIPT
~~//space//
~~TEXT
Deletions:
//space//
Text
//space//
BREAK IN TRANSCRIPT
//space//
TEXT
