August 1, 2012 at 12:22 pm
:cool:I have a problem I have three tables all joined together One is the people.tbl the other the tanfactivity.tbl and the counselors.tbl I am trying to show an active participants report with a date range which is located in the tanfactivity.tbl. I need for users to be able to run the report by the beginning date and end date, showing the updated information. The problem is when a participant changes counselors the updated counselor doesnt show on the report only the last counselor.
here is my select statement can anyone tell me what I'm doing wrong??
SELECT DISTINCT
People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea,
People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.ScairHoursOnly, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita,
TanfActivity_tbl.EventDate, Counselors_tbl.CounselorsName, Counselors_tbl.[Parent ID] AS Expr1, Counselors_tbl.Counselor,
Counselors_tbl.CounselorID
FROM People_tbl INNER JOIN
TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID] LEFT OUTER JOIN
Counselors_tbl ON People_tbl.[Parent ID] = Counselors_tbl.[Parent ID]
WHERE (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate)
ORDER BY People_tbl.ServiceArea, People_tbl.LastName
August 2, 2012 at 5:03 am
Hi, is the report cached or a snapshot?
Does the updated value show correctly if you run that SQL in Management Studio?
Cheers
August 2, 2012 at 12:58 pm
Hello the report is cached. And when I run the Select Statement in Management studio This is the error I get. Msg 8120, Level 16, State 1, Line 1
Column 'People_tbl.ScairHoursOnly' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT DISTINCT
People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea,
People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.ScairHoursOnly, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita,
TanfActivity_tbl.EventDate, Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID
FROM People_tbl INNER JOIN
TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID] LEFT OUTER JOIN
Counselors_tbl ON People_tbl.[Parent ID] = Counselors_tbl.[Parent ID]
GROUP BY People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea,
People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, TanfActivity_tbl.EventDate,
Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID
HAVING (TanfActivity_tbl.EventDate BETWEEN CONVERT(DATETIME, '2012-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-07-31 00:00:00', 102))
August 2, 2012 at 2:54 pm
First, it is a bad idea to hardcode your dates into your query. I will assume for now that it is just for testing purposes
Second using GROUP BY with DISTINCT is unnecessary, use one or the other. Generally I use DISTINCT when I do not need to aggregate (SUM, AVG, etc.) anything, and GROUP BY if I do need to aggregate some values. The error you are getting is because 'People_tbl.ScairHoursOnly' is not in your GROUP BY section. Either remove your GROUP BY section entirely or add 'People_tbl.ScairHoursOnly' to your GROUP BY section and it should work.
August 2, 2012 at 2:57 pm
Thank you very much for your help I appreciate it
August 2, 2012 at 3:18 pm
Here is what I did seems to be working now
SELECT People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea,
People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, TanfActivity_tbl.EventDate,
Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID, Counselors_tbl.EffectiveDate
FROM People_tbl INNER JOIN
TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID] LEFT OUTER JOIN
Counselors_tbl ON People_tbl.[Parent ID] = Counselors_tbl.[Parent ID]
GROUP BY People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea,
People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, TanfActivity_tbl.EventDate,
Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID, Counselors_tbl.EffectiveDate
HAVING (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate)
ORDER BY People_tbl.LastName, Counselors_tbl.EffectiveDate DESC
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply