How to JOIN 3 views Into 1 View???
-
I used the code that Farrell provided, and the results seem to be ok...the only thing that I am not sure is, that the view that has the EVALUATION_DTE field, has multiple records for the same date for the one agent id???
So, would I not need to first group this view so that it's output is showing 1 record per agent id for each date, and then use this view within the code that Farrell provided?
Thank you very much so far...it has been a great learning lesson...
Sometimes I feel that I was born stupid!
-
.the only thing that I am not sure is, that the view that has the EVALUATION_DTE field, has multiple records for the same date for the one agent id???
So, would I not need to first group this view so that it's output is showing 1 record per agent id for each date, and then use this view within the code that...
yes - you could group by agentID & pick the max(evaluation_dte) if that's what you want! The best way to see if it works is to "play around" with the code and run it to see what results you get...
Sometimes I feel that I was born stupid!....IF I had even a penny for each time I thought that I'd be obscenely rich!
**ASCII stupid question, get a stupid ANSI !!!** -
I placed the T-SQL into my view, and the output is still showing just 1 agent id...I noticed this was also the case when I placed Farrel's code within the Query Analyzer...it gave me the following results:
7609__Alexander, Antuan__4.3__242.4__85__1__87__1__100__2005-07-07 00:00:00__2005-07-14 12:39:00__2005-07-07 00:00:00
AGENT_NAME__ASPECT_ID__ACW %__ATT in sec.__Average Compliance %__Total Scores__Average Score__Total Passes__Pass %
Boyd, Lacrecia__0131__5.5__214.6__93__7__73__4__57
Lewis, Edwin__0134__10.6__151.8__88__9__67__7__78
Whitehead, Charise__0139__0__199.2__77__7__71__4__57
I apologize for the way this is placed here...
So, I will need to find a way to keep the grouping but add the element of the dates...
-
My last entry was supposed to have said that when I removed the date fields from the 3 views that I was querying, I receive accurate results...the following is a sample of the correct output:
AGENT_NAME__ASPECT_ID__ACW %__ATT in sec.__Average Compliance %__Total Scores__Average Score__Total Passes__Pass %
Boyd, Lacrecia__0131__5.5__214.6__93__7__73__4__57
Lewis, Edwin__0134__10.6__151.8__88__9__67__7__78
Whitehead, Charise__0139__0__199.2__77__7__71__4__57
-
You initmated that you needed dates for a BETWEEN statement within your code. Is that still necessary? If so, could you show us the code and explain what you want? GROUPing, (as sushila noted) will be the only way you can deal with these multiple records...
I wasn't born stupid - I had to study.
-
Salvatore - it appears that you ran farrell's query against the temp tables that we had created for testing and not against your actual tables....his query should work as you expect (or we think you expect
....I inserted some more sample rows in the 3 tables and his query "seemed" to get what you want....
here're some of the things you can do to help us help you:
1) run farrell's query against YOUR tables - some of the column names were changed (during testing) for convenience so just change those back to whatever the names are in your tables.
2) if you don't get the expected results, please run the following queries and post the results of each query:
a) "select * from ACW_SCORE where ASPECT_ID between 6500 and 7000 order by ASPECT_ID"
b) "select * from COMPLIANCE_WITH_ID where ASPECT_ID between 6500 and 7000 order by ASPECT_ID"
c) "select * from QUALITY_PASS_PERCENT where HSCID between 6500 and 7000 order by HSCID"
Pl. note - I just selected "between 6500 and 7000" - not sure how much data you have in your tables against the various IDs...you may change them to whatever will return about 10-15 rows from each table.
d) now post the query AND results from your first post.
e) tell us what is wrong with that resultset - between the 3 dates in the 3 tables which ones do you want to "query on" and "how".
f) finally pretend you have a query that works exactly as you want it to - and if it did what results would you see?!?! - post those results!
in case farrell (or someone else) happens upon this post in the morning, I'm posting again the 3 tables with the additional sample rows...
REATE 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')
**ASCII stupid question, get a stupid ANSI !!!** -
I ran the new table data within the Query Analyzer, and the results I received were PERFECT!
The problem is placing that T-SQL code within the view that I am creating...In my previous replies I did use Farrel's code both within the Query Analyzer as well as the view, and that is where I posted the output I was receiving...
So what I am going to do now is show you what the views look like that I am attempting to join into one view:
QUALITY_PASS_PERCENT
CREATE VIEW dbo.QUALITY_PASS_PERCENT
AS
SELECT dbo.QUALITY_AVG_SCORE.AGENT_NAME, dbo.QUALITY_AVG_SCORE.[Total Scores], dbo.QUALITY_AVG_SCORE.[Average Score],
dbo.QUALITY_AVG_SCORE.[Total Passes], ROUND(CAST(CAST(dbo.QUALITY_AVG_SCORE.[Total Passes] AS FLOAT)
/ CAST(dbo.QUALITY_AVG_SCORE.[Total Scores] AS FLOAT) AS FLOAT(4, 0)) * 100, 0) AS [Pass %], dbo.QUALITY_AVG_SCORE.HSCID,
dbo.QUALITY_AVG_SCORE.FORM_NAME
FROM dbo.QUALITY_AVG_SCORE INNER JOIN
dbo.QUALITY_AVG_SCORE QUALITY_AVG_SCORE_1 ON dbo.QUALITY_AVG_SCORE.HSCID = QUALITY_AVG_SCORE_1.HSCID
GROUP BY dbo.QUALITY_AVG_SCORE.HSCID, dbo.QUALITY_AVG_SCORE.AGENT_NAME, dbo.QUALITY_AVG_SCORE.[Total Scores],
dbo.QUALITY_AVG_SCORE.[Total Passes], dbo.QUALITY_AVG_SCORE.[Average Score], dbo.QUALITY_AVG_SCORE.FORM_NAME
ACW_SCORE
CREATE VIEW dbo.ACW_SCORE
AS
SELECT 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.], AUDIT_DTE
FROM GROUP_AUDIT
GROUP BY ASPECT_ID, AUDIT_DTE
COMPLIANCE_WITH_ID
CREATE VIEW dbo.COMPLIANCE_WITH_ID
AS
SELECT dbo.CRC_USER.ASPECT_ID, AVG(dbo.ADHERENCE.TOTAL_COMPLIANCE) AS [Average Compliance %], dbo.ADHERENCE.ADHERENCE_DTE
FROM dbo.ADHERENCE INNER JOIN
dbo.EMPLOYEE_MANAGER ON LTRIM(dbo.ADHERENCE.EMPLOYEE_ID) = dbo.EMPLOYEE_MANAGER.EMPLOYEE_ID INNER JOIN
dbo.CRC_USER ON dbo.EMPLOYEE_MANAGER.EMPLOYEE_ID = dbo.CRC_USER.EMPLOYEE_ID
GROUP BY dbo.CRC_USER.ASPECT_ID, dbo.ADHERENCE.ADHERENCE_DTE
Just so that I don't miss anything, the QUALITY_PASS_PERCENT view is pulling from another view called QUALITY_AVG_SCORE, and here is how that view looks:
QUALITY_AVG_SCORE
CREATE VIEW dbo.QUALITY_AVG_SCORE
AS
SELECT FORM_NAME, AGENT_NAME, HSCID, COUNT(AGENT_NAME) AS [Total Scores], AVG(QUALITY_SCORE) AS [Average Score],
SUM(CASE WHEN QUALITY_SCORE > dbo.get_agent_id(HSCID) THEN 1 ELSE 0 END) AS [Total Passes], EVALUATION_DTE
FROM dbo.CRC_QUALITY
GROUP BY HSCID, AGENT_NAME, FORM_NAME, EVALUATION_DTE
So I will continue to play around with placing the code that you all provided (that works) into a view ...If you see anything that could be the cause to my problems, please let me know..
-
Below is the T-SQL that I am using based on the suggestion from Farrel:
SELECT dbo.ACW_SCORE.ASPECT_ID, QUALITY_PASS_PERCENT.AGENT_NAME, dbo.ACW_SCORE.[ACW %], dbo.ACW_SCORE.[ATT in sec.],
dbo.COMPLIANCE_WITH_ID.[Average Compliance %], QUALITY_PASS_PERCENT.[Total Scores], QUALITY_PASS_PERCENT.[Average Score],
QUALITY_PASS_PERCENT.[Total Passes], QUALITY_PASS_PERCENT.[Pass %], dbo.COMPLIANCE_WITH_ID.ADHERENCE_DTE,
QUALITY_PASS_PERCENT.EVALUATION_DTE, dbo.ACW_SCORE.AUDIT_DTE
FROM dbo.ACW_SCORE INNER JOIN
dbo.COMPLIANCE_WITH_ID ON LTRIM(dbo.ACW_SCORE.ASPECT_ID) = dbo.COMPLIANCE_WITH_ID.ASPECT_ID INNER JOIN
(SELECT QUALITY_PASS_PERCENT.AGENT_NAME, QUALITY_PASS_PERCENT.[Total Scores], QUALITY_PASS_PERCENT.[Average Score],
QUALITY_PASS_PERCENT.[Total Passes], QUALITY_PASS_PERCENT.[Pass %], 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.[Total Scores], QUALITY_PASS_PERCENT.[Average Score],
QUALITY_PASS_PERCENT.[Total Passes], QUALITY_PASS_PERCENT.[Pass %], QUALITY_PASS_PERCENT.HSCID)
QUALITY_PASS_PERCENT ON dbo.COMPLIANCE_WITH_ID.ASPECT_ID = QUALITY_PASS_PERCENT.HSCID
Now here is a sample of the results I receive:
ASPECT_IDAGENT_NAMEACW %ATT in sec.Average Compliance %Total ScoresAverage ScoreTotal PassesPass %ADHERENCE_DTEEVALUATION_DTEAUDIT_DTE
6330Alaimo, Joseph8311.294148004/27/057/15/05 12:46:57 PM6/4/05
6330Alaimo, Joseph0226.194148004/27/057/15/05 12:46:57 PM7/19/05
6330Alaimo, Joseph3.6378.694148004/27/057/15/05 12:46:57 PM8/3/05
6330Alaimo, Joseph0378.294148004/27/057/15/05 12:46:57 PM7/25/05
6330Alaimo, Joseph10.5300.694148004/27/057/15/05 12:46:57 PM5/19/05
Do you see anything that I am doing to mess this up?
When you provided the Sample Table data to test within the Query Analyzer, I noticed that there was a table called QPP, but I could not locate where it was CREATED, because it is being referred to much the same as the QUALITY_PASS_PERCENT, but I was not sure....
So I hope that I am getting closer...
-
Salvatore - busy morning so can't test this right now - but it appears that what you noticed about QPP and your omission of it is the problem - it is not being created anywhere because it is what is called a "derived table" - sorta created "on the fly"...
when farrell said in his query - QPP.ASPECT_ID - that's exactly what he was saying - get me the ASPECT_ID from my derived table - not the QUALITY_PASS_PERCENT table...this should work - just use farrell's query exactly as it is...
SELECT dbo.ACW_SCORE.ASPECT_ID, QPP.AGENT_NAME, dbo.ACW_SCORE.[ACW %], dbo.ACW_SCORE.[ATT in sec.],
dbo.COMPLIANCE_WITH_ID.[Average Compliance %], QPP.[Total Scores], QPP.[Average Score],
QPP.[Total Passes], QPP.[Pass %], dbo.COMPLIANCE_WITH_ID.ADHERENCE_DTE,
QPP.EVALUATION_DTE, dbo.ACW_SCORE.AUDIT_DTE
FROM dbo.ACW_SCORE
INNER JOIN dbo.COMPLIANCE_WITH_ID ON LTRIM(dbo.ACW_SCORE.ASPECT_ID) = dbo.COMPLIANCE_WITH_ID.ASPECT_ID
INNER JOIN (SELECT QUALITY_PASS_PERCENT.AGENT_NAME, QUALITY_PASS_PERCENT.[Total Scores], QUALITY_PASS_PERCENT.[Average Score],
QUALITY_PASS_PERCENT.[Total Passes], QUALITY_PASS_PERCENT.[Pass %], 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.[Total Scores], QUALITY_PASS_PERCENT.[Average Score],
QUALITY_PASS_PERCENT.[Total Passes], QUALITY_PASS_PERCENT.[Pass %], QUALITY_PASS_PERCENT.HSCID)
QUALITY_PASS_PERCENT ON dbo.COMPLIANCE_WITH_ID.ASPECT_ID = QUALITY_PASS_PERCENT.HSCID) QPP
**ASCII stupid question, get a stupid ANSI !!!** -
I made the neccessary changes to the code, yet I am still geting the same results...
Below is the T-SQL code that I am using as well as the results:
CREATE VIEW dbo.test
AS
SELECT dbo.ACW_SCORE.ASPECT_ID, QPP.AGENT_NAME, dbo.ACW_SCORE.[ACW %], dbo.ACW_SCORE.[ATT in sec.],
dbo.COMPLIANCE_WITH_ID.[Average Compliance %], QPP.[Total Scores], QPP.[Average Score], QPP.[Total Passes], QPP.[Pass %],
dbo.COMPLIANCE_WITH_ID.ADHERENCE_DTE, QPP.EVALUATION_DTE, dbo.ACW_SCORE.AUDIT_DTE
FROM dbo.ACW_SCORE INNER JOIN
dbo.COMPLIANCE_WITH_ID ON LTRIM(dbo.ACW_SCORE.ASPECT_ID) = dbo.COMPLIANCE_WITH_ID.ASPECT_ID INNER JOIN
(SELECT QUALITY_PASS_PERCENT.AGENT_NAME, QUALITY_PASS_PERCENT.[Total Scores], QUALITY_PASS_PERCENT.[Average Score],
QUALITY_PASS_PERCENT.[Total Passes], QUALITY_PASS_PERCENT.[Pass %], 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.[Total Scores], QUALITY_PASS_PERCENT.[Average Score],
QUALITY_PASS_PERCENT.[Total Passes], QUALITY_PASS_PERCENT.[Pass %], QUALITY_PASS_PERCENT.HSCID) QPP ON
dbo.COMPLIANCE_WITH_ID.ASPECT_ID = QPP.HSCID
Results:
ASPECT_IDAGENT_NAMEACW %ATT in sec.Average Compliance %Total ScoresAverage ScoreTotal PassesPass %ADHERENCE_DTEEVALUATION_DTEAUDIT_DTE
6330Alaimo, Joseph8311.294148004/27/057/15/05 12:46:57 PM6/4/05
6330Alaimo, Joseph0226.194148004/27/057/15/05 12:46:57 PM7/19/05
6330Alaimo, Joseph3.6378.694148004/27/057/15/05 12:46:57 PM8/3/05
6330Alaimo, Joseph0378.294148004/27/057/15/05 12:46:57 PM7/25/05
6330Alaimo, Joseph10.5300.694148004/27/057/15/05 12:46:57 PM5/19/05
What do you think? As you can see the results are showing for one ID, and the dates are different...in contrast when I run Farrell's code as is within the Query Analyzer with the sample table data, it works fine???
I checked the data within that sample table data, and it looks just like what I have in my tables...
-
OK...I was able to recreate my problem with the sample data that sushila provided me....
Below is the code, please try it and you will see what I mean:
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
-
Salvatore - could you please do a "SELECT DISTINCT..." and then the rest of the query...?!?!
Also, re. the sample data I provided - I was just winging it...we really need to test with sample data that YOU provide or you have in YOUR database! This time round if it doesn't work, please post the sample data from the existing tables in your database!
Thanks.
**ASCII stupid question, get a stupid ANSI !!!** -
Sushila is the MASTER at understanding and interpretting! I have been too busy to get back to this, but her remarks fit my designs exactly! Thanks. [you fit Ten Centuries]
Here is a possible solution using Sushila's posted data. (I agree, Salvatore, YOU NEED TO USE YOUR OWN DATA! - if it returns too many records, try SELECT TOP 100.... that should retrun only 100 records and that should be enough to get a handle on what you doing.)
Please note, I have used the MAX function throughout these subselects (the "derived" tables) and that may not be the appropriate choice. For example, using MAX can get you 3 different dates and score values that actually have nothing to do with one another... You need to understand this before you decide what you will do in your query... it is very important to understand the data you will present to your Clients. Let us know what you find, for example, you want the most recent AUDIT_DTE and only those records which match that, i.e., not the MAX( scores). We need to know the relationships of these tables so we can specifically select the records you want to present. (This sounds as if it started as a View, (not just a select) and if that is the case, this will be the data you are presenting to the Client as valid...).
SELECT ACW.ASPECT_ID, QPP.AGENT_NAME, ACW.ACWPct AS ACWPct, ACW.ATT AS [ATT in sec.],
CID.AverageCompliance, QPP.TotalScores, QPP.AverageScore, QPP.TotalPasses, QPP.PassPct,
CID.ADHERENCE_DTE, QPP.EVALUATION_DTE, ACW.AUDIT_DTE
FROM( SELECT #ACW_SCORE.ASPECT_ID,
MAX( #ACW_SCORE.ACWPct) AS ACWPct,
MAX( #ACW_SCORE.ATT) AS ATT,
MAX( #ACW_SCORE.AUDIT_DTE) AS AUDIT_DTE
FROM #ACW_SCORE
GROUP BY #ACW_SCORE.ASPECT_ID) ACW
INNER JOIN( SELECT #COMPLIANCE_WITH_ID.ASPECT_ID,
MAX( #COMPLIANCE_WITH_ID.AverageCompliance) AS AverageCompliance,
MAX( #COMPLIANCE_WITH_ID.ADHERENCE_DTE) AS ADHERENCE_DTE
FROM #COMPLIANCE_WITH_ID
GROUP BY #COMPLIANCE_WITH_ID.ASPECT_ID) CID
ON( ACW.ASPECT_ID = CID.ASPECT_ID)
INNER JOIN( SELECT #QUALITY_PASS_PERCENT.HSCID, #QUALITY_PASS_PERCENT.AGENT_NAME,
MAX( #QUALITY_PASS_PERCENT.TotalScores) AS TotalScores,
MAX( #QUALITY_PASS_PERCENT.AverageScore) AS AverageScore,
MAX( #QUALITY_PASS_PERCENT.TotalPasses) AS TotalPasses,
MAX( #QUALITY_PASS_PERCENT.PassPct) AS PassPct,
MAX( #QUALITY_PASS_PERCENT.EVALUATION_DTE) AS EVALUATION_DTE
FROM #QUALITY_PASS_PERCENT
GROUP BY #QUALITY_PASS_PERCENT.HSCID, #QUALITY_PASS_PERCENT.AGENT_NAME) QPP
ON( ACW.ASPECT_ID = QPP.HSCID)
ORDER BY ACW.ASPECT_ID
DROP TABLE #ACW_SCORE
DROP TABLE #COMPLIANCE_WITH_ID
DROP TABLE #QUALITY_PASS_PERCENT
I wasn't born stupid - I had to study.
-
I have been away for a week, and now I am back, and trying to resolve this SQL issue...
My last attempt was to include the SELECT DISTINCT into the code...that did not fix the issue of the multiple records for an agent...
Sushila asked for TRUE sample data from my database, and from what I can see the sample data that I provided in my last reply on 8/5/2005 is data directly from the various tables...
Now Farrell mentioned the following:
Please note, I have used the MAX function throughout these subselects (the "derived" tables) and that may not be the appropriate choice. For example, using MAX can get you 3 different dates and score values that actually have nothing to do with one another... You need to understand this before you decide what you will do in your query... it is very important to understand the data you will present to your Clients. Let us know what you find, for example, you want the most recent AUDIT_DTE and only those records which match that, i.e., not the MAX( scores). We need to know the relationships of these tables so we can specifically select the records you want to present. (This sounds as if it started as a View, (not just a select) and if that is the case, this will be the data you are presenting to the Client as valid...).
The way this is supposed to work for the Clients is that I need to pull the various data elements from the tables into one table that will be outputted based on a date range...So I would query a date range of '7/1/05' and '7/31/05', and see the results...
The relationship between all tables is the ID field ( for example: ACW_SCORE.ASPECT_ID >>>>> COMPLIANCE_WITH_ID.ASPECT_ID >>>>> QUALITY_PASS_PERCENT.HSCID)
I am not sure if I should use the MAX function on the dates..I believe that I will need some assistance on setting the JOINS up properly...do you agree?
Does this info help?
Thank you!
-
Thank you for the explanation. When this post first started, you wanted only one record for each ASPECT_ID or AGENT_NAME. To that end, we can help you set this up. But you need to let us know what date the range goes against, (AUDIT_DTE, ADHERANCE_DTE, EVALUATION_DTE, etc.). Once we know that, then we need to know which record in the adjoining tables we need to choose? The most recent record? In other words, should we use the MAX date from that adjoining table?
That is the kind of information we need. Hopefully, sushila is checking this as well and can further refine my poorly worded questions.
I wasn't born stupid - I had to study.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply