troubleshooting Question

SQL Query returning multiple rows

Avatar of newjeep19
newjeep19Flag for United States of America asked on
Microsoft SQL ServerSQL
19 Comments1 Solution58 ViewsLast Modified:
I have a query where I am getting contact data for attendees of required courses and if they have or have not attended the course pluse the course name of the ther courses that is required that they need to take. The data for the above is comming from multiple databases and their tables.  Not sure if my JOINS are correct I have tried using DISTICT, GROUP BY and HAVING statements and still duplicating some of the returned row not all rows have duplicate data.

SQL Query:
    SELECT 
		GETDATE() AS CurrentDate,
		DATEADD(d, + 30, GETDATE()) AS MonthlyCurrentDate,
		r.ntid AS 'Student ID', 
		A.A_NBR AS '5 Digit Assoc A #',  
        CASE 
			WHEN r.ntid LIKE '%s%' OR r.ntid LIKE '%swa%' OR r.ntid LIKE '%sid%' OR r.ntid LIKE '%shi%' OR r.ntid LIKE '%sor%' OR r.ntid LIKE '%sak%'  OR r.ntid LIKE '%sco%'
			THEN 'LSP' 
			WHEN r.ntid LIKE '%0A%' OR r.ntid LIKE '%0a%' OR r.ntid LIKE '%a0%' OR r.ntid LIKE '%A0%' 
			THEN 'Agent' 
		END AS 'Student Type Dept', 
		CASE 
			WHEN WC.[Last Name] IS NOT NULL THEN WC.[Last Name]
			ELSE PARSENAME(REPLACE(r.name, ' ', '.'), 2) 
		END AS 'A Last Name', 
		CASE 
			WHEN WC.[First Name] IS NOT NULL THEN WC.[First Name]
			ELSE PARSENAME(REPLACE(r.name, ' ', '.'), 1) 
		END AS 'A First Name', 
		CASE 
			WHEN s.LAST_NM IS NOT NULL THEN s.LAST_NM 
			ELSE PARSENAME(REPLACE(r.name, ' ', '.'), 2) 
		END AS 'LSP Last Name', 
		CASE 
			WHEN s.FIRST_NM IS NOT NULL THEN s.FIRST_NM 
			ELSE PARSENAME(REPLACE(r.name, ' ', '.'), 1) 
		END AS 'LSP First Name', 
		CASE
			--WHEN WC.[EMAIL ADDRESS] IS NOT NULL THEN WC.[EMAIL ADDRESS]
			WHEN AG.Email IS NOT NULL THEN AG.Email + '@company.COM'
			ELSE WC.[A ID]+ '@company.COM'
		END AS 'Email',
		c.EndDateTimeView,
		r.dateTimeRegistered,
		c.name AS 'Course Name' ,
		CASE 
			WHEN r.attended = 'true' AND c.Name LIKE '%Associates%' THEN 'Yes'
			WHEN r.attended = 'true' AND c.Name LIKE '%Day 3%' OR c.Name LIKE '%Retain Bundle Grow%' THEN 'Yes'
			WHEN r.attended = 'true' AND c.Name LIKE '%Property University%' THEN 'Yes'	    
			ELSE 'No'
		END AS 'Attended',
		CASE
			WHEN c.Name LIKE '%Associates%' THEN 'AF Financial University - Associates'
			WHEN c.Name LIKE '%Retain Bundle Grow%' OR c.Name LIKE '%Day 3%' THEN 'Trusted Advisor University'
			WHEN c.Name LIKE '%Property University%' THEN 'Property University'
			ELSE 'No workshops attended'
		END AS 'Workshops',
		 CASE
			WHEN c.Name NOT LIKE '%Associates%' THEN 'Need to attend : AF University Associates'
			ELSE 'Attended: AF University Associates'
		END AS 'Need Workshops AFU',
		CASE 
			WHEN  c.Name NOT LIKE '%Day 3%' OR c.Name NOT LIKE '%Retain Bundle Grow%'THEN 'Need to attend: Trusted Advisor University'
			ELSE 'Attended: Trusted Advisor University'
		END AS 'Need SS Workshop',
		CASE
			WHEN c.Name NOT LIKE '%Property University%' THEN 'Neet to attend: Advanced Auto and Property University'
			ELSE 'Attended: Advanced Auto and Property University'
		END AS 'Need PU Workshop',
		CASE
			WHEN s.StartDate IS NOT NULL THEN s.StartDate
			ELSE '2001-01-01 00:00:00.0000000'
		END AS 'LSP Start Date'
FROM    dbo._registration AS r 
		INNER JOIN wcr_staff.dbo.Affiliations AS a ON a.REP_NT_ID = r.ntid 
		INNER JOIN dbo._course AS C ON C.CourseID = r.courseNumber 
		INNER JOIN wcr_ag.dbo.tblA AS AG ON AG.AGN_A_NBR = a.A_NBR
		LEFT OUTER JOIN wcr_staff.dbo.Staff AS s ON s.SALES_PARTY_ID = A.SALES_PARTY_ID
		LEFT OUTER JOIN WC_Policy_Universe.dbo.['List2014$'] WC ON AG.AGN_NBR = WC.[A ID]
WHERE	(NOT (r.ntid = ' ')) 
		AND(AG.AGN_STATUS_CD = 3)
		AND AG.Type = 'Primary'
		AND (a.IsActive = 1)
		AND (c.EndDateTimeView >= '2012-01-01') 
		OR c.EndDateTimeView >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())+31,0)
	    AND c.EndDateTimeView < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())+32,0)        
        OR c.EndDateTimeView BETWEEN CONVERT(DATETIME, FLOOR(CONVERT(FLOAT,GETDATE())))
		AND CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))+1)
ORDER BY A.AG_NBR ASC


Results of query where duplicate are:
2015-02-23 09:02:19.643	2015-03-25 09:02:19.643	scofvx0e	001937	LSP	HOERY	ROBERT	SPELLMAN	AMANDA	A001937@Company.COM	2015-02-23 17:00:00.000	2015-02-12 13:01:36.000	Sales University: Day 1 Auto	No	No workshops attended	Need to attend : AF University Associates	Need to attend: Trusted Advisor University	Neet to attend: Advanced Auto and Property University	2015-01-07 00:00:00.0000000
2015-02-23 09:02:19.643	2015-03-25 09:02:19.643	scofvx0e	001937	LSP	HOERY	ROBERT	SPELLMAN	AMANDA	A001937@Company.COM	2015-02-23 17:00:00.000	2015-02-12 13:01:36.000	Sales University: Day 1 Auto	No	No workshops attended	Need to attend : AF University Associates	Need to attend: Trusted Advisor University	Neet to attend: Advanced Auto and Property University	2015-01-07 00:00:00.0000000
2015-02-23 09:02:19.643	2015-03-25 09:02:19.643	scofvx0e	001937	LSP	HOERY	ROBERT	SPELLMAN	AMANDA	A001937@Company.COM	2015-02-23 17:00:00.000	2015-02-12 13:01:36.000	Sales University: Day 1 Auto	No	No workshops attended	Need to attend : AF University Associates	Need to attend: Trusted Advisor University	Neet to attend: Advanced Auto and Property University	2015-01-07 00:00:00.0000000

As you can see that there are three rows returning the exact same data. I know that somtimes when there is simuliar data in different tables that can cause duplicates but I need data from those other database tables.

Please help ASAP
Thanks in advance
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros