Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

childt table not showing updates in report Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13
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

Post #1338743
Posted Thursday, August 2, 2012 5:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:57 AM
Points: 2,112, Visits: 3,712
Hi, is the report cached or a snapshot?
Does the updated value show correctly if you run that SQL in Management Studio?

Cheers
Post #1339058
Posted Thursday, August 2, 2012 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13
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))

Post #1339468
Posted Thursday, August 2, 2012 2:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
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.
Post #1339532
Posted Thursday, August 2, 2012 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13
Thank you very much for your help I appreciate it
Post #1339534
Posted Thursday, August 2, 2012 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13
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
Post #1339541
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse