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

Open in new window

Actual ResultsPic1.jpg
newjeep19Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
This is hard to do with "aircode" but try the query below.

A couple of notes about why your query would not work:
You are including the course in your grouping
The CASE statement to calculate the course end date would provide incorrect data as each step is independent

SELECT 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) 
        ELSE s.LAST_NM 
    END AS 'LSP Last Name', 
    CASE 
        WHEN s.FIRST_NM IS NULL THEN PARSENAME(REPLACE(r.name, ' ', '.'), 1) 
		ELSE s.FIRST_NM 
    END AS 'LSP First Name',
    MAX(CASE
        WHEN c.Name LIKE '%Allstate Financial University%' THEN 1
        ELSE 0
    END) AS 'Allstate Financial University',
    MAX(CASE
		WHEN c.Name LIKE '%Allstate Financial University%' AND r.attended = 'true' THEN ISNULL(c.EndDateTimeView, cd.EndCourseDateTime)
		ELSE NULL
    END) AS 'Attended Date',
    SUM(CASE
        WHEN c.Name LIKE '%Property University%' THEN 1 
        ELSE 0
    END) AS 'Property University',
    MAX(CASE
        WHEN c.Name LIKE '%Property University%' AND r.attended = 'true' THEN ISNULL(c.EndDateTimeView, cd.EndCourseDateTime)
        ELSE NULL
    END) AS 'Attended Date',
    SUM(CASE
        WHEN c.Name LIKE '%Sales and Service%' THEN 1
        ELSE 0
    END) AS 'Sales and Service',
    MAX(CASE
		WHEN c.Name LIKE '%Sales and Service%' AND r.attended = 'true' THEN ISNULL(c.EndDateTimeView, 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,
	r.attended,
	AG.PrimaryAgentNumber

Open in new window

0
newjeep19Author Commented:
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
0
Brian CroweDatabase AdministratorCommented:
This just means that you have multiple records in one of the grouped tables:
_registration
Affilitations
tblAgents
Staff

Since we are grouping by fields from these tables then somewhere in there you have a one-to-many relationship.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

newjeep19Author Commented:
OK is a way to keep that relationship in the database but  not in the query?
0
Brian CroweDatabase AdministratorCommented:
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.
0
PortletPaulfreelancerCommented:
This is classic "Hail Mary" use of "select distinct"

Please read Select Distinct is returning duplicates ...

You must first recognize the "select distinct" cannot, and does not, return duplicates.
Because "select distinct" is considering  all, 100%, full, complete, the entirety, of the row.
Just a full stop could make one row distinct from the one after.

So. If you have used "select distinct" and it hasn't solved the duplication - it just isn't the right technique.

You MUST decide what it is you actually need from each table, then go about getting just that and nothing more. Often this leads to using GROUP BY and/or ROW_NUMBER()

these are different, what table does it come from?
For each column that differ like this you must decide this like:
can I live without that column?
can I take the maximum or minimum? (group_by)
could I take only the "latest" or the "first" (row_number)

--------------
Now another matter.

There simply is no point using a LEFT OUTER JOIN and then insisting every record meet some condition on that table through the where clause. That is an implicit inner join and you might as well just use INNER JOIN as it will be faster.

AS IS
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'

Open in new window


Those where conditions on alias C and alias AG negate the relevant left outer joins

TO BE
FROM		_registration AS r  
			INNER JOIN wcr_staff.dbo.Affiliations AS A   ON A.REP_NT_ID = r.ntid
			INNER JOIN _course AS c                      ON c.CourseID = r.courseNumber
			INNER JOIN wcr_agents.dbo.tblAgents AS AG    ON AG.AGN_AGENT_NBR = A.AGENT_NBR 
			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
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'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.