How to JOIN 3 views Into 1 View???
-
thanks - will take a look at this tonight and get back to you...
**ASCII stupid question, get a stupid ANSI !!!** -
I submitted my last reply without adding that...the data that I provided was just a small sample...
When I do an ORDER BY ASPECT_ID..I would see multiple rows for the same agent, but with a different date...( I am sure you were all ready aware of that)...but I did not provide that view to you..so that you could see a variety of other agents...
Let me know if there is anything else you require..
-
salvatore - I have reached an impasse with this query....in the sample set provided -
a) barring one aspect_id which is common between acw_score and compliance_with_id tables, I don't see any other ids...so really cannot comprehend what tweaking you could possibly have done to get you one row each with an inner join...
b) secondly, as you say, you see multiple rows for the same agent when you query with a date range - I don't understand how you can get one single row for these agents unless you do an aggregate on the values....you still haven't specified what it is that you want if there is more than one row per agent...splly. when you say that all the averages, sums, counts etc..have already been done?!?!
I do have a query for you...but am not at all sure that this is what is required...I created 2 derived tables...one to union the results of all 3 tables since the agent_names/IDs are not common for these 3...and one table to query the quality_pass_percent table since I'm assuming that so long as there is an ID, there will be a corresponding agent_name in this table for that ID.
If this query doesn't work, I'm all out of what questions to ask you in order to get the right result...farrell/somebody else will have to take over - my apologies....
SELECT A.ASPECT_ID, B.AGENT_NAME, MAX(A.ACWPct) ACWPct, MAX(A.ATT) AS 'ATT in sec.', MAX(A.AverageCompliance) AS AverageCompliance, MAX(A.TotalScores) AS TotalScores, MAX(A.AverageScore) AS AverageScore, MAX(A.TotalPasses) TotalPasses, MAX(A.PassPct) AS PassPct, MAX(A.DTE) DATE FROM (SELECT ASPECT_ID, '' AS AGENT_NAME, ACWPct, ATT, 0 AS AverageCompliance, 0 AS TotalScores, 0 AS AverageScore, 0 AS TotalPasses, 0 AS PassPct, DTE = CASE WHEN (AUDIT_DTE BETWEEN '07/10/05' AND '07/14/05') THEN CONVERT(VarChar, AUDIT_DTE, 120) ELSE 'NO AUDIT' END FROM ACW_SCORE WHERE AUDIT_DTE BETWEEN '07/10/05' AND '07/14/05' UNION SELECT ASPECT_ID, '', 0, 0, AverageCompliance, 0, 0, 0, 0,DTE = CASE WHEN (ADHERENCE_DTE BETWEEN '07/10/05' AND '07/14/05') THEN CONVERT(VarChar, ADHERENCE_DTE, 120) ELSE 'NO ADHERENCE' END FROM COMPLIANCE_WITH_ID WHERE ADHERENCE_DTE BETWEEN '07/10/05' AND '07/14/05' UNION SELECT HSCID, '', 0, 0, 0, TotalScores, AverageScore, TotalPasses, PassPct, DTE = CASE WHEN (EVALUATION_DTE BETWEEN '07/10/05' AND '07/14/05') THEN CONVERT(VarChar, EVALUATION_DTE, 120) ELSE 'NO EVALUATION' END FROM QUALITY_PASS_PERCENT WHERE EVALUATION_DTE BETWEEN '07/10/05' AND '07/14/05')A INNER JOIN (SELECT HSCID, AGENT_NAME FROM QUALITY_PASS_PERCENT)B ON A.ASPECT_ID = B.HSCID GROUP BY A.ASPECT_ID, B.AGENT_NAME ORDER BY A.ASPECT_ID, A.DATE
**ASCII stupid question, get a stupid ANSI !!!** -
farrell - if what i posted doesn't work and you want to take a stab at this, i'm posting the insert statements per the sample rows that salvatore sent so you can use this for testing.....
insert into acw_score values('7/12/05', 4333, 8.4, 201.8)
insert into acw_score values('7/12/05', 0782, 0, 299.3)
insert into acw_score values('7/12/05', 4683, 8.8, 140.4)
insert into acw_score values('7/14/05', 6698, 6.5, 187.2)
insert into acw_score values('7/12/05', 1665, 6, 304.8)
insert into acw_score values('7/13/05', 0710, 4.1, 209)
insert into COMPLIANCE_WITH_ID values(4333, 90, '7/13/05')
insert into COMPLIANCE_WITH_ID values(0644, 95, '7/11/05')
insert into COMPLIANCE_WITH_ID values(0273, 79, '7/11/05')
insert into COMPLIANCE_WITH_ID values(1967, 89, '7/13/05')
insert into COMPLIANCE_WITH_ID values(4633, 78, '7/13/05')
insert into QUALITY_PASS_PERCENT values('Trinh', 6, 96, 5, 83, 0146, '7/13/05')
insert into QUALITY_PASS_PERCENT values('Aviles', 2, 41, 0, 0, 0274, '7/11/05')
insert into QUALITY_PASS_PERCENT values('Coon', 2, 83, 1, 50, 0298, '7/11/05')
insert into QUALITY_PASS_PERCENT values('Breen', 1, 100, 1, 100, 0447, '7/13/05')
insert into QUALITY_PASS_PERCENT values('MacDowell', 1, 100, 1, 100, 0674, '7/13/05')
**ASCII stupid question, get a stupid ANSI !!!** -
Salvatore,
We are at a real loss here until you can tell how these tables relate each other and just exactly how the dates matter.
These are rather taxing queries, (especially all the work sushila has done) and it is difficult to go any further in helping you without VERY specific information on your part. If english is not your first language and you are having difficulty trying to tell us the information you need, please ask an associate to help you. Otherwise, if you are not understanding what we mean when we speak of table relationships, please ask your DBA or another programmer to review this posting so they can understand what we are asking and help you to answer our questions.
sushila's idea of UNIONing derived tables is brilliant! But until we know the answer's to the questions we have asked, even the most beautiful code is useless if we have no idea whether it solves the problem....
This may sound harsh, but we have to know this information to proceed further. I know you are posting under Newbie and we appreciate that. But you must go to someone else you work with so you have a fuller understanding of these table relationships and can explain that information to us...
Thanks.
I wasn't born stupid - I had to study.
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply