UNRESOLVED - How to Join 3 Views into 1 View? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UNRESOLVED - How to Join 3 Views into 1 View?

Author

Topic

Salnick4
Starting Member

19 Posts

Posted - 2005-08-02 : 07:59:05

I am working with 3 views that I created that pull data from 3 various tables.

The Primary key for each view is an ID field, which if I join the 3 views with that ID, then it works great....the problem is that there are also date fields within each view that I need carried over to the main query so that I can use a WHERE clause and pull specific records.

Below is the code that I am currently using within the new query:

SELECT     dbo.ACW_SCORE.ASPECT_ID, dbo.QUALITY_PASS_PERCENT.AGENT_NAME, dbo.ACW_SCORE.[ACW %], 
                      dbo.ACW_SCORE.[ATT in sec.], dbo.COMPLIANCE_WITH_ID.[Average Compliance %], dbo.QUALITY_PASS_PERCENT.[Total Scores], 
                      dbo.QUALITY_PASS_PERCENT.[Average Score], dbo.QUALITY_PASS_PERCENT.[Total Passes], dbo.QUALITY_PASS_PERCENT.[Pass %], 
                      dbo.COMPLIANCE_WITH_ID.ADHERENCE_DTE, dbo.QUALITY_PASS_PERCENT.EVALUATION_DTE,  dbo.ACW_SCORE.AUDIT_DTE
FROM         dbo.ACW_SCORE INNER JOIN
                      dbo.COMPLIANCE_WITH_ID ON dbo.ACW_SCORE.ASPECT_ID = dbo.COMPLIANCE_WITH_ID.ASPECT_ID INNER JOIN
                      dbo.QUALITY_PASS_PERCENT ON dbo.COMPLIANCE_WITH_ID.ASPECT_ID = dbo.QUALITY_PASS_PERCENT.HSCID

When this code is executed the data shows only 1 ID for every record, and the dates that are in the final 3 fields of this query, are different for each record.

I hope that I have explained myself clearly.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 09:03:33

Are

dbo.ACW_SCORE
dbo.COMPLIANCE_WITH_ID
dbo.QUALITY_PASS_PERCENT

views?

If so might help if you post the DDL for the views.

Are these are the three date columns?

ADHERENCE_DTE
EVALUATION_DTE
AUDIT_DTE

Should they be the same in all three "views" for a given ID?

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-02 : 09:16:26

Please follow the link below...I'm sure we can figure it out with enough info

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Salnick4
Starting Member

19 Posts

Posted - 2005-08-02 : 10:09:49

I apologize for not providing enough detail.

I will attempt to give the DDL for the 3 views:

dbo.ACW_SCORE(ASPECT_ID int, AUDIT_DTE datetime, [ACW %] int, [ATT in sec] int)
dbo.COMPLIANCE_WITH_ID(ADHERENCE_DTE datetime, EMPLOYEE_ID int, [Average Compliance %] int)
dbo.QUALITY_PASS_PERCENT(AGENT_NAME varchar, [Total Scores] int, [Total Passes] int, [Pass %] int, HSCID int, EVALUATION_DTE datetime)

I am not sure exactly how I can show the sample data in the form of DML?

To answer Kristen's question about the date fields...the data in each of the views have daily dates, so for each unique ID there should be a corresponding date that matches in all 3 views.

If there is more I need to provide, please let me know...I might need assistance.

Thank you...

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 10:20:23

Are they views, or tables?

If VIEWs you should be able to do

exec sp_helptext('ACW_SCORE')

and you will get the DDL for the VIEW

You can probably make some INSERT statement like
INSERT INTO MyTable
SELECT 'A', 'B', 123 UNION ALL
SELECT 'X', 'Y', 123

by doing

SELECT TOP 10 'SELECT', *, 'UNION ALL'
FROM MyTable

and just hand editing to put some quotes and commas around the text stuff (and chopping the "UNION ALL" off the last line)

Kristen

Salnick4
Starting Member

19 Posts

Posted - 2005-08-02 : 14:31:06

Thank you for the assistance:

Below is the sample data for the 3 views:
ACW_SCORE:

AUDIT_DTE____ASPECT_ID___ACW %____ATT in sec.
6/1/05_________6142_______6.4________201.8
6/27/05________6426_______0__________212.3
7/1/05_________5982_______5.3________252.2

COMPLIANCE_WITH_ID:

ASPECT_ID_______Average Compliance %_____ADHERENCE_DTE
__0303_________________94_________________7/27/05
__5732_________________97_________________7/20/05
__5864_________________79_________________7/22/05

QUALITY_PASS_PERCENT:

AGENT_NAME______Total Scores___Average Score__Total Passes___Pass %_HSCID_EVALUATION_DTE
Alexander, Antuan_____1____________87_____________1___________100___7609_7/7/05 5:40:02 PM
Alexander, Antuan_____1____________87_____________1___________100___7609_7/7/05 5:53:28 PM
Alexander, Antuan_____1____________87_____________1___________100___7609_7/14/05 12:38:56 AM
Allison, Katy_________1____________85_____________1___________100___6001_7/15/05 1:11:34 AM

Does this help? I am sorry that I am still learning...

Salnick4
Starting Member

19 Posts

Posted - 2005-08-02 : 14:32:43

To also answer your question Kristen, yes these are views...and I am not sure where I would use the statement 'exec sp_helptext('ACW_SCORE')' to get the DDL for the view?

If you can assist me with what you need I will do my best.

Thank you.

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 15:07:06

OK, do you have the SQL Server client on your machine?

In Start : Programs that would give you "Microsoft SQL Server : Query Analyser"

In there you can just type in a query, execute it, and see the results.

If that's not installed, and you want to have some serious play-time with SQL Server, it would be good to get it installed if you can. (Bizarrely it is installed from the Server Install disk by starting off a Server Install and then saying "Actually, I don't want to install a SQL Server, I just want the client tools"

If you don't have those tools then anything which can execute SQL statements will do, but you'll need a connection to the SQL Server, and that in turn will require a login/password (or that your Windows account has Authentication for the SQL Server box)

(I'm going to hop off and grab a bite to eat now, and I probably won't be around until tomorrow morning, UK time)

Kristen

Salnick4
Starting Member

19 Posts

Posted - 2005-08-02 : 16:28:34

I tried running the ''exec sp_helptext('ACW_SCORE')' within the Query Analyzer, and I keep getting an error - Line 1: Incorrect syntax near 'ACW_SCORE'.

I will do whatever it takes to get this to work, so please show me what I need to do to get you all the pertinent info so as to resolve my issue...

Thank you for all your assistance!

Kristen
Test

22859 Posts

Posted - 2005-08-03 : 01:41:36

Me bad, as they say nowadays :-( There shouldn't be any parenthesis in there

exec sp_helptext 'ACW_SCORE'

Sorry

Kristen

Salnick4
Starting Member

19 Posts

Posted - 2005-08-03 : 14:42:18

Ok...here is what I got after the exec:

CREATE VIEW dbo.ACW_SCORE
AS
SELECT AUDIT_DTE, ASPECT_ID, (CASE WHEN (SUM(AFTER_CALL_TME) = 0) THEN 0 ELSE CAST((SUM(AFTER_CALL_TME) * 1.0 / SUM(STAFF_TME))
* 100 AS DECIMAL(5, 1)) END) AS [ACW %], (CASE WHEN (SUM(AUTO_CALL_DIST) = 0) THEN 0 ELSE CAST((SUM(AUTO_CALL_DIST)
* 1.0 / SUM(AFTER_CALLS)) AS DECIMAL(5, 1)) END) AS [ATT in sec.]
FROM GROUP_AUDIT
GROUP BY ASPECT_ID, AUDIT_DTE

Thank you

Kristen
Test

22859 Posts

Posted - 2005-08-03 : 16:05:22

Sorry, not got time to tackle this tonight, this post will bring it up tomrrow to remind me

Kristen

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 01:01:29

quote:
Originally posted by Kristen

Sorry, not got time to tackle this tonight, this post will bring it up tomrrow to remind me

Kristen

Salnick4
Starting Member

19 Posts

Posted - 2005-08-04 : 14:36:10

Kristen,

Is there a simple way that I can show you some sample data of what I get now without using the date fields from the 3 views, possibly using the Query Analyzer?

I want to give you as much as I can so that you can assist me..

Thank you.

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 14:51:38

Sorry, its been a busy day and I've just dipped in and out of SQL Team, and I knew this will take me a fair amount of time to help you with so haven't added anything.

If you can provide DDL for the tables and views (complete CREATE statements, and all the constraints and indexes etc.) and a "handful" of rows [representative of the problem] as INSERT statements, then anyone here will be able to just create the tables and try the problem.

Without that we have to try to imagine what the problems might be, and often it turns out to be something outside that! and a fair amount of time elapses

If you can run your script and re-creat the problem locally then you will have done enough for us to be able to do it too!

When you have done that change the SUBJECT of this thread [as author you can edit the first message at the top of the thread] to append "- NOT YET SOLVED" or somesuch - otherwise other folk may see the number of replies and assume its been solved.

Brett's link, earlier in the thread, has some suggestions:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen

Salnick4
Starting Member

19 Posts

Posted - 2005-08-05 : 14:42:18

I have tested output with the following code, and here you will see what my problem is...it is hsowing duplicate records for one agent, when I am looking for it to show 1 record per unique date:

CREATE TABLE #ACW_SCORE( AUDIT_DTE smalldatetime,
ASPECT_ID integer,
ACWPct decimal(7,1),
ATT decimal(7,1))
INSERT INTO #ACW_SCORE VALUES( '6/1/05', 6142, 6.4, 201.8)
INSERT INTO #ACW_SCORE VALUES( '6/27/05', 6426, 0, 212.3)
INSERT INTO #ACW_SCORE VALUES( '7/7/05', 7609, 4.3, 242.4)
INSERT INTO #ACW_SCORE VALUES( '7/7/05', 0131, 5.5, 214.6)
INSERT INTO #ACW_SCORE VALUES( '7/8/05', 0131, 7.5, 202.6)
INSERT INTO #ACW_SCORE VALUES( '8/7/05', 0134, 10.6, 151.8)
INSERT INTO #ACW_SCORE VALUES( '8/7/05', 0139, 1.1, 199.2)

CREATE TABLE #COMPLIANCE_WITH_ID( ASPECT_ID integer,
AverageCompliance integer,
ADHERENCE_DTE smalldatetime)
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0303, 94, '7/27/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 5732, 97, '7/20/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 7609, 85, '7/7/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0131, 93, '7/14/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0131, 90, '7/8/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0134, 93, '7/14/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0139, 93, '7/11/05')

CREATE TABLE #QUALITY_PASS_PERCENT( AGENT_NAME varchar(50),
TotalScores tinyint,
AverageScore integer,
TotalPasses tinyint,
PassPct tinyint,
HSCID integer,
EVALUATION_DTE smalldatetime)
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/7/05 5:40:28')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/7/05 5:53:28')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/14/05 12:38:56')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Allison, Katy', 1, 85, 1, 100, 6001, '7/15/05 1:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Boyd, Lacrecia', 1, 85, 1, 100, 0131, '7/20/05 1:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Boyd, Lacrecia', 1, 77, 1, 80, 0131, '7/20/05 2:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Lewis, Edwin', 1, 85, 1, 100, 0134, '8/7/05 1:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Whitehead, Charise', 1, 85, 1, 100, 0139, '7/11/05 1:11:34')

SELECT #ACW_SCORE.ASPECT_ID, QPP.AGENT_NAME, #ACW_SCORE.ACWPct, #ACW_SCORE.ATT AS [ATT in sec.],
#COMPLIANCE_WITH_ID.AverageCompliance, QPP.TotalScores, QPP.AverageScore, QPP.TotalPasses, QPP.PassPct,
#COMPLIANCE_WITH_ID.ADHERENCE_DTE, QPP.EVALUATION_DTE, #ACW_SCORE.AUDIT_DTE
FROM #ACW_SCORE
INNER JOIN #COMPLIANCE_WITH_ID ON #ACW_SCORE.ASPECT_ID = #COMPLIANCE_WITH_ID.ASPECT_ID
INNER JOIN( SELECT #QUALITY_PASS_PERCENT.AGENT_NAME, #QUALITY_PASS_PERCENT.TotalScores,
#QUALITY_PASS_PERCENT.AverageScore, #QUALITY_PASS_PERCENT.TotalPasses,
#QUALITY_PASS_PERCENT.PassPct, #QUALITY_PASS_PERCENT.HSCID,
MAX( #QUALITY_PASS_PERCENT.EVALUATION_DTE) AS EVALUATION_DTE
FROM #QUALITY_PASS_PERCENT
GROUP BY #QUALITY_PASS_PERCENT.AGENT_NAME, #QUALITY_PASS_PERCENT.TotalScores,
#QUALITY_PASS_PERCENT.AverageScore, #QUALITY_PASS_PERCENT.TotalPasses,
#QUALITY_PASS_PERCENT.PassPct, #QUALITY_PASS_PERCENT.HSCID) QPP
ON #COMPLIANCE_WITH_ID.ASPECT_ID = QPP.HSCID

DROP TABLE #ACW_SCORE
DROP TABLE #COMPLIANCE_WITH_ID
DROP TABLE #QUALITY_PASS_PERCENT

It has something to do with the data that is residing in the 3 tables...because when I take out some of the multiple records from the INSERT, I get perfect results...look at this:

CREATE TABLE #ACW_SCORE( AUDIT_DTE smalldatetime,
ASPECT_ID integer,
ACWPct decimal(7,1),
ATT decimal(7,1))
INSERT INTO #ACW_SCORE VALUES( '6/1/05', 6142, 6.4, 201.8)
INSERT INTO #ACW_SCORE VALUES( '6/27/05', 6426, 0, 212.3)
INSERT INTO #ACW_SCORE VALUES( '7/7/05', 7609, 4.3, 242.4)
INSERT INTO #ACW_SCORE VALUES( '7/7/05', 0131, 5.5, 214.6)
INSERT INTO #ACW_SCORE VALUES( '8/7/05', 0134, 10.6, 151.8)
INSERT INTO #ACW_SCORE VALUES( '8/7/05', 0139, 1.1, 199.2)

CREATE TABLE #COMPLIANCE_WITH_ID( ASPECT_ID integer,
AverageCompliance integer,
ADHERENCE_DTE smalldatetime)
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0303, 94, '7/27/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 5732, 97, '7/20/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 7609, 85, '7/7/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0131, 93, '7/14/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0134, 93, '7/14/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0139, 93, '7/11/05')

CREATE TABLE #QUALITY_PASS_PERCENT( AGENT_NAME varchar(50),
TotalScores tinyint,
AverageScore integer,
TotalPasses tinyint,
PassPct tinyint,
HSCID integer,
EVALUATION_DTE smalldatetime)
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/7/05 5:40:28')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/7/05 5:53:28')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/14/05 12:38:56')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Allison, Katy', 1, 85, 1, 100, 6001, '7/15/05 1:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Boyd, Lacrecia', 1, 85, 1, 100, 0131, '7/20/05 1:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Lewis, Edwin', 1, 85, 1, 100, 0134, '8/7/05 1:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Whitehead, Charise', 1, 85, 1, 100, 0139, '7/11/05 1:11:34')

SELECT #ACW_SCORE.ASPECT_ID, QPP.AGENT_NAME, #ACW_SCORE.ACWPct, #ACW_SCORE.ATT AS [ATT in sec.],
#COMPLIANCE_WITH_ID.AverageCompliance, QPP.TotalScores, QPP.AverageScore, QPP.TotalPasses, QPP.PassPct,
#COMPLIANCE_WITH_ID.ADHERENCE_DTE, QPP.EVALUATION_DTE, #ACW_SCORE.AUDIT_DTE
FROM #ACW_SCORE
INNER JOIN #COMPLIANCE_WITH_ID ON #ACW_SCORE.ASPECT_ID = #COMPLIANCE_WITH_ID.ASPECT_ID
INNER JOIN( SELECT #QUALITY_PASS_PERCENT.AGENT_NAME, #QUALITY_PASS_PERCENT.TotalScores,
#QUALITY_PASS_PERCENT.AverageScore, #QUALITY_PASS_PERCENT.TotalPasses,
#QUALITY_PASS_PERCENT.PassPct, #QUALITY_PASS_PERCENT.HSCID,
MAX( #QUALITY_PASS_PERCENT.EVALUATION_DTE) AS EVALUATION_DTE
FROM #QUALITY_PASS_PERCENT
GROUP BY #QUALITY_PASS_PERCENT.AGENT_NAME, #QUALITY_PASS_PERCENT.TotalScores,
#QUALITY_PASS_PERCENT.AverageScore, #QUALITY_PASS_PERCENT.TotalPasses,
#QUALITY_PASS_PERCENT.PassPct, #QUALITY_PASS_PERCENT.HSCID) QPP
ON #COMPLIANCE_WITH_ID.ASPECT_ID = QPP.HSCID

DROP TABLE #ACW_SCORE
DROP TABLE #COMPLIANCE_WITH_ID
DROP TABLE #QUALITY_PASS_PERCENT

In this code I removed 2nd instance of ID '0131' from the 3 tables...

Let me know if you can see what is causing the problem...

Thank you.

Kristen
Test

22859 Posts

Posted - 2005-08-07 : 07:06:17

Thanks for teh code, MUCH easier to see what you are trying to do now

#COMPLIANCE_WITH_ID has two rows with HSCID = 131. #ACW_SCORE.ASPECT_ID also has two rows with HSCID = 131. When you join the two tables the resulting recordset will contain FOUR rows. Every matching row in #ACW_SCORE is matched with every matching row in #COMPLIANCE_WITH_ID (a cartesian join of sorts).

Then you also have two rows for QPP.HSCID = 131 - so when that is joined in you will get EIGHT rows for 131

(On JOINS where there is One row in TableA matching one row in TableB then they each match with each other only once, so you only get one row).

Here are the rows that are included in the JOIN


SELECT DISTINCT
	[Included?] = CASE WHEN #COMPLIANCE_WITH_ID.ASPECT_ID IS NOT NULL THEN 'Yes' ELSE '' END,
	#ACW_SCORE.*
FROM	#ACW_SCORE
LEFT OUTER JOIN #COMPLIANCE_WITH_ID 
		ON #ACW_SCORE.ASPECT_ID = #COMPLIANCE_WITH_ID.ASPECT_ID
ORDER BY #ACW_SCORE.ASPECT_ID, #ACW_SCORE.AUDIT_DTE

SELECT DISTINCT
	[Included?] = CASE WHEN #ACW_SCORE.ASPECT_ID IS NOT NULL THEN 'Yes' ELSE '' END,
	#COMPLIANCE_WITH_ID.*
FROM	#COMPLIANCE_WITH_ID
	LEFT OUTER JOIN #ACW_SCORE
		ON #ACW_SCORE.ASPECT_ID = #COMPLIANCE_WITH_ID.ASPECT_ID
ORDER BY #COMPLIANCE_WITH_ID.ASPECT_ID, #COMPLIANCE_WITH_ID.ADHERENCE_DTE

So you need to further qualify the JOINs to match up the two rows in #ACW_SCORE with the [presumably] corresponding rows in #COMPLIANCE_WITH_ID.

Looks like AUDIT_DTE and ADHERENCE_DTE might do, but in your sample data that will remove a row for each of the two tables.

However, on #QUALITY_PASS_PERCENT I can't see any obvious column! but hopefully your knowledge of the data and structure will provide you with something

I formatted up your code to make it easier for me to read, I'll post it here in cae its useful to anyone else


SET DATEFORMAT mdy
GO
SET NOCOUNT ON
GO
CREATE TABLE #ACW_SCORE
(
	AUDIT_DTE smalldatetime, 
	ASPECT_ID integer, 
	ACWPct decimal(7,1), 
	ATT decimal(7,1)
) 
INSERT INTO #ACW_SCORE VALUES( '6/1/05', 6142, 6.4, 201.8) 
INSERT INTO #ACW_SCORE VALUES( '6/27/05', 6426, 0, 212.3) 
INSERT INTO #ACW_SCORE VALUES( '7/7/05', 7609, 4.3, 242.4) 
INSERT INTO #ACW_SCORE VALUES( '7/7/05', 0131, 5.5, 214.6)
INSERT INTO #ACW_SCORE VALUES( '7/8/05', 0131, 7.5, 202.6)
INSERT INTO #ACW_SCORE VALUES( '8/7/05', 0134, 10.6, 151.8)
INSERT INTO #ACW_SCORE VALUES( '8/7/05', 0139, 1.1, 199.2)


CREATE TABLE #COMPLIANCE_WITH_ID
(
	ASPECT_ID integer, 
	AverageCompliance integer, 
	ADHERENCE_DTE smalldatetime
) 
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0303, 94, '7/27/05') 
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 5732, 97, '7/20/05') 
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 7609, 85, '7/7/05') 
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0131, 93, '7/14/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0131, 90, '7/8/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0134, 93, '7/14/05')
INSERT INTO #COMPLIANCE_WITH_ID VALUES( 0139, 93, '7/11/05') 

CREATE TABLE #QUALITY_PASS_PERCENT
(
	AGENT_NAME varchar(50), 
	TotalScores tinyint, 
	AverageScore integer, 
	TotalPasses tinyint, 
	PassPct tinyint, 
	HSCID integer, 
	EVALUATION_DTE smalldatetime
) 
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/7/05 5:40:28') 
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/7/05 5:53:28') 
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Alexander, Antuan', 1, 87, 1, 100, 7609, '7/14/05 12:38:56') 
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Allison, Katy', 1, 85, 1, 100, 6001, '7/15/05 1:11:34') 
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Boyd, Lacrecia', 1, 85, 1, 100, 0131, '7/20/05 1:11:34') 
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Boyd, Lacrecia', 1, 77, 1, 80, 0131, '7/20/05 2:11:34')
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Lewis, Edwin', 1, 85, 1, 100, 0134, '8/7/05 1:11:34') 
INSERT INTO #QUALITY_PASS_PERCENT VALUES( 'Whitehead, Charise', 1, 85, 1, 100, 0139, '7/11/05 1:11:34') 

SELECT	#ACW_SCORE.ASPECT_ID, 
	QPP.AGENT_NAME, 
	#ACW_SCORE.ACWPct, 
	#ACW_SCORE.ATT AS [ATT in sec.], 
	#COMPLIANCE_WITH_ID.AverageCompliance, 
	QPP.TotalScores, 
	QPP.AverageScore, 
	QPP.TotalPasses, QPP.PassPct, 
	#COMPLIANCE_WITH_ID.ADHERENCE_DTE, 
	QPP.EVALUATION_DTE, 
	#ACW_SCORE.AUDIT_DTE
