Report Doesnt show Updates from child table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Report Doesnt show Updates from child table

Author

Topic

desireemm1
Starting Member

2 Posts

Posted - 2012-08-02 : 02:14:17

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 doesn't 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

desireemm1
Starting Member

2 Posts

Posted - 2012-08-02 : 21:00:12

I think I got it 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources