PostgreSQL database for ScoutBook exports and targeted rank advancement planning

TargetedRankRequirementsReport.sql

-- Create a database in PostgreSQL

-- Apply this SQL to the dabase, using the command:
-- psql -d theDatabaseName -f TargetedRankRequirementsReport.sql

-- Import the Scoutbook exported troop roster into the scouts table first.
-- Import Scoutbook exported rank requirements into the rank_requirements table second.

-- Run various views to get "Reports". The view scout_requirements_targeted_all_scouts is probably of the most
-- interest and can be further filtered to target your needs.

-- The requirements table could probably use a flag to indicate if a rank is teachable or a skill based requirement, 
-- which would further assist in generating a succinct report that can be used for meeting planning.

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.14
-- Dumped by pg_dump version 13.14

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: rank_requirements; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.rank_requirements (
    "BSA Member ID" integer,
    "First Name" character varying(255),
    "Middle Name" character varying(255),
    "Last Name" character varying(255),
    "Advancement Type" character varying(255),
    "Advancement" character varying(255),
    "Version" integer,
    "Date Completed" date,
    "Approved" integer,
    "Awarded" integer,
    "MarkedCompletedBy" character varying(255),
    "MarkedCompletedDate" date,
    "CounselorApprovedBy" character varying(255),
    "CounselorApprovedDate" date,
    "LeaderApprovedBy" character varying(255),
    "LeaderApprovedDate" date,
    "AwardedBy" character varying(255),
    "AwardedDate" date
);


--
-- Name: ranks; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.ranks (
    id smallint NOT NULL,
    rank_name character varying NOT NULL,
    sb_rank_name character varying NOT NULL,
    advancement_type character varying NOT NULL
);


--
-- Name: scouts; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.scouts (
    "UserID" integer,
    "BSA Member ID" integer NOT NULL,
    "First Name" character varying(255),
    "Last Name" character varying(255),
    "Suffix" character varying(255),
    "Nickname" character varying(255),
    "Address 1" character varying(255),
    "Address 2" character varying(255),
    "City" character varying(255),
    "State" character varying(255),
    "Zip" integer,
    "Home Phone" character varying(255),
    "School Grade" integer,
    "School Name" character varying(255),
    "LDS" character varying(255),
    "Swimming Classification" character varying(255),
    "Swimming Classification Date" character varying(255),
    "Unit Number" integer,
    "Unit Type" character varying(255),
    "Date Joined Scouts BSA" character varying(255),
    "Den Type" character varying(255),
    "Den Number" character varying(255),
    "Date Joined Den" character varying(255),
    "Patrol Name" character varying(255),
    "Date Joined Patrol" character varying(255),
    "Parent 1 Email" character varying(255),
    "Parent 2 Email" character varying(255),
    "Parent 3 Email" character varying(255),
    "OA Member Number" character varying(255),
    "OA Election Date" character varying(255),
    "OA Ordeal Date" character varying(255),
    "OA Brotherhood Date" character varying(255),
    "OA Vigil Date" character varying(255),
    "OA Active" character varying(255)
);


--
-- Name: scouts_current_rank; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.scouts_current_rank AS
 SELECT rr."BSA Member ID" AS member_id,
    rr."Last Name" AS last_name,
    rr."First Name" AS first_name,
    r.id AS rank_id,
    r.rank_name
   FROM (public.rank_requirements rr
     JOIN public.ranks r ON (((rr."Advancement")::text = (r.sb_rank_name)::text)))
  WHERE (((rr."Advancement Type")::text = 'Rank'::text) AND (r.id = ( SELECT max(r2.id) AS max
           FROM (public.rank_requirements rr2
             JOIN public.ranks r2 ON (((rr2."Advancement")::text = (r2.sb_rank_name)::text)))
          WHERE (((rr2."Advancement Type")::text = 'Rank'::text) AND (rr2."BSA Member ID" = rr."BSA Member ID")))))
UNION
 SELECT s."BSA Member ID" AS member_id,
    s."Last Name" AS last_name,
    s."First Name" AS first_name,
    NULL::smallint AS rank_id,
    NULL::character varying AS rank_name
   FROM public.scouts s
  WHERE (NOT (s."BSA Member ID" IN ( SELECT rr."BSA Member ID"
           FROM public.rank_requirements rr
          WHERE ((rr."Advancement Type")::text = 'Rank'::text))))
  ORDER BY 4;


--
-- Name: troopwebhost_roster; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.troopwebhost_roster (
    "Adult" character varying(255),
    "Name" character varying(255),
    "Spouse" character varying(255),
    "BSA ID" integer,
    "BSA Registration Ends" character varying(255),
    "Date Joined Scouting" character varying(255),
    "From Pack" integer,
    "Date Joined Unit" character varying(255),
    "Left Unit" character varying(255),
    "Last Aff Date" character varying(255),
    "YPT Last Completed" character varying(255),
    "YPT Expires" character varying(255),
    "Transfer" character varying(255),
    "Multiple" character varying(255),
    "Balance" real,
    "Eagle?" character varying(255),
    "Eagle Date" character varying(255),
    "Leadership" character varying(255),
    "Position Date" character varying(255),
    "Patrol" character varying(255),
    "Crew" character varying(255),
    "Rank" character varying(255),
    "Rank Date" character varying(255),
    "Merit Badges" integer,
    "Next Rank ID" integer,
    "Registered Gender" character varying(255),
    "Age" integer,
    "Born" date,
    "Ethnic" character varying(255),
    "Address" character varying(255),
    "Home Phone" character varying(255),
    "Business Phone" character varying(255),
    "Cell Phone" character varying(255),
    "Cell Phone Carrier" character varying(255),
    "SMS" character varying(255),
    "Email" character varying(255),
    "Email #2" character varying(255),
    "School" character varying(255),
    "Grade" integer,
    "Scout Life" character varying(255),
    "Shirt Size" character varying(255),
    "Medical Part A" character varying(255),
    "Medical Part B" character varying(255),
    "Medical Part C" character varying(255),
    "Other Med Date" character varying(255),
    "Emergency Contact 1" character varying(255),
    "Emergency Contact 1 Phone" character varying(255),
    "Emergency Contact 2" character varying(255),
    "Emergency Contact 2 Phone" character varying(255),
    "Employer" character varying(255),
    "Occupation" character varying(255),
    "Business Address" character varying(255),
    "Driver's License" character varying(255),
    "Make/Model/Year" character varying(255),
    "License Plate" character varying(255),
    "Seat Belts" integer,
    "Trailer Hitch" character varying(255),
    "Liability Per Person" character varying(255),
    "Liability Per Accident" character varying(255),
    "Property Damage" character varying(255),
    "Health Insurance" character varying(255),
    "Dietary Restrictions" character varying(255),
    "Allergies" character varying(255),
    "Swim Level" character varying(255),
    "Swim Date" character varying(255),
    "Camping Nights" integer,
    "Total Hiking Miles" integer,
    "Total Service Hours" real,
    "OA Member" character varying(255),
    "OA Active" character varying(255),
    "OA Election" character varying(255),
    "OA Call Out" character varying(255),
    "OA Ordeal" character varying(255),
    "OA Brotherhood" character varying(255),
    "OA Vigil" character varying(255),
    "OA Vigil Name" character varying(255),
    "OA Notes" character varying(255),
    "Mic-O-Say Tribal Name" character varying(255),
    "FIrst Name" character varying(255),
    "Middle Name" character varying(255),
    "Last Name" character varying(255),
    "Suffix" character varying(255),
    "Mailing Address Line 1" character varying(255),
    "Mailing Address Line 2" character varying(255),
    "City" character varying(255),
    "State" character varying(255),
    "Zip Code" character varying(255),
    "Bio" character varying(512),
    "Scouting Experience" character varying(255)
);


--
-- Name: black_pug_roster; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.black_pug_roster AS
 SELECT s."BSA Member ID" AS "BSA Person ID",
    'youth'::text AS "Youth / Adult",
    s."First Name",
    (twr."Middle Name")::character varying AS "Middle Name",
    s."Last Name",
    s."Suffix",
    s."Parent 1 Email" AS "Email",
    twr."Born" AS "Date of Birth",
    date_part('year'::text, age((CURRENT_DATE)::timestamp with time zone, (twr."Born")::timestamp with time zone)) AS "Age",
    'Male'::text AS "Gender",
    scr.rank_name AS "Scout Rank",
    NULL::character varying AS "Leadership Position",
    (((s."Address 1")::text || ' '::text) || (COALESCE(s."Address 2", ''::character varying))::text) AS "Address",
    s."City",
    s."State",
    s."Zip",
    s."Home Phone",
    twr."Business Phone" AS "Work Phone",
    twr."Cell Phone",
    s."School Name" AS "School",
    NULL::date AS "YPT Date",
    twr."Allergies",
    NULL::character varying AS "Medical Concerns",
    twr."Dietary Restrictions",
    twr."Emergency Contact 1" AS "Emergency Contact Name",
    twr."Emergency Contact 1 Phone" AS "Emergency Contact Phone",
    NULL::character varying AS "Emergency Contact Email",
    NULL::character varying AS "Emergency Contact Relationship",
    NULL::character varying AS "Emergency Contact Additional Info",
    NULL::character varying AS "Parent / Guardian Name",
    s."Parent 1 Email" AS "Parent / Guardian Email",
    NULL::character varying AS "Parent / Guardian Cell Phone",
    NULL::character varying AS "Parent / Guardian Home Phone",
    NULL::character varying AS "Parent / Guardian Work Phone",
    NULL::character varying AS "Parent / Guardian Relationship"
   FROM ((public.scouts s
     JOIN public.scouts_current_rank scr ON ((s."BSA Member ID" = scr.member_id)))
     JOIN public.troopwebhost_roster twr ON ((s."BSA Member ID" = twr."BSA ID")));


--
-- Name: requirement_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.requirement_type (
    id integer NOT NULL,
    type_name character varying
);


--
-- Name: requirements; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.requirements (
    id bigint NOT NULL,
    rank_id smallint NOT NULL,
    type_id integer,
    requirement_number integer NOT NULL,
    life_teachable boolean NOT NULL,
    sm_only boolean NOT NULL,
    requirement_alpha character varying(2),
    twh_id character varying(5) NOT NULL,
    sb_id character varying(5) NOT NULL,
    description character varying(50) NOT NULL
);


--
-- Name: rank_requirements_base; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.rank_requirements_base AS
 SELECT r.id AS rank_id,
    r.rank_name,
    r2.id AS requirement_id,
    r2.twh_id,
    r2.sb_id,
    r2.requirement_number,
    r2.requirement_alpha,
    r2.life_teachable,
    r2.sm_only,
    rt.type_name,
    r2.description AS rank_description,
    rr."BSA Member ID" AS member_id,
    rr."First Name" AS first_name,
    rr."Middle Name" AS middle_name,
    rr."Last Name" AS last_name,
    rr."Version" AS requirement_version,
    rr."Date Completed" AS date_completed,
    rr."Approved" AS approved,
    rr."Awarded" AS awarded,
    rr."MarkedCompletedBy" AS marked_completed_by,
    rr."MarkedCompletedDate" AS marked_completed_date,
    rr."CounselorApprovedBy" AS counselor_approved_by,
    rr."CounselorApprovedDate" AS counselor_approved_date,
    rr."LeaderApprovedBy" AS leader_approved_by,
    rr."LeaderApprovedDate" AS leader_approved_date,
    rr."AwardedBy" AS awarded_by,
    rr."AwardedDate" AS awarded_date,
    s."Patrol Name" AS patrol_name
   FROM ((((public.rank_requirements rr
     JOIN public.ranks r ON (((r.advancement_type)::text = (rr."Advancement Type")::text)))
     JOIN public.requirements r2 ON (((r.id = r2.rank_id) AND ((rr."Advancement")::text = (r2.sb_id)::text))))
     JOIN public.requirement_type rt ON ((r2.type_id = rt.id)))
     JOIN public.scouts s ON ((rr."BSA Member ID" = s."BSA Member ID")));


--
-- Name: requirements_base; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.requirements_base AS
 SELECT r.id AS requirement_id,
    r.rank_id,
    r.twh_id,
    r.sb_id,
    r.description,
    r.requirement_number,
    r.requirement_alpha,
    r.life_teachable,
    r.sm_only,
    r.type_id,
    rt.type_name,
    r2.rank_name,
    r2.advancement_type,
    r2.sb_rank_name
   FROM ((public.requirements r
     JOIN public.requirement_type rt ON ((rt.id = r.type_id)))
     JOIN public.ranks r2 ON ((r2.id = r.rank_id)))
  ORDER BY r.rank_id, r.requirement_number, r.requirement_alpha;


--
-- Name: scouts_next_rank; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.scouts_next_rank AS
 SELECT scr.member_id,
    scr.last_name,
    scr.first_name,
    r.id AS rank_id,
    r.rank_name
   FROM (public.scouts_current_rank scr
     JOIN public.ranks r ON (((scr.rank_id + 1) = r.id)))
UNION
 SELECT scr.member_id,
    scr.last_name,
    scr.first_name,
    (1)::smallint AS rank_id,
    'Scout'::character varying AS rank_name
   FROM public.scouts_current_rank scr
  WHERE (scr.rank_id IS NULL);


--
-- Name: scouts_requirements_for_next_rank; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.scouts_requirements_for_next_rank AS
 SELECT snr.member_id,
    snr.last_name,
    snr.first_name,
    snr.rank_name,
    r.rank_id,
    r.sb_id,
    r.requirement_id,
    r.requirement_number,
    r.requirement_alpha,
    r.type_id,
    r.type_name,
    r.description,
    r.life_teachable,
    r.sm_only
   FROM (public.requirements_base r
     JOIN public.scouts_next_rank snr ON ((r.rank_id = snr.rank_id)))
  WHERE (NOT (r.requirement_id IN ( SELECT rrb.requirement_id
           FROM public.rank_requirements_base rrb
          WHERE (rrb.member_id = snr.member_id))))
  ORDER BY r.rank_id, snr.member_id, r.requirement_number, r.requirement_alpha;


--
-- Name: rank_requirements_targeted; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.rank_requirements_targeted AS
 SELECT srfnr.rank_id,
    srfnr.rank_name,
    srfnr.requirement_id,
    srfnr.sb_id,
    srfnr.requirement_number,
    srfnr.requirement_alpha,
    srfnr.type_id,
    srfnr.type_name,
    srfnr.description,
    srfnr.life_teachable,
    count(*) AS count
   FROM public.scouts_requirements_for_next_rank srfnr
  WHERE (srfnr.sm_only = false)
  GROUP BY srfnr.rank_name, srfnr.rank_id, srfnr.sb_id, srfnr.requirement_id, srfnr.type_id, srfnr.type_name, srfnr.description, srfnr.requirement_number, srfnr.requirement_alpha, srfnr.life_teachable
  ORDER BY (count(*)) DESC, srfnr.rank_id, srfnr.requirement_number, srfnr.requirement_alpha;


--
-- Name: scouts_requirements_outstanding; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.scouts_requirements_outstanding AS
 SELECT sr."BSA Member ID" AS member_id,
    sr."Last Name" AS last_name,
    sr."First Name" AS first_name,
    sr."Patrol Name" AS patrol_name,
    sr.id AS requirement_id,
    sr.rank_id,
    r.rank_name,
    sr.twh_id,
    sr.sb_id,
    sr.requirement_number,
    sr.requirement_alpha,
    rt.type_name,
    sr.description,
    (sr.rank_id = snr.rank_id) AS is_next_rank_requirement
   FROM ((((( SELECT requirements.id,
            requirements.rank_id,
            requirements.twh_id,
            requirements.sb_id,
            requirements.description,
            requirements.requirement_number,
            requirements.requirement_alpha,
            requirements.life_teachable,
            requirements.sm_only,
            requirements.type_id,
            scouts."UserID",
            scouts."BSA Member ID",
            scouts."First Name",
            scouts."Last Name",
            scouts."Suffix",
            scouts."Nickname",
            scouts."Address 1",
            scouts."Address 2",
            scouts."City",
            scouts."State",
            scouts."Zip",
            scouts."Home Phone",
            scouts."School Grade",
            scouts."School Name",
            scouts."LDS",
            scouts."Swimming Classification",
            scouts."Swimming Classification Date",
            scouts."Unit Number",
            scouts."Unit Type",
            scouts."Date Joined Scouts BSA",
            scouts."Den Type",
            scouts."Den Number",
            scouts."Date Joined Den",
            scouts."Patrol Name",
            scouts."Date Joined Patrol",
            scouts."Parent 1 Email",
            scouts."Parent 2 Email",
            scouts."Parent 3 Email",
            scouts."OA Member Number",
            scouts."OA Election Date",
            scouts."OA Ordeal Date",
            scouts."OA Brotherhood Date",
            scouts."OA Vigil Date",
            scouts."OA Active"
           FROM (public.requirements
             CROSS JOIN public.scouts)) sr
     JOIN public.ranks r ON ((r.id = sr.rank_id)))
     JOIN public.scouts_next_rank snr ON ((snr.member_id = sr."BSA Member ID")))
     JOIN public.requirement_type rt ON ((rt.id = sr.type_id)))
     LEFT JOIN public.rank_requirements_base rrb ON (((snr.member_id = rrb.member_id) AND (sr.id = rrb.requirement_id))))
  WHERE ((rrb.rank_id IS NULL) AND (sr.rank_id >= snr.rank_id))
  ORDER BY sr."BSA Member ID", sr.rank_id, sr.requirement_number, sr.requirement_alpha;


--
-- Name: swim_requirements; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.swim_requirements AS
 SELECT r2.id AS rank_id,
    r2.rank_name,
    r.id AS requirement_id,
    r.sb_id,
    rt.type_name,
    r.description
   FROM ((public.requirements r
     JOIN public.ranks r2 ON ((r2.id = r.rank_id)))
     JOIN public.requirement_type rt ON ((rt.id = r.type_id)))
  WHERE (r.type_id = 1)
  ORDER BY r.requirement_number, r.requirement_alpha;


--
-- Name: rank_requirements_targeted_water; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.rank_requirements_targeted_water AS
 SELECT sro.rank_name,
    sro.requirement_number,
    sro.requirement_alpha,
    sro.description,
    count(*) AS count,
    string_agg((((sro.first_name)::text || ' '::text) || (sro.last_name)::text), ', '::text) AS scouts
   FROM (public.swim_requirements sr
     JOIN public.scouts_requirements_outstanding sro ON ((sr.requirement_id = sro.requirement_id)))
  GROUP BY sro.rank_name, sro.requirement_number, sro.requirement_alpha, sro.description
  ORDER BY sro.rank_name, sro.requirement_number, sro.requirement_alpha;


--
-- Name: rank_requirements_targeted_with_scouts; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.rank_requirements_targeted_with_scouts AS
 SELECT srfnr.rank_name,
    srfnr.sb_id,
    srfnr.requirement_id,
    srfnr.type_id,
    srfnr.type_name,
    srfnr.description,
    srfnr.life_teachable,
    count(*) AS count,
    string_agg((((srfnr.first_name)::text || ' '::text) || (srfnr.last_name)::text), ', '::text) AS scout_details_arry
   FROM public.scouts_requirements_for_next_rank srfnr
  WHERE (srfnr.sm_only = false)
  GROUP BY srfnr.rank_name, srfnr.rank_id, srfnr.sb_id, srfnr.requirement_id, srfnr.type_id, srfnr.type_name, srfnr.description, srfnr.requirement_number, srfnr.requirement_alpha, srfnr.life_teachable
  ORDER BY (count(*)) DESC, srfnr.rank_id, srfnr.requirement_number, srfnr.requirement_alpha;


--
-- Name: requirement_type_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

ALTER TABLE public.requirement_type ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.requirement_type_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: requirements_needed_for_next_rank_grouped_by_scout; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.requirements_needed_for_next_rank_grouped_by_scout AS
 SELECT (((srfnr.first_name)::text || ' '::text) || (srfnr.last_name)::text) AS scout_name,
    srfnr.rank_name,
    count(*) AS count,
    string_agg((((((srfnr.sb_id)::text || '-'::text) || (srfnr.type_name)::text) || '-'::text) || (srfnr.description)::text), '
'::text ORDER BY srfnr.sb_id) AS requirements
   FROM public.scouts_requirements_for_next_rank srfnr
  WHERE (srfnr.sm_only = false)
  GROUP BY (((srfnr.first_name)::text || ' '::text) || (srfnr.last_name)::text), srfnr.rank_name
  ORDER BY (count(*));


--
-- Name: scout_requirements_targeted_all_scouts; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.scout_requirements_targeted_all_scouts AS
 SELECT rrt.rank_id,
    rrt.rank_name,
    rrt.sb_id,
    rrt.requirement_id,
    rrt.requirement_number,
    rrt.requirement_alpha,
    rrt.type_id,
    rrt.type_name,
    rrt.description,
    rrt.life_teachable,
    rrt.count,
    ( SELECT string_agg((((sro.first_name)::text || ' '::text) || (sro.last_name)::text), ', '::text) AS string_agg
           FROM public.scouts_requirements_outstanding sro
          WHERE ((sro.requirement_id = rrt.requirement_id) AND (sro.is_next_rank_requirement = true))) AS needs_for_next_rank,
    ( SELECT string_agg((((sro.first_name)::text || ' '::text) || (sro.last_name)::text), ', '::text) AS string_agg
           FROM public.scouts_requirements_outstanding sro
          WHERE ((sro.requirement_id = rrt.requirement_id) AND (sro.is_next_rank_requirement = false))) AS needs
   FROM public.rank_requirements_targeted rrt;


--
-- Name: scoutingorg_ypt_status; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.scoutingorg_ypt_status (
    "District" character varying(255),
    "Sub_District" character varying(255),
    "Program" character varying(255),
    "Unit_Number" integer,
    "Gender_Accepted" character varying(255),
    "Chartered_Org_Name" character varying(255),
    "First_Name" character varying(255),
    "Middle_Name" character varying(255),
    "Last_Name" character varying(255),
    "Member_ID" integer,
    "Position" character varying(255),
    "Status" character varying(255),
    "Effective_Through" date,
    "Youth_Protection_Code" character varying(255),
    "Y01_Completed" date,
    "Y01_Expires" date,
    "Street_Address" character varying(255),
    "City" character varying(255),
    "State" character varying(255),
    "Zip" character varying(255),
    "Email_Address" character varying(255),
    "Phone_Number" character varying(255),
    "Registration_Date" date,
    "Online_Courses" character varying(255)
);


--
-- Data for Name: rank_requirements; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.rank_requirements ("BSA Member ID", "First Name", "Middle Name", "Last Name", "Advancement Type", "Advancement", "Version", "Date Completed", "Approved", "Awarded", "MarkedCompletedBy", "MarkedCompletedDate", "CounselorApprovedBy", "CounselorApprovedDate", "LeaderApprovedBy", "LeaderApprovedDate", "AwardedBy", "AwardedDate") FROM stdin;
\.


--
-- Data for Name: ranks; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.ranks (id, rank_name, sb_rank_name, advancement_type) FROM stdin;
1	Scout	Scout	Scout Rank Requirement
2	Tenderfoot	Tenderfoot	Tenderfoot Rank Requirement
3	Second Class	Second Class	Second Class Rank Requirement
4	First Class	First Class	First Class Rank Requirement
6	Life	Life Scout	Life Scout Rank Requirement
7	Eagle	Eagle Scout	Eagle Scout Rank Requirement
5	Star	Star Scout	Star Scout Rank Requirement
\.


--
-- Data for Name: requirement_type; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.requirement_type (id, type_name) FROM stdin;
2	Camping
3	First Aid
4	Knots & Lashings
5	Knowledge
6	Merit Badge
7	Rank Completion
8	Sharps
9	Safety
10	Fitness
11	Flag
12	Service
13	EDGE
14	Participation
15	Map & Compass
16	Leadership
1	Swim & Watercraft
\.


--
-- Data for Name: requirements; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.requirements (id, rank_id, type_id, requirement_number, life_teachable, sm_only, requirement_alpha, twh_id, sb_id, description) FROM stdin;
0	1	5	1	f	f	a	1.a	1a	Scout Oath, Law, etc.
1	1	5	1	f	t	b	1.b	1b	Scout Spirit
2	1	5	1	f	f	c	1.c	1c	Scout Sign
3	1	5	1	f	f	d	1.d	1d	First Class Badge
4	1	5	1	f	f	e	1.e	1e	Outdoor Code
5	1	5	1	f	f	f	1.f	1f	Pledge of Allegiance
6	1	5	2	f	f	a	2.a	2a	Scout Leadership
7	1	5	2	f	f	b	2.b	2b	Advancement
8	1	5	2	f	f	c	2.c	2c	Ranks
9	1	5	2	f	t	d	2.d	2d	Merit Badges
10	1	5	3	f	f	a	3.a	3a	Patrol Method
11	1	5	3	f	f	b	3.b	3b	Patrol Name, etc.
12	1	4	4	f	f	a	4.a	4a	Knots
13	1	4	4	f	f	b	4.b	4b	Whip and Fuse
14	1	5	5	f	f		5.	5	Pocketknife
15	1	5	6	f	f	a	6.a	6a	Child Safety
16	1	5	6	f	f	b	6.b	6b	Cyber Chip
17	1	7	7	f	t		7.	7	Scoutmaster Conference
20	2	2	1	f	f	a	1.a	1a	Camping Gear
21	2	2	1	f	f	b	1.b	1b	Camp One Night
22	2	5	1	f	f	c	1.c	1c	Outdoor Code
23	2	2	2	f	f	a	2.a	2a	Meal Preparation
24	2	2	2	f	f	b	2.b	2b	Meal Cleanup
25	2	2	2	f	f	c	2.c	2c	Patrol Meal
26	2	4	3	f	f	a	3.a	3a	Square Knot
27	2	4	3	f	f	b	3.b	3b	Two Half-Hitches
28	2	4	3	f	f	c	3.c	3c	Taut-Line Hitch
29	2	8	3	f	f	d	3.d	3d	Knife, Saw and Ax
30	2	3	4	t	f	a	4.a	4a	First Aid
31	2	3	4	t	f	b	4.b	4b	Poisonous Plants
32	2	3	4	f	f	c	4.c	4c	Prevent Injuries
33	2	3	4	f	f	d	4.d	4d	First Aid Kit
34	2	9	5	f	f	a	5.a	5a	Buddy System
35	2	9	5	f	f	b	5.b	5b	Lost
36	2	9	5	f	f	c	5.c	5c	Safe Hiking
37	2	10	6	f	f	a	6.a	6a	Fitness Test 1
38	2	10	6	f	f	b	6.b	6b	Fitness Plan
39	2	10	6	f	f	c	6.c	6c	Fitness Test 2
40	2	11	7	f	f	a	7.a	7a	U.S. Flag
41	2	12	7	f	f	b	7.b	7b	Community Service
42	2	13	8	f	f		8.	8	EDGE Method
43	2	7	9	f	t		9.	9	Scout Spirit
18	2	7	10	f	t		10.	10	Scoutmaster Conference
19	2	7	11	f	t		11.	11	Board of Review
47	3	14	1	f	f	a	1.a	1a	5 Activities
48	3	5	1	f	f	b	1.b	1b	Leave No Trace
49	3	2	1	f	f	c	1.c	1c	Select Camp Site
50	3	5	2	f	f	a	2.a	2a	Explain Cooking Fire Use
51	3	2	2	t	f	b	2.b	2b	Gather Kindling
52	3	2	2	t	f	c	2.c	2c	Light and Extinguish Fire
53	3	5	2	t	f	d	2.d	2d	Explain Stove Use
54	3	2	2	f	f	e	2.e	2e	Cook a Meal
55	3	4	2	f	f	f	2.f	2f	Sheet Bend Knot
56	3	4	2	f	f	g	2.g	2g	Bowline Knot
57	3	15	3	t	f	a	3.a	3a	Demonstrate Compass
58	3	2	3	f	f	b	3.b	3b	5 Mile Hike
59	3	5	3	f	f	c	3.c	3c	Hiking Hazards
60	3	15	3	t	f	d	3.d	3d	Directions w/o Compass
61	3	2	4	f	f		4.	4	Native Animals
62	3	1	5	f	f	a	5.a	5a	Safe Swim Preparation
63	3	1	5	f	f	b	5.b	5b	Beginner Swim Test
64	3	1	5	f	f	c	5.c	5c	Water Rescue Methods
65	3	1	5	f	f	d	5.d	5d	Swimming Rescue Strategy
66	3	3	6	t	f	a	6.a	6a	Demonstrate First Aid
67	3	3	6	t	f	b	6.b	6b	Hurry First Aid Cases
68	3	3	6	f	f	c	6.c	6c	Prevent Injuries
69	3	3	6	f	f	d	6.d	6d	Emergency Response
70	3	3	6	f	f	e	6.e	6e	Vehicle Accident Response
71	3	10	7	f	f	a	7.a	7a	Physical Activity
72	3	10	7	f	f	b	7.b	7b	Discuss Physical Fitness
73	3	10	7	f	f	c	7.c	7c	Drug Abuse Danger
74	3	11	8	f	f	a	8.a	8a	Flag Ceremony
75	3	11	8	f	f	b	8.b	8b	Respect for U.S. Flag
76	3	5	8	f	f	c	8.c	8c	Earn Money
77	3	5	8	f	f	d	8.d	8d	Shop For Best Price
78	3	12	8	f	f	e	8.e	8e	Community Service
79	3	5	9	f	f	a	9.a	9a	Personal Safety
80	3	5	9	f	f	b	9.b	9b	Bullying
44	3	7	10	f	t		10.	10	Scout Spirit
45	3	7	11	f	t		11.	11	Scoutmaster Conference
46	3	7	12	f	t		12.	12	Board of Review
85	4	14	1	f	f	a	1.a	1a	10 Activities
86	4	5	1	f	f	b	1.b	1b	Tread Lightly
87	4	2	2	f	f	a	2.a	2a	Patrol Menu
88	4	2	2	f	f	b	2.b	2b	Food Budget
89	4	2	2	f	f	c	2.c	2c	Pans and Utensils
90	4	2	2	f	f	d	2.d	2d	Safe Food Handling
91	4	2	2	f	f	e	2.e	2e	Patrol Cook
92	4	4	3	t	f	a	3.a	3a	Lashings
93	4	4	3	t	f	b	3.b	3b	Timber Hitch and Clove Hitch
94	4	4	3	t	f	c	3.c	3c	Square, Shear and Diagonal Lashings
95	4	4	3	t	f	d	3.d	3d	Camp Gadget
96	4	15	4	t	f	a	4.a	4a	Map and Compass Hike
97	4	15	4	t	f	b	4.b	4b	GPS
98	4	2	5	f	f	a	5.a	5a	10 Native Plants
99	4	5	5	f	f	b	5.b	5b	Two Weather Forecasts
100	4	5	5	f	f	c	5.c	5c	Three Weather Indicators
101	4	5	5	f	f	d	5.d	5d	Extreme Weather Conditions
102	4	1	6	f	f	a	6.a	6a	Swimmer Test
103	4	1	6	f	f	b	6.b	6b	Safe Trip Afloat
104	4	1	6	f	f	c	6.c	6c	Boat Parts
105	4	1	6	f	f	d	6.d	6d	Body Position in Watercraft
106	4	1	6	f	f	e	6.e	6e	Line Rescue
107	4	3	7	t	f	a	7.a	7a	Bandages
108	4	3	7	t	f	b	7.b	7b	Medical Transport
109	4	3	7	f	f	c	7.c	7c	Heart Attack
110	4	3	7	f	f	d	7.d	7d	Utility Hazards
111	4	3	7	f	f	e	7.e	7e	Home Emergency Plan
112	4	3	7	f	f	f	7.f	7f	Potable Water
113	4	10	8	f	f	a	8.a	8a	Physical Activity
114	4	10	8	f	f	b	8.b	8b	Physical Fitness Plan
115	4	5	9	f	f	a	9.a	9a	Constitutional Rights and Duties
116	4	5	9	f	f	b	9.b	9b	Environmental Issue
117	4	2	9	f	f	c	9.c	9c	Monitor and Reduce Garbage
118	4	12	9	f	f	d	9.d	9d	Community Service
81	4	5	10	f	f		10.	10	Recruit a New Scout
82	4	7	11	f	t		11.	11	Scout Spirit
83	4	7	12	f	t		12.	12	Scoutmaster Conference
84	4	7	13	f	t		13.	13	Board of Review
119	5	14	1	f	f		1.	1	Active Leadership 4 Months
120	5	7	2	f	t		2.	2	Scout Spirit
121	5	6	3	f	t		3.	3	Merit Badges
122	5	12	4	f	f		4.	4	Community Service
123	5	16	5	f	t		5.	5	Leadership
124	5	5	6	f	f		6.	6	Child Safety
125	5	7	7	f	t		7.	7	Scoutmaster Conference
126	5	7	8	f	t		8.	8	Board of Review
127	6	14	1	f	f		1.	1	Active 6 Months
128	6	5	2	f	t		2.	2	Scout Spirit
129	6	6	3	f	t		3.	3	Merit Badges
130	6	12	4	f	f		4.	4	Community Service
131	6	16	5	f	t		5.	5	Leadership
132	6	7	6	f	f		6.	6	EDGE Method
133	6	7	7	f	t		7.	7	Scoutmaster Conference
134	6	7	8	f	t		8.	8	Board of Review
135	7	14	1	f	f		1.	1	Active 6 Months
136	7	7	2	f	t		2.	2	Scout Spirit
137	7	6	3	f	t		3.	3	Merit Badges
138	7	16	4	f	t		4.	4	Leadership
139	7	12	5	f	t		5.	5	Eagle Service Project
140	7	7	6	f	t		6.	6	Scoutmaster Conference
141	7	7	7	f	t		7.	7	Eagle Scout Board of Review
\.


--
-- Data for Name: scoutingorg_ypt_status; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.scoutingorg_ypt_status ("District", "Sub_District", "Program", "Unit_Number", "Gender_Accepted", "Chartered_Org_Name", "First_Name", "Middle_Name", "Last_Name", "Member_ID", "Position", "Status", "Effective_Through", "Youth_Protection_Code", "Y01_Completed", "Y01_Expires", "Street_Address", "City", "State", "Zip", "Email_Address", "Phone_Number", "Registration_Date", "Online_Courses") FROM stdin;
\.


--
-- Data for Name: scouts; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.scouts ("UserID", "BSA Member ID", "First Name", "Last Name", "Suffix", "Nickname", "Address 1", "Address 2", "City", "State", "Zip", "Home Phone", "School Grade", "School Name", "LDS", "Swimming Classification", "Swimming Classification Date", "Unit Number", "Unit Type", "Date Joined Scouts BSA", "Den Type", "Den Number", "Date Joined Den", "Patrol Name", "Date Joined Patrol", "Parent 1 Email", "Parent 2 Email", "Parent 3 Email", "OA Member Number", "OA Election Date", "OA Ordeal Date", "OA Brotherhood Date", "OA Vigil Date", "OA Active") FROM stdin;
\.


--
-- Data for Name: troopwebhost_roster; Type: TABLE DATA; Schema: public; Owner: -
--

COPY public.troopwebhost_roster ("Adult", "Name", "Spouse", "BSA ID", "BSA Registration Ends", "Date Joined Scouting", "From Pack", "Date Joined Unit", "Left Unit", "Last Aff Date", "YPT Last Completed", "YPT Expires", "Transfer", "Multiple", "Balance", "Eagle?", "Eagle Date", "Leadership", "Position Date", "Patrol", "Crew", "Rank", "Rank Date", "Merit Badges", "Next Rank ID", "Registered Gender", "Age", "Born", "Ethnic", "Address", "Home Phone", "Business Phone", "Cell Phone", "Cell Phone Carrier", "SMS", "Email", "Email #2", "School", "Grade", "Scout Life", "Shirt Size", "Medical Part A", "Medical Part B", "Medical Part C", "Other Med Date", "Emergency Contact 1", "Emergency Contact 1 Phone", "Emergency Contact 2", "Emergency Contact 2 Phone", "Employer", "Occupation", "Business Address", "Driver's License", "Make/Model/Year", "License Plate", "Seat Belts", "Trailer Hitch", "Liability Per Person", "Liability Per Accident", "Property Damage", "Health Insurance", "Dietary Restrictions", "Allergies", "Swim Level", "Swim Date", "Camping Nights", "Total Hiking Miles", "Total Service Hours", "OA Member", "OA Active", "OA Election", "OA Call Out", "OA Ordeal", "OA Brotherhood", "OA Vigil", "OA Vigil Name", "OA Notes", "Mic-O-Say Tribal Name", "FIrst Name", "Middle Name", "Last Name", "Suffix", "Mailing Address Line 1", "Mailing Address Line 2", "City", "State", "Zip Code", "Bio", "Scouting Experience") FROM stdin;
\.


--
-- Name: requirement_type_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.requirement_type_id_seq', 16, true);


--
-- Name: ranks ranks_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.ranks
    ADD CONSTRAINT ranks_pk PRIMARY KEY (id);


--
-- Name: ranks ranks_rank_name_unique; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.ranks
    ADD CONSTRAINT ranks_rank_name_unique UNIQUE (rank_name);


--
-- Name: requirement_type requirement_type_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.requirement_type
    ADD CONSTRAINT requirement_type_pk PRIMARY KEY (id);


--
-- Name: requirements requirements_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.requirements
    ADD CONSTRAINT requirements_pk PRIMARY KEY (id);


--
-- Name: scouts scouts_pk; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.scouts
    ADD CONSTRAINT scouts_pk PRIMARY KEY ("BSA Member ID");


--
-- Name: rank_requirements_advancement_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX rank_requirements_advancement_idx ON public.rank_requirements USING btree ("Advancement");


--
-- Name: rank_requirements_advancement_type_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX rank_requirements_advancement_type_idx ON public.rank_requirements USING btree ("Advancement Type");


--
-- Name: rank_requirements_approved_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX rank_requirements_approved_idx ON public.rank_requirements USING btree ("Approved");


--
-- Name: rank_requirements_awarded_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX rank_requirements_awarded_idx ON public.rank_requirements USING btree ("Awarded");


--
-- Name: rank_requirements_bsa_member_id_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX rank_requirements_bsa_member_id_idx ON public.rank_requirements USING btree ("BSA Member ID");


--
-- Name: rank_requirements_first_name_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX rank_requirements_first_name_idx ON public.rank_requirements USING btree ("First Name");


--
-- Name: rank_requirements_last_name_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX rank_requirements_last_name_idx ON public.rank_requirements USING btree ("Last Name");


--
-- Name: ranks_advancement_type_unique; Type: INDEX; Schema: public; Owner: -
--

CREATE UNIQUE INDEX ranks_advancement_type_unique ON public.ranks USING btree (advancement_type);


--
-- Name: requirement_type_type_name_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirement_type_type_name_idx ON public.requirement_type USING btree (type_name);


--
-- Name: requirements_life_teachable_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_life_teachable_idx ON public.requirements USING btree (life_teachable);


--
-- Name: requirements_rank_id_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_rank_id_idx ON public.requirements USING btree (rank_id);


--
-- Name: requirements_requirement_alpha_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_requirement_alpha_idx ON public.requirements USING btree (requirement_alpha);


--
-- Name: requirements_requirement_number_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_requirement_number_idx ON public.requirements USING btree (requirement_number);


--
-- Name: requirements_sb_id_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_sb_id_idx ON public.requirements USING btree (sb_id);


--
-- Name: requirements_sm_only_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_sm_only_idx ON public.requirements USING btree (sm_only);


--
-- Name: requirements_twh_id_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_twh_id_idx ON public.requirements USING btree (twh_id);


--
-- Name: requirements_type_id_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX requirements_type_id_idx ON public.requirements USING btree (type_id);


--
-- Name: scouts_first_name_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX scouts_first_name_idx ON public.scouts USING btree ("First Name");


--
-- Name: scouts_last_name_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX scouts_last_name_idx ON public.scouts USING btree ("Last Name");


--
-- Name: scouts_patrol_name_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX scouts_patrol_name_idx ON public.scouts USING btree ("Patrol Name");


--
-- Name: rank_requirements rank_requirements_scouts_fk; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.rank_requirements
    ADD CONSTRAINT rank_requirements_scouts_fk FOREIGN KEY ("BSA Member ID") REFERENCES public.scouts("BSA Member ID") ON DELETE CASCADE;


--
-- Name: requirements requirements_ranks_fk; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.requirements
    ADD CONSTRAINT requirements_ranks_fk FOREIGN KEY (rank_id) REFERENCES public.ranks(id);


--
-- Name: requirements requirements_requirement_type_fk; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.requirements
    ADD CONSTRAINT requirements_requirement_type_fk FOREIGN KEY (type_id) REFERENCES public.requirement_type(id);


--
-- PostgreSQL database dump complete
--