7,613
edits
Changes
Jump to navigation
Jump to search
no edit summary
{{Project|Has project output=Data,How-to|Has sponsor=McNair ProjectsCenter
|Has title=VentureXpert Data
|Has owner=Augi Liebster,
|Has project status=Active
}}
The successors to this project include:
*[[VCDB24]], which is the most recent iteration.
*[[VCDB23]]
*[[VCDB20Q3]]
*[[VCDB20H1]]
*[[VCDB4]]
==Relevant Former Projects==
==Location==
My scripts for SDC pulls are located in the E drive in the location:
E:\McNair\Projects\VentureXpertDatabase\ScriptsForSDCExtract
My successfully pulled and normalized files are stored in the location:
E:\McNair\Projects\VentureXpertDatabase\ExtractedDataQ2
My script scripts for loading tables and data is are in one big text file in the location: E:\McNair\Projects\VentureXpertDatabase\vcdb3\LoadingScripts
There are a variety of SQL files in there with self explanatory names. The file that has all of the loading scripts is called LoadingScriptsV1. The folder vcdb2 is there for reference to see what people before had done. ExtractedData is there because I pulled data before July 1st, and Ed asked me to repull the data.
-built from masteronesynth, masterreals
-combines the real and one synth table
==Ranking Tables and Graphs==
This is a slight detour from the creation of VCDB3. However, this is a cool process because you actually get to use the data you've been working with. This process is extensive, but the queries are easy to understand. If you wish to have deeper understanding of the process, read the code. It is located in:
E:McNair\Projects\VentureXpert Database\vcdb3\LoadingScripts\RoundRanking.SQL
First you must create a table that has aggregate round information grouped by cities and round year. Since this is a little difficult to picture, I will attach the code.
DROP TABLE roundleveloutput;
CREATE TABLE roundleveloutput AS SELECT
city, statecode, roundyear AS year,
SUM(rndamtestm*seedflag) AS seedamnt,
SUM(rndamtestm*earlyflag) AS earlyamnt,
SUM(rndamtestm*laterflag) AS lateramnt,
SUM(rndamtestm*growthflag) AS selamnt,
SUM(growthflag*dealflag) AS numseldeals
FROM round GROUP BY city, statecode, roundyear;
--30028
Next create a table that lists the all time SEL amount by city. Keep including the state code since this will ensure that you have the right city. City names are often repeated in different states. Next, create a table which lists unique city, state for every year since 1980. Then, build a table which matches portcos to the city, state, year blowout table for each year they were alive. This table should be relatively large since it lists companies once for every year they were alive up until the present. Then create a table that displays the number of companies alive in a city every year since 1980. Then add in a table that lists all of the information you have built in tables previously based on city, state, year. Also add in population. Then you can run the ranking queries.
For states follow the same general process but group by states not cities and states.
If this explanation was not enough for you (it was not meant to be in depth) go to the location defined above and read the actual code. With the description I have given, you should be able to piece together what each query does.
==Master Tables==
