To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.
Course Of The Month
7 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sql Question
Posted on 2016-09-12
I have this dataset from my query
section_id user_id Role Status Entity_ID Course_Name
592253 12345 Student Active 001 PRINCIPLES OF TECHNOLOGY A
592255 12345 Student Active 001 ADVANCED PLANT & SOIL SCIENCE
593571 12345 Student Active 002 PRINCIPLES OF TECHNOLOGY A
593573 12345 Student Active 002 ADVANCED PLANT & SOIL SCIENCE
You can see I have user 12345 that have both entity_ID 001 and 002, but if you notice the course names are the same for 001 as they are for 002. 002 is the campus that the student resides on, he then goes to 001 to complete the course( cross-entity).
What I need to accomplish is in the data extract only pull the entity that the student resides on, which in this case is 002, and the course information. I do not want to pull the 001 data.
Now, this is just an example, I have students at other entities like 010, 003, 012, etc that I am in the same boat with. Additionally, I have students that are at 001, so those are good because they are not going anywhere else to do the class since they are on that home campus.
Does anyone have an idea on how I can accomplish this? Thank you so much
section_id user_id Role Status Entity_ID Course_Name
592253 12345 Student Active 001 PRINCIPLES OF TECHNOLOGY A
592255 12345 Student Active 001 ADVANCED PLANT & SOIL SCIENCE
593571 12345 Student Active 002 PRINCIPLES OF TECHNOLOGY A
593573 12345 Student Active 002 ADVANCED PLANT & SOIL SCIENCE
You can see I have user 12345 that have both entity_ID 001 and 002, but if you notice the course names are the same for 001 as they are for 002. 002 is the campus that the student resides on, he then goes to 001 to complete the course( cross-entity).
What I need to accomplish is in the data extract only pull the entity that the student resides on, which in this case is 002, and the course information. I do not want to pull the 001 data.
Now, this is just an example, I have students at other entities like 010, 003, 012, etc that I am in the same boat with. Additionally, I have students that are at 001, so those are good because they are not going anywhere else to do the class since they are on that home campus.
SELECT DISTINCT
ZZ_TEST_SIS_AllCourses.Course_ID AS section_id, ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID AS user_id, 'Student' AS Role,
'Active' AS Status, ZZ_TEST_SIS_AllCourses.Entity_ID, ZZ_TEST_SIS_AllCourses.Course_Name
FROM ZZ_TEST_SIS_AllCourses INNER JOIN
ZZ_TEST_SISCombinedScheduleandDemographics ON ZZ_TEST_SIS_AllCourses.Course_Number = ZZ_TEST_SISCombinedScheduleandDemographics.Course AND
ZZ_TEST_SIS_AllCourses.sky_Sect = ZZ_TEST_SISCombinedScheduleandDemographics.Section
WHERE (ZZ_TEST_SIS_AllCourses.Course_ID IN ('592253', '592255', '592266', '593571', '593573', '593585', '595105', '595207')) AND
(ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID = '12345')
Does anyone have an idea on how I can accomplish this? Thank you so much
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
- +1
7 Comments
Active today
If students are at other entities, how do we know which one you want? Or is there only ever one other entity possible, and in that case, you want the one that isn't 001 ?
Active 1 day ago
This can be done if you can tell me how to identify the campus the student resides on. I guess the question is, if the student doesn't reside on the home campus (001) will their campus always have a duplicate entry for 001?
I don't really understand what you are trying to accomplish. If you don't want 001 data, why don't you just filter it out (by adding another condition into WHERE clause like AND ZZ_TEST_SIS_AllCourses.Ent
Or you are trying to get the courses that student resides on and also completes those courses at home campus (001)? If that is the case, you can add another contidion into WHERE clause to only get the courses that student completes at home campus like AND ZZ_TEST_SIS_AllCourses.Cou
Code sample could look like:
Or you are trying to get the courses that student resides on and also completes those courses at home campus (001)? If that is the case, you can add another contidion into WHERE clause to only get the courses that student completes at home campus like AND ZZ_TEST_SIS_AllCourses.Cou
Code sample could look like:
SELECT DISTINCT
ZZ_TEST_SIS_AllCourses.Course_ID AS section_id, ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID AS user_id, 'Student' AS Role,
'Active' AS Status, ZZ_TEST_SIS_AllCourses.Entity_ID, ZZ_TEST_SIS_AllCourses.Course_Name
FROM ZZ_TEST_SIS_AllCourses INNER JOIN
ZZ_TEST_SISCombinedScheduleandDemographics ON ZZ_TEST_SIS_AllCourses.Course_Number = ZZ_TEST_SISCombinedScheduleandDemographics.Course AND
ZZ_TEST_SIS_AllCourses.sky_Sect = ZZ_TEST_SISCombinedScheduleandDemographics.Section
WHERE (ZZ_TEST_SIS_AllCourses.Course_ID IN ('592253', '592255', '592266', '593571', '593573', '593585', '595105', '595207')) AND
(ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID = '12345') AND
-- if filtering out only 001 - home campus
( ZZ_TEST_SIS_AllCourses.Entity_ID <> '001) AND
-- if we need to make sure the courses also be completed by the student at home campus
(ZZ_TEST_SIS_AllCourses.Course_Name IN (
SELECT ZZ_TEST_SIS_AllCourses.Course_Name
FROM ZZ_TEST_SIS_AllCourses INNER JOIN
ZZ_TEST_SISCombinedScheduleandDemographics ON ZZ_TEST_SIS_AllCourses.Course_Number = ZZ_TEST_SISCombinedScheduleandDemographics.Course AND ZZ_TEST_SIS_AllCourses.sky_Sect = ZZ_TEST_SISCombinedScheduleandDemographics.Section
WHERE (ZZ_TEST_SIS_AllCourses.Course_ID IN ('592253', '592255', '592266', '593571', '593573', '593585', '595105', '595207')) AND (ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID = '12345') AND (ZZ_TEST_SIS_AllCourses.Entity_ID = '001')))
There is a field called School number I brought in. That is how I know which school the student is from.
The ones highlighted in the yellow are the ones I need. Where the school number and the away school match. So if there is a 002 as the school number and away school has 002 that is what I need.
The ones highlighted in the yellow are the ones I need. Where the school number and the away school match. So if there is a 002 as the school number and away school has 002 that is what I need.
SELECT DISTINCT
ZZ_TEST_SIS_AllCourses.Course_ID AS section_id, ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID AS user_id, 'Student' AS Role,
'Active' AS Status, ZZ_TEST_SIS_AllCourses.Course_Name, ZZ_TEST_SISCombinedScheduleandDemographics.SchoolNumber,
ZZ_TEST_SIS_AllCourses.Entity_ID as 'Away School'
FROM ZZ_TEST_SIS_AllCourses INNER JOIN
ZZ_TEST_SISCombinedScheduleandDemographics ON ZZ_TEST_SIS_AllCourses.Course_Number = ZZ_TEST_SISCombinedScheduleandDemographics.Course AND
ZZ_TEST_SIS_AllCourses.sky_Sect = ZZ_TEST_SISCombinedScheduleandDemographics.Section
WHERE (ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID = '12346')
Active today
Then just look for the condition where SchoolNumber = [Away School]
SELECT DISTINCT
ZZ_TEST_SIS_AllCourses.Course_ID AS section_id, ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID AS user_id, 'Student' AS Role,
'Active' AS Status, ZZ_TEST_SIS_AllCourses.Course_Name, ZZ_TEST_SISCombinedScheduleandDemographics.SchoolNumber,
ZZ_TEST_SIS_AllCourses.Entity_ID as 'Away School'
FROM ZZ_TEST_SIS_AllCourses INNER JOIN
ZZ_TEST_SISCombinedScheduleandDemographics ON ZZ_TEST_SIS_AllCourses.Course_Number = ZZ_TEST_SISCombinedScheduleandDemographics.Course AND
ZZ_TEST_SIS_AllCourses.sky_Sect = ZZ_TEST_SISCombinedScheduleandDemographics.Section
WHERE (ZZ_TEST_SISCombinedScheduleandDemographics.StudentInternalID = '12346')
AND (ZZ_TEST_SIS_AllCourses.Entity_ID = ZZ_TEST_SISCombinedScheduleandDemographics.SchoolNumber)
Thank you, I had figured that out before I saw that you posted this. I guess I just needed to get ideas then that got me seeing what I was doing incorrectly. Again thank you
Featured Post
How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Format Date fields | 11 | 62 | |
| Retrieving JSON data as OUT parameter in Sql Server CLR procedure | 1 | 42 | |
| how to solve "recursive" database tables | 2 | 52 | |
| rolling count by date, hour query | 7 | 30 |
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed.
Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses
Course of the Month7 days, 16 hours left to enroll
737 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.