Solved

SQL Query issue-problem

Posted on 2014-10-09
6
236 Views
Last Modified: 2014-10-31
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
0
Comment
Question by:newjeep19
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40370791
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
 

Author Comment

by:newjeep19
ID: 40370885
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40370951
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:newjeep19
ID: 40371006
OK is a way to keep that relationship in the database but  not in the query?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40371101
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40374633
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
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.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question