FROM	#ACW_SCORE 
	INNER JOIN #COMPLIANCE_WITH_ID 
		ON #ACW_SCORE.ASPECT_ID = #COMPLIANCE_WITH_ID.ASPECT_ID 
	INNER JOIN
	(
		SELECT	#QUALITY_PASS_PERCENT.AGENT_NAME, 
			#QUALITY_PASS_PERCENT.TotalScores, 
			#QUALITY_PASS_PERCENT.AverageScore, 
			#QUALITY_PASS_PERCENT.TotalPasses, 
			#QUALITY_PASS_PERCENT.PassPct, 
			#QUALITY_PASS_PERCENT.HSCID, 
			MAX( #QUALITY_PASS_PERCENT.EVALUATION_DTE) AS EVALUATION_DTE 
		FROM #QUALITY_PASS_PERCENT 
		GROUP BY #QUALITY_PASS_PERCENT.AGENT_NAME, 
			#QUALITY_PASS_PERCENT.TotalScores, 
			#QUALITY_PASS_PERCENT.AverageScore, 
			#QUALITY_PASS_PERCENT.TotalPasses, 
			#QUALITY_PASS_PERCENT.PassPct, 
			#QUALITY_PASS_PERCENT.HSCID
	) QPP 
		ON #ACW_SCORE.ASPECT_ID = QPP.HSCID

DROP TABLE #ACW_SCORE 
DROP TABLE #COMPLIANCE_WITH_ID 
DROP TABLE #QUALITY_PASS_PERCENT

Kristen

Salnick4
Starting Member

19 Posts

Posted - 2005-08-17 : 16:29:12

Kristen,

I appreciate your analysis of the code, and your susggestions..

Could you provide me with some clarification of the code you provided below:

SELECT DISTINCT
[Included?] = CASE WHEN #COMPLIANCE_WITH_ID.ASPECT_ID IS NOT NULL THEN 'Yes' ELSE '' END,
#ACW_SCORE.*
FROM #ACW_SCORE
LEFT OUTER JOIN #COMPLIANCE_WITH_ID
ON #ACW_SCORE.ASPECT_ID = #COMPLIANCE_WITH_ID.ASPECT_ID
ORDER BY #ACW_SCORE.ASPECT_ID, #ACW_SCORE.AUDIT_DTE

SELECT DISTINCT
[Included?] = CASE WHEN #ACW_SCORE.ASPECT_ID IS NOT NULL THEN 'Yes' ELSE '' END,
#COMPLIANCE_WITH_ID.*
FROM #COMPLIANCE_WITH_ID
LEFT OUTER JOIN #ACW_SCORE
ON #ACW_SCORE.ASPECT_ID = #COMPLIANCE_WITH_ID.ASPECT_ID
ORDER BY #COMPLIANCE_WITH_ID.ASPECT_ID, #COMPLIANCE_WITH_ID.ADHERENCE_DTE

When I reviewed my data, based on your comments, I found that there is a common field on all 3 tables that should be used within the JOIN...that is the ID field ( ex: ACW_SCORE.ASPECT_ID >>> COMPLIANCE_WITH_ID.ASPECT_ID >>>>> QUALITY_PASS_PERCENT.HSCID)

There is also the dates that can be used in the JOIN, but I am not sure about that...

My final output should allow the Client to use a date range so that they can pull the report...but which date do I use, or should it be within the WHERE clause for a 3 dates?

Thank you for all that you have done to assist me thus far!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources