How to JOIN 3 views Into 1 View???
-
farrell - the only other request i would make of salvatore is to post sample data where we have the same set of IDs in each table - copying and pasting from one of the posts above:
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.
Otherwise, what we need to resolve this is exactly what you said farrell -
1) which of the 3 date columns are we querying in the between operator ?!
2) when you get multiple rows from the query, how do you identify the one you want - in other words - if you get 3 rows with 3 different dates, do you want to pick the most recent, just pick any date, oldest date etc..
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks buddy.
I wasn't born stupid - I had to study.
-
I can not say enough in appreciation for both Farrell and Sushila's support and suggestions...
To answer your questions, I am not sure which date field to use for the range???...and here is my reason:
When a date range is selected, and let's say we use the ACW_SCORE.AUDIT_DTE field, the output of data would need to pull from the other 2 tables based on the same date range.
This means that if I ask to query BETWEEN '8/1/05' AND '8/19/05' from the ACW_SCORE.AUDIT_DTE, I will need to also pull that same date range from COMPLIANCE_WITH_ID.ADHERENCE_DTE & QUALITY_PASS_PERCENT.EVALUATION_DTE...
The output then should be 1 row per agent, that is because I am trying to use aggregate function to SUM, AVG, and COUNT these fields under their appropriate headings...
So when you look back at our previous memo's within this thread, you can see that I am trying to get the 1 row of data per agent ID, by way of using the appropriate JOINS...
Does this make sense? I will be more than happy to provide any sample data you may require...I am wondering though whether the problem lies in accurate JOINS????
What do you think?
-
Salvatore - FINALLY....the logic that Farrell and I were looking for....
Again, I apologise for not having the time to fully test this as I would like to - but I am way behind at work - maybe if my solution doesn't work, it'll at least get you started in the right direction and/or Farrell will be back...
I have "guessed at" the avg, sum and count...pl. change whereever wrong...
SELECT CWID.ASPECT_ID, QPP.AGENT_NAME, AVG(ACW.ACWPct) AS ACWPct, AVG(ACW.ATT) AS [ATT in sec.], AVG(CWID.AverageCompliance) AS AverageCompliance, SUM(QPP.TotalScores) AS TotalScores, AVG(QPP.AverageScore) AS AverageScore, COUNT(QPP.TotalPasses) AS TotalPasses, AVG(QPP.PassPct) AS PassPct, MAX(CWID.ADHERENCE_DTE) AS ADHERENCE_DTE, MAX(QPP.EVALUATION_DTE) AS EVALUATION_DTE, MAX(ACW.AUDIT_DTE) AS AUDIT_DTE FROM ACW_SCORE ACW INNER JOIN COMPLIANCE_WITH_ID CWID ON ACW.ASPECT_ID = CWID.ASPECT_ID INNER JOIN QUALITY_PASS_PERCENT QPP ON CWID.ASPECT_ID = QPP.HSCID WHERE (ACW.AUDIT_DTE BETWEEN '07/10/05' AND '07/14/05') OR (CWID.ADHERENCE_DTE BETWEEN '07/10/05' AND '07/14/05') OR (QPP.EVALUATION_DTE BETWEEN '07/10/05' AND '07/14/05') GROUP BY CWID.ASPECT_ID, QPP.AGENT_NAME ORDER BY CWID.ASPECT_ID
**ASCII stupid question, get a stupid ANSI !!!** -
salvatore - I just revisited the script I posted....I think this is what it should actually be...
or at least something like this...depending on what you want...you may need to "play around" with it a little...
SELECT CWID.ASPECT_ID, QPP.AGENT_NAME, AVG(ACW.ACWPct) AS ACWPct, AVG(ACW.ATT) AS [ATT in sec.], AVG(CWID.AverageCompliance) AS AverageCompliance, SUM(QPP.TotalScores) AS TotalScores, AVG(QPP.AverageScore) AS AverageScore, COUNT(QPP.TotalPasses) AS TotalPasses, AVG(QPP.PassPct) AS PassPct, CASE WHEN (MAX(CWID.ADHERENCE_DTE) BETWEEN '07/10/05' AND '07/14/05') THEN CONVERT(VarChar(16), MAX(CWID.ADHERENCE_DTE), 120) ELSE 'NO ADHERENCE' END ADHERENCE_DTE, CASE WHEN (MAX(QPP.EVALUATION_DTE) BETWEEN '07/10/05' AND '07/14/05') THEN CONVERT(VarChar(16), MAX(QPP.EVALUATION_DTE), 120) ELSE 'NO EVALUATION' END EVALUATION_DTE, CASE WHEN (MAX(ACW.AUDIT_DTE) BETWEEN '07/10/05' AND '07/14/05') THEN CONVERT(VarChar(16), MAX(ACW.AUDIT_DTE), 120) ELSE 'NO AUDIT' END AUDIT_DTE FROM ACW_SCORE ACW INNER JOIN COMPLIANCE_WITH_ID CWID ON ACW.ASPECT_ID = CWID.ASPECT_ID INNER JOIN QUALITY_PASS_PERCENT QPP ON CWID.ASPECT_ID = QPP.HSCID WHERE (ACW.AUDIT_DTE BETWEEN '07/10/05' AND '07/14/05') OR (CWID.ADHERENCE_DTE BETWEEN '07/10/05' AND '07/14/05') OR (QPP.EVALUATION_DTE BETWEEN '07/10/05' AND '07/14/05') GROUP BY CWID.ASPECT_ID, QPP.AGENT_NAME ORDER BY CWID.ASPECT_ID
**ASCII stupid question, get a stupid ANSI !!!** -
Excellent!
FYI: you do not have to specify the length when you convert a date choosing one of the format numbers.
For instance: SELECT CONVERT( varchar, GETDATE(), 120)
SELECT CONVERT( varchar, GETDATE(), 101)
I wasn't born stupid - I had to study.
-
Based on the suggessted code from Sushila I made a slight adjustment based on my tables:
SELECT CWID.ASPECT_ID, QPP.AGENT_NAME, AVG(ACW.[ACW %]) AS ACWPct, AVG(ACW.[ATT in sec.]) AS [ATT in sec.], AVG(CWID.[Average Compliance %])
AS AverageCompliance, SUM(QPP.[Total Scores]) AS TotalScores, AVG(QPP.[Average Score]) AS AverageScore, COUNT(QPP.[Total Passes])
AS TotalPasses, AVG(QPP.[Pass %]) AS PassPct, CASE WHEN (MAX(CWID.ADHERENCE_DTE) BETWEEN '07/10/05' AND '07/14/05')
THEN CONVERT(VarChar(16), MAX(CWID.ADHERENCE_DTE), 120) ELSE 'NO ADHERENCE' END ADHERENCE_DTE,
CASE WHEN (MAX(QPP.EVALUATION_DTE) BETWEEN '07/10/05' AND '07/14/05') THEN CONVERT(VarChar(16), MAX(QPP.EVALUATION_DTE), 120)
ELSE 'NO EVALUATION' END EVALUATION_DTE, CASE WHEN (MAX(ACW.AUDIT_DTE) BETWEEN '07/10/05' AND '07/14/05')
THEN CONVERT(VarChar(16), MAX(ACW.AUDIT_DTE), 120) ELSE 'NO AUDIT' END AUDIT_DTE
FROM ACW_SCORE ACW INNER JOIN
COMPLIANCE_WITH_ID CWID ON ACW.ASPECT_ID = CWID.ASPECT_ID INNER JOIN
QUALITY_PASS_PERCENT QPP ON CWID.ASPECT_ID = QPP.HSCID
WHERE (ACW.AUDIT_DTE BETWEEN '07/10/05' AND '07/14/05') OR
(CWID.ADHERENCE_DTE BETWEEN '07/10/05' AND '07/14/05') OR
(QPP.EVALUATION_DTE BETWEEN '07/10/05' AND '07/14/05')
GROUP BY CWID.ASPECT_ID, QPP.AGENT_NAME
ORDER BY CWID.ASPECT_ID
Now here is what I received in the results:
ASPECT_ID____AGENT_NME____ACWpct___ATTinsec___AVERAGEcompliance____TOTALSCORES___AVERAGESCORE__TOTALPASSES__PASSpct___ADHERENCEdte____EVALUATIONdte____AUDITdte
4306______Freeland, Chandra____9.5_____207.8___82___55__82__55__45.4545454545455___2005-07-14 00:00__NO EVALUATION__NO AUDIT
4417______Workman, Stacie_____6______142.7__94__28__92__28__100__2005-07-14 00:00__NO EVALUATION__NO AUDIT
I apologize for the poor formating of the results shown here...but what I do not understand is why I only received 2 agents...
Is this because of the WHERE clause ...which filtered out all others and only those that did not meet that criteria??
I am still playing with it...but I wanted your feedback on this...
Thanks
-
salvatore - could you pl. query the 3 tables with the date filter...
(ACW.AUDIT_DTE BETWEEN '07/10/05' AND '07/14/05') (CWID.ADHERENCE_DTE BETWEEN '07/10/05' AND '07/14/05') (QPP.EVALUATION_DTE BETWEEN '07/10/05' AND '07/14/05')
and post the results you get from each...that will help!
**ASCII stupid question, get a stupid ANSI !!!** -
Here is where I think the problem lies, and let me knowe if you agree..
The code that Sushila provided was sucessful, once I adjusted the JOIN to include the LTRIM function on the ACW.ASPECT_ID...the results were almost exactly what I am looking for...the only exception was the aggregate functions that were used...
Sushila used the following:
SELECT CWID.ASPECT_ID, QPP.AGENT_NAME, AVG(ACW.ACWPct) AS ACWPct,AVG(ACW.ATT) AS [ATT in sec.], AVG(CWID.AverageCompliance) AS AverageCompliance,SUM(QPP.TotalScores) AS TotalScores,AVG(QPP.AverageScore) AS AverageScore, COUNT(QPP.TotalPasses) AS TotalPasses,AVG(QPP.PassPct) AS PassPct
...but for me those views (CWID, QPP, ACW) all ready have aggregate functions done within them, so I was trying to just pull their fields into another view that would use the JOIN and WHERE on the dates and retrieve 1 row per agent...
If you look back at my original SQL code you will see this..
So when I attempt to use your code but remove the aggragate functions, then I have to include those fields within the GROUP BY, and that seems to throw a wrench in my results...
I guess I will have to re-look at my supporting queries and see how I can create just one view by incorporating the combined aggregate functions into one...
I probably sound like a maniac...and I have to admit this issue has gotten almot to that point...
If there is anything that I need to clarify, or if you think that I can do something else based on what I just stated...I am willing to listen and learn!
THANK YOU!
-
salvatore - i apologise but i cannot take a look at this until later on today...farrell - do you have the time ?!
if everything is already "aggregated" then you may just want to do a max(colName) everywhere that i've done a sum, avg, count etc...and see if that gets you the results you want...if not - just post the results of querying the 3 tables with those 3 date filters and we'll see if that throws any light on this...
**ASCII stupid question, get a stupid ANSI !!!** -
also...unless there is a compelling reason to use varchar datatypes for your ID columns, you'd be better off using integers....(meant to say that as soon as i read you had to use ltrim & then just forgot in the hurry i was in...)
**ASCII stupid question, get a stupid ANSI !!!** -
I am sorry - I have not been following this as I should have.
Salvatore, could you post what sushila asked. That would probably give us the best approach at getting back into this...
Thanks.
I wasn't born stupid - I had to study.
-
I think that I am finnaly seeing some light at the end of the tunnel...after makeing some slight adjustments to the code, I have been successful in seeing 1 row per agent...
The issue is whether or not the aggregate functions that I forced within this SQL code is actually bringing over the correct data from their respective tables...for some fields it looks like the data is off by a small percentage...
Also, the CASE statements for the 3 date fields within the SQL are giving the following output:
0131 Boyd, Lacrecia CRC UM Ferri, Lynne 5.1 209.8 94 7 87 5 71.0 NO ADHERENCE NO EVALUATION NO AUDIT
0134 Lewis, Edwin CRC UM Maue, Tina 10.8 152.8 90 9 67 7 78.0 NO ADHERENCE NO EVALUATION NO AUDIT
So...now when I change the WHERE clause I do not beleive that the data is changing???
Any suggestions??
-
salvatore - for either farrell or me (or anyone else) to see any light at all you really will have to query the 3 tables with the same date range and post the results - otherwise we would only be coming up with guesses at best....
please, please, please send us the output of the 3 queries before asking for any more suggestions...shots in the dark could only result in some more "misses" than "the single hit" that we're looking for...
**ASCII stupid question, get a stupid ANSI !!!** -
Here is the results from Sushila's request - 'salvatore - could you pl. query the 3 tables with the date filter...
(ACW.AUDIT_DTE BETWEEN '07/10/05' AND '07/14/05') (CWID.ADHERENCE_DTE BETWEEN '07/10/05' AND '07/14/05') (QPP.EVALUATION_DTE BETWEEN '07/10/05' AND '07/14/05')
ACW_SCORE:
ASPECT_ID____ACW %____ATT in sec.____AUDIT_DTE
___4333______8.4_________353.4________7/12/05
___0782______0___________299.3________7/12/05
___4683______8.8_________140.4________7/12/05
___6698______6.5_________187.2________7/14/05
___1665______6___________304.8________7/12/05
___0710______4.1_________209__________7/13/05
CWID:
ASPECT_ID____AVERAGE COMPLIANCE____ADHERENCE_DTE
___4333____________90_________________7/13/05
___4683____________78_________________7/13/05
___0644____________95_________________7/11/05
___0273____________79_________________7/11/05
___1967____________89_________________7/13/05
QPP:
HSCID__AGENT NAME__UNIT MANAGER__FORM_NAME__EVALUATION DATE__Total Scores__AVG Score__Total Passes__Pass %
__0146__Trinh, Hoang__CRC UM Johnson, Linda__CRC Test Form__7/13/05 4:11:14 PM__6__96__5__83
__0274__Aviles, Cheryl__CRC UM Santiago, Mercedes__DB Customer Care1__7/11/05 1:38:45 PM__2__41__0__0
__0298__Coon, Vernida__CRC UM Stachowiak, Mary__DB Customer Care1__7/11/05 2:14:34 PM__2__83__1__50
__0447__Breen, Kelly__CRC UM Ductor, Heather__CRC Test Form__7/13/05 7:34:26 PM__1__100__1__100
__0674__MacDowell, Nicole__CRC UM Ductor, Heather__CRC Test Form__7/13/05 5:19:08 PM__1__100__1__100
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply