API tools faq
paste
Guest User

Untitled

a guest
Jan 20th, 2019
133
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.40 KB | None | 0 0
raw download clone embed print report
  1. WHERE
  2. Assignments.Year = '2018'
  3. AND Assignments.CourseNumber = 'PHED 1164'
  4. AND Assignments.Semester = 'fall'
  5. AND Assignments.Assignment = 'Fitbit'
  6.  
  7. SELECT
  8. c.username AS Username,
  9. ROUND(
  10. LEAST(
  11. GREATEST(
  12. AVG(
  13. activities.fairly_act_min + activities.vact_min
  14. )/(
  15. (
  16. c.max_avgweeklymin - c.min_avgweeklymin
  17. )/ c.max_percent
  18. ),
  19. AVG(activities.steps)/(
  20. (c.max_avgsteps - c.min_avgsteps)/ c.max_percent
  21. )- c.max_percent
  22. ),
  23. 100
  24. ),
  25. 1
  26. ) AS 'Activity Log Points Grade',
  27. '#' AS 'End-of-Line Indicator'
  28. FROM
  29. activities
  30. JOIN (
  31. SELECT
  32. Students.encodedid,
  33. Students.username,
  34. g.*
  35. FROM
  36. Assignments
  37. LEFT JOIN Students ON Assignments.SectionNumber = Students.SectionNumber
  38. LEFT JOIN (
  39. SELECT
  40. section,
  41. MAX(avgweeklymin) max_avgweeklymin,
  42. MIN(avgweeklymin) min_avgweeklymin,
  43. MAX(percent) max_percent,
  44. MAX(avgsteps) max_avgsteps,
  45. MIN(avgsteps) min_avgsteps
  46. FROM
  47. GradingScale
  48. GROUP BY
  49. section
  50. ) g ON Assignments.SectionNumber = g.section
  51. WHERE
  52. Assignments.Year = '2018'
  53. AND Assignments.CourseNumber = 'PHED 1164'
  54. AND Assignments.Semester = 'fall'
  55. AND Assignments.Assignment = 'Fitbit'
  56. ) c ON activities.encodedid <> ''
  57. AND activities.encodedid = c.encodedid
  58. JOIN (
  59. SELECT
  60. id,
  61. encodedid,
  62. GROUP_CONCAT(
  63. fairly_act_min + vact_min
  64. ORDER BY
  65. (fairly_act_min + vact_min)* 1 DESC
  66. ) grouped_steps
  67. FROM
  68. activities
  69. GROUP BY
  70. encodedid,
  71. WEEK(
  72. DATE_ADD(activitydate, INTERVAL 1 DAY)
  73. )
  74. ) p ON activities.id = p.id
  75. AND FIND_IN_SET(
  76. fairly_act_min + vact_min, grouped_steps
  77. ) <= 5
  78. GROUP BY
  79. c.encodedid;
  80.  
  81. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  82. SET AUTOCOMMIT = 0;
  83. START TRANSACTION;
  84. SET time_zone = "+00:00";
  85.  
  86. CREATE TABLE `activities` (
  87. `id` int(11) NOT NULL,
  88. `steps` mediumint(6) UNSIGNED DEFAULT NULL,
  89. `lightly_act_min` varchar(50) NOT NULL,
  90. `fairly_act_min` varchar(50) NOT NULL,
  91. `sed_act_min` varchar(50) NOT NULL,
  92. `vact_min` varchar(50) NOT NULL,
  93. `encodedid` varchar(25) NOT NULL,
  94. `activitydate` date NOT NULL,
  95. `username` varchar(50) NOT NULL
  96. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  97.  
  98. INSERT INTO `activities` (`id`, `steps`, `lightly_act_min`, `fairly_act_min`, `sed_act_min`, `vact_min`, `encodedid`, `activitydate`, `username`) VALUES
  99. (76139, 9416, '254', '72', '642', '75', '29BD68', '2018-10-01', ''),
  100. (106553, 0, '0', '0', '1440', '0', '29BD68', '2018-08-28', ''),
  101. (106554, 126, '6', '0', '1434', '0', '29BD68', '2018-08-29', ''),
  102. (106555, 9768, '257', '57', '629', '73', '29BD68', '2018-08-30', ''),
  103. (106556, 4884, '150', '20', '865', '14', '29BD68', '2018-08-31', ''),
  104. (106557, 7246, '170', '81', '778', '56', '29BD68', '2018-09-01', ''),
  105. (106575, 0, '18', '5', '1018', '33', '29BD68', '2018-09-19', ''),
  106. (106576, 65, '20', '21', '1366', '33', '29BD68', '2018-09-20', ''),
  107. (106577, 34, '13', '3', '1331', '25', '29BD68', '2018-09-21', ''),
  108. (106578, 18, '5', '0', '1435', '0', '29BD68', '2018-09-22', ''),
  109. (106579, 32, '0', '0', '1440', '0', '29BD68', '2018-09-23', ''),
  110. (106580, 6, '0', '0', '1440', '0', '29BD68', '2018-09-24', ''),
  111. (106581, 10320, '251', '121', '585', '34', '29BD68', '2018-09-25', ''),
  112. (106582, 5974, '236', '32', '723', '3', '29BD68', '2018-09-26', ''),
  113. (106583, 8455, '239', '47', '733', '27', '29BD68', '2018-09-27', ''),
  114. (106584, 6262, '204', '71', '765', '10', '29BD68', '2018-09-28', ''),
  115. (123953, 40, '1', '0', '1439', '0', '29BD68', '2018-11-11', ''),
  116. (123954, 639, '48', '0', '1392', '0', '29BD68', '2018-11-12', ''),
  117. (123955, 5878, '270', '76', '485', '36', '29BD68', '2018-11-13', ''),
  118. (123956, 7485, '330', '81', '1014', '15', '29BD68', '2018-11-14', ''),
  119. (123957, 5372, '270', '53', '545', '0', '29BD68', '2018-11-15', ''),
  120. (124888, 4699, '214', '12', '825', '6', '29BD68', '2018-11-16', ''),
  121. (125819, 0, '2', '0', '1438', '0', '29BD68', '2018-11-17', ''),
  122. (126750, 0, '0', '0', '1440', '0', '29BD68', '2018-11-18', ''),
  123. (129533, 5070, '279', '14', '728', '14', '29BD68', '2018-11-19', ''),
  124. (129534, 6471, '256', '124', '657', '15', '29BD68', '2018-11-20', ''),
  125. (129535, 3475, '144', '9', '722', '29', '29BD68', '2018-11-21', ''),
  126. (131405, 232, '16', '0', '965', '0', '29BD68', '2018-11-22', ''),
  127. (131406, 3546, '135', '1', '791', '18', '29BD68', '2018-11-23', ''),
  128. (133276, 6143, '278', '71', '657', '24', '29BD68', '2018-11-24', '');
  129.  
  130. ALTER TABLE `activities`
  131. ADD PRIMARY KEY (`id`),
  132. ADD UNIQUE KEY `encodedid` (`encodedid`,`activitydate`) USING BTREE;
  133.  
  134. ALTER TABLE `activities`
  135. MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=230103;
  136. COMMIT;
  137.  
  138. CREATE TABLE `Assignments` (
  139. `AssignmentID` int(11) NOT NULL,
  140. `Assignment` enum('Fitbit','Written Paper','Fitness Assessment') NOT NULL,
  141. `PointsPossible` int(11) NOT NULL DEFAULT 0,
  142. `CourseNumber` varchar(90) NOT NULL,
  143. `SectionNumber` varchar(8) NOT NULL,
  144. `Semester` enum('Fall','Spring','Summer1','Summer2','Winter','May') NOT NULL,
  145. `Year` year(4) NOT NULL,
  146. `PreStartDate` datetime NOT NULL,
  147. `PreEndDate` datetime NOT NULL,
  148. `PostStartDate` datetime NOT NULL,
  149. `PostEndDate` datetime NOT NULL
  150. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  151.  
  152. INSERT INTO `Assignments` (`AssignmentID`, `Assignment`, `PointsPossible`, `CourseNumber`, `SectionNumber`, `Semester`, `Year`, `PreStartDate`, `PreEndDate`, `PostStartDate`, `PostEndDate`) VALUES
  153. (47, 'Fitbit', 100, 'PHED 1164', '5073', 'Fall', 2018, '2018-09-03 00:00:00', '2018-12-07 23:30:00', '2018-09-03 00:00:00', '2018-12-07 23:30:00'),
  154. (48, 'Fitbit', 100, 'PHED 1164', '5075', 'Fall', 2018, '2018-09-03 00:00:00', '2018-12-07 23:30:00', '2018-09-03 00:00:00', '2018-12-07 23:30:00'),
  155. (49, 'Fitbit', 100, 'PHED 1164', '5019', 'Fall', 2018, '2018-09-03 00:00:00', '2018-12-12 23:30:00', '2018-09-03 00:00:00', '2018-12-12 23:30:00'),
  156. (61, 'Fitness Assessment', 100, 'PHED 1164', '5087', 'Fall', 2018, '2018-09-23 00:00:00', '2018-10-01 23:30:00', '2018-11-30 00:00:00', '2018-12-07 23:30:00');
  157.  
  158. ALTER TABLE `Assignments`
  159. ADD PRIMARY KEY (`AssignmentID`);
  160. ALTER TABLE `Assignments` ADD FULLTEXT KEY `section_number_index` (`SectionNumber`);
  161.  
  162. ALTER TABLE `Assignments`
  163. MODIFY `AssignmentID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=91;
  164. COMMIT;
  165.  
  166. CREATE TABLE `Students` (
  167. `id` bigint(20) NOT NULL,
  168. `SectionNumber` varchar(8) NOT NULL,
  169. `first` varchar(30) NOT NULL,
  170. `last` varchar(30) NOT NULL,
  171. `username` varchar(50) NOT NULL DEFAULT '',
  172. `semester` enum('fall','spring','summer1','summer2','winterminimester','mayminimester') NOT NULL,
  173. `year` year(4) NOT NULL,
  174. `CourseNumber` varchar(90) NOT NULL,
  175. `passtext` varchar(255) NOT NULL DEFAULT '',
  176. `email` varchar(200) NOT NULL DEFAULT '',
  177. `created` date DEFAULT NULL,
  178. `active` enum('y','n') NOT NULL DEFAULT 'y',
  179. `visitorIP` varchar(15) NOT NULL DEFAULT '',
  180. `aboutme` text NOT NULL,
  181. `age` char(3) NOT NULL DEFAULT '',
  182. `regkey` varchar(25) NOT NULL DEFAULT '',
  183. `goal_start` date NOT NULL,
  184. `goal` enum('muscle','weight','perform','toning') NOT NULL,
  185. `encodedid` varchar(25) NOT NULL,
  186. `accesstoken` varchar(2000) NOT NULL,
  187. `accesstoken_expiry` datetime DEFAULT NULL,
  188. `refreshtoken` varchar(2000) NOT NULL
  189. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  190.  
  191. INSERT INTO `Students` (`id`, `SectionNumber`, `first`, `last`, `username`, `semester`, `year`, `CourseNumber`, `passtext`, `email`, `created`, `active`, `visitorIP`, `aboutme`, `age`, `regkey`, `goal_start`, `goal`, `encodedid`, `accesstoken`, `accesstoken_expiry`, `refreshtoken`) VALUES
  192. (8, '5075', 'Tim', 'Mousel', 'mousel', 'fall', 2018, 'PHED 1164', '$2y$12$FDSvsMUpSQ79THcC6eCij.YjDFbwGQhHozvcJVa81tD4oYKAyrRi2', 'mousel@defend.net', '2018-03-26', 'y', '99.165.89.33', '', '', '8630082118400458833008407', '0000-00-00', 'muscle', '29BD68', 'eyJhbGciOiJIUzI1NiJ9.eyJhdWQiOiIyMkNWWkYiLCJzdWIiOiIyOUJENjgiLCJpc3MiOiJGaXRiaXQiLCJ0eXAiOiJhY2Nlc3NfdG9rZW4iLCJzY29wZXMiOiJyd2VpIHJhY3QgcmhyIHJwcm8gcm51dCIsImV4cCI6MTU0ODA1NjI0MywiaWF0IjoxNTQ4MDI3NDQzfQ.K5Pag0_4EfME21I5aUrPqwPE-otPQH458jidDl1FvgM', '2019-01-21 07:37:23', '1498d6c65ceaa43daf5a40db4af60a16a9cb37b96d86de3635c1a4ff89723574');
  193.  
  194. ALTER TABLE `Students`
  195. ADD PRIMARY KEY (`id`);
  196. ALTER TABLE `Students` ADD FULLTEXT KEY `encodedid_index` (`encodedid`);
  197.  
  198. ALTER TABLE `Students`
  199. MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=459;
  200. COMMIT;
  201.  
  202. CREATE TABLE `GradingScale` (
  203. `id` int(11) NOT NULL,
  204. `letter` enum('A','B','C','D','F') NOT NULL,
  205. `percent` smallint(4) NOT NULL,
  206. `avgsteps` smallint(6) NOT NULL,
  207. `avgweeklymin` smallint(4) NOT NULL,
  208. `section` varchar(8) NOT NULL,
  209. `AssignmentID` int(11) NOT NULL
  210. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Grading Scale';
  211.  
  212.  
  213. INSERT INTO `GradingScale` (`id`, `letter`, `percent`, `avgsteps`, `avgweeklymin`, `section`, `AssignmentID`) VALUES
  214. (1101, 'A', 100, 10000, 100, '5075', 1),
  215. (1102, 'A', 90, 9500, 90, '5075', 1),
  216. (1103, 'B', 80, 9000, 80, '5075', 1),
  217. (1104, 'C', 70, 8500, 70, '5075', 1),
  218. (1105, 'D', 60, 8000, 60, '5075', 1),
  219. (1106, 'F', 50, 7500, 50, '5075', 1),
  220. (1107, 'F', 40, 7000, 40, '5075', 1),
  221. (1108, 'F', 30, 6500, 30, '5075', 1),
  222. (1109, 'F', 20, 6000, 20, '5075', 1),
  223. (1110, 'F', 10, 5500, 10, '5075', 1),
  224. (1111, 'F', 0, 5000, 0, '5075', 1);
  225.  
  226. ALTER TABLE `GradingScale`
  227. ADD PRIMARY KEY (`id`) USING BTREE,
  228. ADD UNIQUE KEY `section` (`section`,`percent`,`letter`) USING BTREE;
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!