-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmaster_data_source_2.sql
76 lines (76 loc) · 3.92 KB
/
master_data_source_2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
SELECT
CASE WHEN null IS NULL THEN '2022 - 2023' END AS "application year",
[athena_to_s3_scholar_data.csv].[appnum] AS "conf",
[athena_to_s3_scholar_data.csv].[childid],
[gender],
[dateofbirth],
[athena_to_s3_scholar_data.csv].[isell],
[enrolledschoolname],
[athena_to_s3_scholar_data.csv].[enrollverified],
[athena_to_s3_scholar_data.csv].[istransferring],
[currentschool],
/* [athena_to_s3_enrolled_siblings.csv].[enrolledschoolcode] AS "enrolled sibling school", */
[athena_to_s3_scholar_data.csv].[appdate],
[uberapplicationlate],
[applicationstatus],
[bestcurrentstatus],
[bestpullstatus],
[athena_to_s3_scholar_data.csv].[gradeabbrev] AS "entering grade",
[gradedobmismatch],
[acceptedschoolcode],
[acceptedschooladminadded],
[acceptedschoolrank],
LEFT([athena_to_s3_scholar_data.csv].[currentaccepteddate], 10) AS "currentaccepteddate",
LEFT([athena_to_s3_scholar_data.csv].[firstacceptdate], 10) AS "firstacceptdate",
[highestrankschoolname],
[applyingtoschoolany],
[appclosedate],
[closerequest],
[closerequestdate],
[importedscholarid],
[athena_to_s3_customfields_data.csv].[migratory esd id],
[isapplying],
[isreopened],
[reopendate],
[source],
[athena_to_s3_scholar_data.csv].[familyid],
[athena_to_s3_scholar_data.csv].[familynum],
[athena_to_s3_responsible_adults.csv].[responsibleadultid],
[athena_to_s3_responsible_adults.csv].[street],
[athena_to_s3_responsible_adults.csv].[apartmentnumber],
[athena_to_s3_responsible_adults.csv].[borough],
[athena_to_s3_responsible_adults.csv].[city],
[athena_to_s3_responsible_adults.csv].[state],
[athena_to_s3_responsible_adults.csv].[zip],
LEFT([athena_to_s3_responsible_adults.csv].[zip], 5) AS 'zip5',
[athena_to_s3_responsible_adults.csv].[latitude],
[athena_to_s3_responsible_adults.csv].[longitude],
[athena_to_s3_responsible_adults.csv].[schooldistrict],
CASE WHEN [athena_to_s3_responsible_adults.csv].[zoneerror] = 'TRUE' THEN 1 ELSE 0 END AS 'csd flagged',
[athena_to_s3_customfields_data.csv].[addressstatus],
[athena_to_s3_customfields_data.csv].[verbal status confirmation],
[athena_to_s3_responsible_adults.csv].[rfscompleted],
[athena_to_s3_responsible_adults.csv].[rfswatched],
[athena_to_s3_responsible_adults.csv].[rfsinvited],
[athena_to_s3_customfields_data.csv].[uniform fitting invited],
[athena_to_s3_customfields_data.csv].[uniform fitting attended],
[athena_to_s3_customfields_data.csv].[registration invited],
[athena_to_s3_customfields_data.csv].[registration attended],
[athena_to_s3_customfields_data.csv].[dress rehearsal invited],
[athena_to_s3_customfields_data.csv].[dress rehearsal attended],
[athena_to_s3_customfields_data.csv].[birth certificate],
[athena_to_s3_customfields_data.csv].[health form],
[athena_to_s3_customfields_data.csv].[immunization record],
[athena_to_s3_customfields_data.csv].[proof of residency],
[athena_to_s3_responsible_adults.csv].[language],
[athena_to_s3_customfields_data.csv].[accept_seat_and_decline_wl],
[athena_to_s3_customfields_data.csv].[accepted_but_wl_open],
[athena_to_s3_customfields_data.csv].[close request survey],
[athena_to_s3_customfields_data.csv].[reopen request status],
[athena_to_s3_customfields_data.csv].[reopen request date],
FROM [CSV1].[athena_to_s3_scholar_data.csv]
LEFT JOIN [CSV1].[athena_to_s3_responsible_adults.csv] ON [CSV1].[athena_to_s3_scholar_data.csv].responsibleadultid = [CSV1].[athena_to_s3_responsible_adults.csv].responsibleadultid
LEFT JOIN [CSV1].[athena_to_s3_customfields_data.csv] ON [CSV1].[athena_to_s3_scholar_data.csv].childid = [CSV1].[athena_to_s3_customfields_data.csv].childid
/* LEFT JOIN [CSV1].[athena_to_s3_family_members.csv] ON [CSV1].[athena_to_s3_scholar_data.csv].familyid = [CSV1].[athena_to_s3_family_members.csv].familyid */
/* LEFT JOIN [CSV1].[athena_to_s3_enrolled_siblings.csv] ON [CSV1].[athena_to_s3_scholar_data.csv].childid = [CSV1].[athena_to_s3_enrolled_siblings.csv].childid */
ORDER BY [athena_to_s3_scholar_data.csv].appdate DESC;