Solved

SQL Query issue-problem

Posted on 2014-10-09
6
191 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now