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" character varying(255),
    "Approved" integer,
    "Awarded" integer,
    "MarkedCompletedBy" character varying(255),
    "MarkedCompletedDate" character varying(255),
    "CounselorApprovedBy" character varying(255),
    "CounselorApprovedDate" character varying(255),
    "LeaderApprovedBy" character varying(255),
    "LeaderApprovedDate" character varying(255),
    "AwardedBy" character varying(255),
    "AwardedDate" character varying(255)
);


--
-- 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: 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: 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: 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_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: 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;


--
-- 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
5	Star	Star Scout	Star Scout Rank Requirement
6	Life	Life Scout	Life Scout Rank Requirement
7	Eagle	Eagle Scout	Eagle 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;
141	7	7	7	f	t		7.	7	Eagle Scout Board of Review
30	2	3	4	t	f	a	4.a	4a	First Aid
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.
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
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
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
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
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
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
0	1	5	1	f	f	a	1.a	1a	Scout Oath, Law, etc.
133	6	7	7	f	t		7.	7	Scoutmaster Conference
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
17	1	7	7	f	t		7.	7	Scoutmaster Conference
18	2	7	10	f	t		10.	10	Scoutmaster Conference
19	2	7	11	f	t		11.	11	Board of Review
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
57	3	15	3	t	f	a	3.a	3a	Demonstrate Compass
60	3	15	3	t	f	d	3.d	3d	Directions w/o Compass
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
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
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
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
50	3	5	2	f	f	a	2.a	2a	Explain Cooking Fire 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
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
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
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
76	3	5	8	f	f	c	8.c	8c	Earn Money
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
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
120	5	7	2	f	t		2.	2	Scout Spirit
121	5	6	3	f	t		3.	3	Merit Badges
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
119	5	14	1	f	f		1.	1	Active Leadership 4 Months
29	2	8	3	f	f	d	3.d	3d	Knife, Saw and Ax
\.


--
-- 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;
\.


--
-- 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");


--
-- 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
--