Link to home
Start Free TrialLog in
Avatar of newjeep19
newjeep19Flag for United States of America

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:
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 ResultsUser generated image
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

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

Avatar of newjeep19

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
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial