How to JOIN 3 views Into 1 View???
-
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.
-
I am not understanding the question. Should the dates in the final 3 fields of this query be the same for each record? What is the problem with the output?
Thanks
I wasn't born stupid - I had to study.
-
No - I think this is a case of outer joins...
it would help if we had the ddls of the 3 tables, some sample data and required output!
**ASCII stupid question, get a stupid ANSI !!!** -
I agree...I am in the process of providing the details...
This is what I have so far...please bear with me, I am still learning how to provide DDL and DML for these 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)
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
-
Could you give us the same output, but chose one or two ASPECT_ID's (which should match the HSCID)? Thanks.
That way I can see the dates you are hoping to get. If you could also post the output from your query using the same ASPECT_ID's, that would be great.
That way, we can see what you are hoping to get and what you are getting.... Shushila, (or someone else) may already be able to see this issue, but I am not sure what problem you are having...
I wasn't born stupid - I had to study.
-
Ok...here you go:
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/7/05_________7609_______4.3________242.4
COMPLIANCE_WITH_ID:
ASPECT_ID_______Average Compliance %_____ADHERENCE_DTE
__0303_________________94_________________7/27/05
__5732_________________97_________________7/20/05
__7609_________________85_________________7/7/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 the above help? I placed the data for the same ID for all 3 views...they are for ID 7609...
I am not sure what you want in your 2nd request for an output?...Do you want to see what it looks like when I have the date fields included within the query?
Thanks
-
Salvatore - what Farrell is asking for is the results that you get from the query that you posted at the beginning of this thread...also the results that you actually want to see - - when you include the date fields it is obviously not giving you what you want so post the results that you ARE expecting from querying WITH DATES and we'll try and figure out the query to get you there!
**ASCII stupid question, get a stupid ANSI !!!** -
Sushila is absolutely correct.
I am guessing you are getting the following:
ASPECT_ID, AGENT_NAME, [ACW %], [ATT in sec], [Average Compliance %], [Total Scores], ADHERENCE_DTE, EVALUATION_DTE, AUDIT_DTE
7609 Alexander, Antuan, 4.3, 242.4, 85, 1, 7/7/05 7/7/05 5:40:02 PM
7609 Alexander, Antuan, 4.3, 242.4, 85, 1, 7/7/05 7/7/05 5:53:28 PM
7609 Alexander, Antuan, 4.3, 242.4, 85, 1, 7/7/05 7/14/05 12:38:56 AM
[Hopefully this will print legibly...
And, maybe, you are hoping for one or two records only? Audit_Dte has multiple values, which may return repeated data... It may be as simple as dropping the timestamp and Grouping...
I wasn't born stupid - I had to study.
-
here're the ddls & sample data for the 3 tables...salvatore - now you just have to explain what results you're looking for:
create table ACW_SCORE
(AUDIT_DTE smalldatetime,
ASPECT_ID int,
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)
create table COMPLIANCE_WITH_ID
(ASPECT_ID int,
AverageCompliance int,
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')
create table QUALITY_PASS_PERCENT
(AGENT_NAME varchar(50),
TotalScores tinyint,
AverageScore int,
TotalPasses tinyint,
PassPct tinyint,
HSCID int,
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')
**ASCII stupid question, get a stupid ANSI !!!** -
The results should be 1 record for each ID, this should be taken care of within the JOIN...the problem is that the output gets messed up when a date is used within the SELECT..
Below is a sample of what I see when the date is included:
AUDIT_DTE____ASPECT_ID______AGENT_NAME_______ACW %_____ATT in sec.__Average Compliance %__Total Scores__Average Score__Total Passes__Pass %
5/19/05__4306_Freeland, Chandra_9.5__207.8__91__1__82_0__100
5/19/05__4306_Freeland, Chandra_9.5__207.8_74__1__82__0_100
5/19/05__4306_Freeland, Chandra_9.5__207.8__92__1__82__0_100
It seems to ungroup and then it shows the first record that the date is associated with and puts that in the output..
Below is what the output should look like:
ASPECT_ID__AGENT_NAME____ACW %__ATT in sec.__Average Compliance %__Total Scores__Average Score__Total Passes__Pass %
4306___Freeland, Chandra__9.5__207.8__91__1__82__0__100
2315___Jones, Mike___9.5__207.8_74__1__82__0___100
0786___Matthews, Sally___9.5__207.8__92__1__82__0__100
The date range would be within the WHERE clause but not shown in the output..
Does this help?
-
Here is one possible solution using Sushila's DDL & sample data and the maximum #QUALITY_PASS_PERCENT.EVALUATION_DTE, (you can decide how to choose that date, but you need to GROUP upon that field as it causing you multiple records returned)...
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)
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')
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')
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
I wasn't born stupid - I had to study.
-
I apologize for my ignorance, but I am a bit confused as to what I should do with the DDL and sample data that you provided...
Am I to replace my code within the view with the code you provided?
Or is it that I need to GROUP BY the QUALITY_PASS_PERCENT.EVALUATION_DTE?
Also what is meant by the 'maximum #QUALITY_PASS_PERCENT.EVALUATION_DTE'?
I appreciate your patience with me...
-
I apologize for my ignorance, but I am a bit confused as to what I should do with the DDL and sample data that you provided...
Salvatore - you don't have to do anything with the ddls and the sample data - that was provided so that testing the code becomes much easier if you have sample data to work with..YOU have the data and the tables, but for anyone at ssc wanting to help it speeds up the process when this is available!
Am I to replace my code within the view with the code you provided?
run the select statement that Farrell provided in your query analyzer - if the results are what you're looking for, then by all means replace the code within the view with what farrell sent.
Or is it that I need to GROUP BY the QUALITY_PASS_PERCENT.EVALUATION_DTE?
I'm not sure about this as I'm still not clear on what date filters you want to set!
Also what is meant by the 'maximum #QUALITY_PASS_PERCENT.EVALUATION_DTE'?
I believe farrell was getting the max(date) because he assumed that if there were (eg
3 dates for a given ID, then you want only the most recent date...If farrell's solution is not what you're looking for ...why don't you post the t-sql within your view (WITH WHATEVER DATE FILTERS YOU WANT TO SET) that YOU are testing right now...so post the code, the results that you get from running that code - then the results you want to see!
Unlike farrell I was born stupid and I'm still studying...
**ASCII stupid question, get a stupid ANSI !!!** -
Thank you sushila! That is better than how I would have answered. Exactly correct!
Salvatore, the ball is in your court now...
I wasn't born stupid - I had to study.
-
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!
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply