June 5, 2013 at 8:00 am
Hi
I have a report outputting health data with 3 parameters:
1 Schema
2 Practice
3 SpecialtyReferredTo
I want to populate the SpecialtyReferredTo parameter values ONLY when the main stored procedure has data against both the practice selected AND SpecialtyReferredTo, but when I use the main stored procedure as the query on which to populate the SpecialtyReferredTo parameter, I get an error about using forward dependencies. When I add a dataset specifically for the SpecialtyReferredTo parameter (which is basically recycling the 'main' select statement), it takes so long the report becomes unusable.
Can anyone help me get around this please?
The main select statement is as follows:
SELECT
--ISNULL(ServiceTeam,'NOT SPECIFIED') AS ServiceTeam,
--CASE WHEN AOUT.NationalCode=5 Then 'Active'
--ELSE 'Waiting'
--END As Status, GP.Code
GP.Code, Refs.SpecialtyReferredTo, Count(*) as 'Referrals'
FROM ABI_RiO.dbo.vwSGReferrals Refs
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps ON Refs.ClientID = Apps. ClientID AND Refs.ReferralNumber = Apps.ReferralNumber
LEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT ON AOUT.Code=APPs.Outcome
LEFT JOIN ABI_RiO.SchemaSG.ClientIndex CI ON Refs.ClientID = CI.ClientID
LEFT JOIN ABI_RiO.SchemaSG.GenGPPractice GP ON GP.Code = CI.AIMTCCurrentGPPractice
--WHERE Refs.SpecialtyReferredTo = @Specialty
--AND GP.Code = @Practice
where Refs.DischargeReason IS NULL
AND ((Apps.ContactID = ISNULL((SELECT Max(Apps2.ContactID)
FROM ABI_RiO.dbo.vwSGReferrals Refs2
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2 ON Refs2.ClientID = Apps2. ClientID AND Refs2.ReferralNumber = Apps2.ReferralNumber
LEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT ON AOUT.Code=APPs2.Outcome
WHERE Refs2.ClientID = Refs.ClientID
AND Refs2.ReferralNumber = Refs.ReferralNumber
AND NationalCode=5),
(SELECT Max(Apps2.ContactID)
FROM ABI_RiO.dbo.vwSGReferrals Refs2
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2 ON Refs2.ClientID = Apps2. ClientID AND Refs2.ReferralNumber = Apps2.ReferralNumber
WHERE Refs2.ClientID = Refs.ClientID
AND Refs2.ReferralNumber = Refs.ReferralNumber
))) OR Apps.ContactID IS NULL)
GROUP BY GP.Code, Refs.SpecialtyReferredTo
ORDER BY GP.Code
Many thanks.
Tim
June 6, 2013 at 7:35 am
Parameters are for filtering main report data not the other way round. Although its a reverse functionality you are trying to implement, try this for the parameter SpecialityRefTo drop down list:
SELECT
Distinct Refs.SpecialtyReferredTo
FROM ABI_RiO.dbo.vwSGReferrals Refs
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps ON Refs.ClientID = Apps. ClientID AND Refs.ReferralNumber = Apps.ReferralNumber
LEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT ON AOUT.Code=APPs.Outcome
LEFT JOIN ABI_RiO.SchemaSG.ClientIndex CI ON Refs.ClientID = CI.ClientID
LEFT JOIN ABI_RiO.SchemaSG.GenGPPractice GP ON GP.Code = CI.AIMTCCurrentGPPractice
where Refs.DischargeReason IS NULL
AND ((Apps.ContactID = ISNULL((SELECT Max(Apps2.ContactID)
FROM ABI_RiO.dbo.vwSGReferrals Refs2
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2 ON Refs2.ClientID = Apps2. ClientID AND Refs2.ReferralNumber = Apps2.ReferralNumber
LEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT ON AOUT.Code=APPs2.Outcome
WHERE Refs2.ClientID = Refs.ClientID
AND Refs2.ReferralNumber = Refs.ReferralNumber
AND NationalCode=5),
(SELECT Max(Apps2.ContactID)
FROM ABI_RiO.dbo.vwSGReferrals Refs2
LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2 ON Refs2.ClientID = Apps2. ClientID AND Refs2.ReferralNumber = Apps2.ReferralNumber
WHERE Refs2.ClientID = Refs.ClientID
AND Refs2.ReferralNumber = Refs.ReferralNumber
))) OR Apps.ContactID IS NULL)
GROUP BY GP.Code, Refs.SpecialtyReferredTo
ORDER BY GP.Code
June 6, 2013 at 9:10 am
Hi Gini
Thanks for that, I think it will do the job. I just need to get our server to speed up as it took 2.5 mins to execute. I think turning into a table with a daily update may address that though.
Cheers.
Tim.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply