newjeep19
asked on
SQL Query issue-problem
I have a query that I run that brings back data if a student has or has not attended ceritian courses. However, if they have attended one or two but not all three or they have attended all three or just one or none of the courses it returns the data in multiple rows. I want all the data for that students attendance to be in one row.
EX: Student A took one out of the three courses he needs to take I need to data to come as (below):
Student A ID, Student A Last Name, Student A First Name, Course 1 (not attended), Date Attended for Course 1 (Null) Course 2 (attended), Dated attended for Course 2 (Date Attended), Course 3(not attended), Date Attended for Course 3(Null)
My SQL code:
EX: Student A took one out of the three courses he needs to take I need to data to come as (below):
Student A ID, Student A Last Name, Student A First Name, Course 1 (not attended), Date Attended for Course 1 (Null) Course 2 (attended), Dated attended for Course 2 (Date Attended), Course 3(not attended), Date Attended for Course 3(Null)
My SQL code:
SELECT DISTINCT
r.ntid,
A.REP_NT_ID,
AG.AGN_TERR_AGTMGR_CD AS Territory,
AG.EFS_Terr AS Market,
AG.PrimaryAgentNumber AS 'Assoc Agt #',
CASE
WHEN s.LAST_NM IS NULL THEN PARSENAME(REPLACE(r.name, ' ', '.'), 2)
WHEN s.LAST_NM IS NOT NULL THEN s.LAST_NM
END AS 'LSP Last Name',
CASE
WHEN s.FIRST_NM IS NULL THEN PARSENAME(REPLACE(r.name, ' ', '.'), 1)
WHEN s.FIRST_NM IS NOT NULL THEN s.FIRST_NM
END AS 'LSP First Name',
CASE
WHEN c.Name LIKE '%Allstate Financial University%'
THEN c.name
ELSE 'NOT ATTENDED'
END AS 'Allstate Financial University',
CASE
WHEN c.Name LIKE '%Allstate Financial University%' AND r.attended = 'true'
THEN c.EndDateTimeView
WHEN c.EndDateTimeView IS NULL
THEN cd.EndCourseDateTime
ELSE Null
END AS 'Attended Date',
CASE
WHEN c.Name LIKE '%Property University%'
THEN c.name
ELSE 'NOT ATTENDED'
END AS 'Property University',
CASE
WHEN c.Name LIKE '%Property University%' AND r.attended = 'true'
THEN c.EndDateTimeView
WHEN c.EndDateTimeView IS NULL
THEN cd.EndCourseDateTime
ELSE Null
END AS 'Attended Date',
CASE
WHEN c.Name LIKE '%Sales and Service%'
THEN c.name
ELSE 'NOT ATTENDED'
END AS 'Sales and Service',
CASE
WHEN c.Name LIKE '%Sales and Service%' AND r.attended = 'true'
THEN c.EndDateTimeView
WHEN c.EndDateTimeView IS NULL
THEN cd.EndCourseDateTime
ELSE Null
END AS 'Attended Date'
FROM _registration AS r
LEFT OUTER JOIN wcr_staff.dbo.Affiliations AS A ON A.REP_NT_ID = r.ntid
LEFT OUTER JOIN _course AS c ON c.CourseID = r.courseNumber
LEFT OUTER JOIN wcr_staff.dbo.Staff AS s ON s.SALES_PARTY_ID = A.SALES_PARTY_ID
LEFT OUTER JOIN _courseDate AS cd ON cd.CourseID = c.CourseID
LEFT OUTER JOIN wcr_agents.dbo.tblAgents AS AG ON AG.AGN_AGENT_NBR = A.AGENT_NBR
WHERE r.ntid LIKE 's%'
AND c.EndDateTimeView >= '2012-01-01'
AND c.EndDateTimeView < '2014-12-31'
AND AG.AGN_TERR_AGTMGR_CD IS NOT NULL
--AND AG.[Type] = 'Primary'
GROUP BY r.ntid,
A.REP_NT_ID,
AG.AGN_TERR_AGTMGR_CD,
AG.EFS_Terr,
s.LAST_NM,
s.FIRST_NM,
r.name,
c.Name,
EndDateTimeView,
cd.EndCourseDateTime,
r.attended, AG.PrimaryAgentNumber
Actual Results
ASKER
Thank you for your quick repy. However, multiple rows are sill returned pless see below
001937 scofvx14 SCOFVX14 3 22 DUNAHAY KELLY 0 NULL 0 NULL 0
001937 scofvx14 SCOFVX14 3 22 DUNAHAY KELLY 0 NULL 4 2012-10-18 17:00:00.000 0
001937 scofvx14 SCOFVX14 3 22 DUNAHAY KELLY 0 NULL 0 NULL 0
001937 scofvx14 SCOFVX14 3 22 DUNAHAY KELLY 0 NULL 4 2012-10-18 17:00:00.000 0
This just means that you have multiple records in one of the grouped tables:
Since we are grouping by fields from these tables then somewhere in there you have a one-to-many relationship.
_registration
Affilitations
tblAgents
Staff
Since we are grouping by fields from these tables then somewhere in there you have a one-to-many relationship.
ASKER
OK is a way to keep that relationship in the database but not in the query?
There are several ways to do it either through a subquery, cte, or an aggregate on the column referenced from the "many" table.
It's just hard to do from this side of the curtain where we have very limited insight to your schema and data.
It's just hard to do from this side of the curtain where we have very limited insight to your schema and data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A couple of notes about why your query would not work:
Open in new window