Query Help ($50) - Randomize Data for Polling

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query Help ($50) - Randomize Data for Polling

Author

Topic

tswiss
Starting Member

7 Posts

Posted - 2014-09-03 : 01:06:32

I just started on SQL Server 3 days ago as I did everything on Excel spreadsheets before, but this query is too much for me now and I'm trying to start a new polling business.

I have a list of all the voters in the State. I need to:
1) Randomize the phone numbers into groups of 10,000 to poll for Governor of Illinois (Statewide).
2) Randomize the phone numbers into groups of 10,000 to poll for Mayor of Chicago (Citywide).

I would be happy to pay someone $50 to help me with these 2 queries. I will need to run these queries maybe 20-30 times per election cycle, so I can't keep calling the same list over and over.

The data is in 3 tables (Voters, Districts, and VoteHistory). They all have the same SUID (State-assigned Unique ID). About 50% have phone numbers. There are 8.2 million listed voters. The Voter.Status = 'A' (for Active).

I would like each poll to pull from all 102 Counties equal to the proportion that they represent overall. For example, Chicago voters should represent 20% of the voters. (I guess if the data was randomized properly, the results should automatically be proportional).

I would like the telephone lists created from people who voted in at least 2 of the last 6 elections. These are available under VoteHistory.ElectionDate as a date (3/18/2014, 11/06/2012, 3/20/12, 11/02/2010, 2/2/2010, 11/4/2008).

Database Tables (columns):
Voters (SUID, JurisdictionID, LastName, FirstName, Address, City, State, Zip, Status, Telephone, Sex
Districts (SUID, DistrictType, District Name)
VoteHistory (SUID, ElectionDate, ElectionType, Party)

Chicago's JurisdictionID = 105

Here is what I have so far:
SELECT
Voters.LastName
,Voters.FirstName
,Voters.Sex
,Voters.Telephone
,Voters.JurisdictionID
FROM
Voters
INNER JOIN Districts ON Districts.SUID = Voters.SUID
JOIN VoteHistory ON VoteHistory.SUID = Voters.SUID
WHERE
-- Voters.Jurisdiction = 105 (for the Chicago Poll)
Voters.Status = 'A'
and Voters.Telephone is not null

As you can see, I am just beginning and I need some help with complicated WHERE clauses. Thanks...Tom

Date Info:
VoteHistory.ElectionDate = '2014-03-18'
VoteHistory.ElectionDate = '2012-11-06'
VoteHistory.ElectionDate = '2012-03-20'
VoteHistory.ElectionDate = '2010-11-02'
VoteHistory.ElectionDate = '2010-02-02'
VoteHistory.ElectionDate = '2008-11-04'

Alternatively - Any ElectionDate after and including 2008-11-04 where VoteHistory.ElectionType = GP or GE would work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-03 : 01:23:13

(ROW_NUMBER() OVER (ORDER BY NEWID()) - 1) / 10000 AS TelephoneGroup

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA