Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query issue-problem

Posted on 2014-10-09
6
Medium Priority
?
312 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

824 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