SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Report Doesnt show Updates from child table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

desireemm1
Starting Member

2 Posts

Posted - 08/02/2012 :  02:14:17  Show Profile  Reply with Quote
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 - 08/02/2012 :  21:00:12  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000