Changing to union query in stored procedure

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

General SQL Server Forums

New to SQL Server Programming

Changing to union query in stored procedure

Author

Topic

cwildeman
Starting Member

40 Posts

Posted - 2011-04-14 : 09:04:02

I have the following stored procedure below. I want to change it to a union query. The union query runs successfully minus the where clause. There are parameters in the stored procedure including @HAC. In the where clause of the current query it filters on DischDate (@StartDate and @EndDate) which should work with the stored procedure with the union query. However, @HAC refers to dbo.HACList.HAC and I want it to also refer to dbo.HAC_DVTPE.HospitalAcquired as well when I convert it to a union query. How would I write the where clause in the stored procedure to have @HAC equal to both fields?
Thanks,
Chuck

--------------------------------------------------------------
ALTER PROCEDURE [dbo].[rpt_HAC_HACTypeDetails]

@StartDate datetime,
@EndDate datetime,
@HAC bit

AS

BEGIN

SET NOCOUNT ON;

SET @EndDate = dateadd(d,1, @EndDate);

SELECT dbo.HACList.HAC, dbo.HACType.HacType, dbo.HACType.HacCategory, dbo.vEncounter.DischDate, dbo.tblFacility.FacilityCode AS Hospital,
dbo.vEncounter.PatAcctNum, dbo.HACList.PayerType, dbo.HACList.SpecificOpportunitiesIdentified, dbo.HACList.FinalOutcome, dbo.HACList.Referral1
FROM dbo.HACList INNER JOIN dbo.HACType ON dbo.HACList.HacTypeID = dbo.HACType.HACTypeID INNER JOIN dbo.vEncounter INNER JOIN
dbo.tblFacility ON dbo.vEncounter.Facility = dbo.tblFacility.AiceFacility ON dbo.HACList.AcctNum = dbo.vEncounter.PatAcctNum
where (dbo.vEncounter.DischDate >= @StartDate) AND (dbo.vEncounter.DischDate < @EndDate)
and @HAC = dbo.HACList.HACORDER BY dbo.HACType.HacType, dbo.vEncounter.DischDate

end

------------------------------------------------------------
UNION QUERY

SELECT dbo.HAC_DVTPE.HospitalAcquired AS HAC, 'DVT/PE' AS HacType, 'DVT/PE' AS HacCategory, dbo.vEncounter.DischDate,
dbo.tblFacility.FacilityCode AS Hospital, dbo.vEncounter.PatAcctNum, dbo.tblPayerType.PayerType, dbo.HAC_DVTPE.HospitalCourse AS SpecificOpportunitiesIdentified, dbo.HAC_DVTPE.Therapy AS FinalOutcome, dbo.HACReferral.Referral AS Referral1
FROM dbo.vEncounter INNER JOIN
dbo.tblFacility ON dbo.vEncounter.Facility = dbo.tblFacility.AiceFacility INNER JOIN
dbo.HAC_DVTPE ON dbo.vEncounter.PatAcctNum = dbo.HAC_DVTPE.AcctNum LEFT OUTER JOIN dbo.HACReferral ON dbo.HAC_DVTPE.Referred = dbo.HACReferral.ReferralID LEFT OUTER JOIN dbo.tblPayerType ON dbo.HAC_DVTPE.PayerType = dbo.tblPayerType.PayerTypeID

union all

SELECT dbo.HACList.HAC, dbo.HACType.HacType, dbo.HACType.HacCategory, dbo.vEncounter.DischDate, dbo.tblFacility.FacilityCode AS Hospital,
dbo.vEncounter.PatAcctNum, dbo.HACList.PayerType, dbo.HACList.SpecificOpportunitiesIdentified, dbo.HACList.FinalOutcome, dbo.HACList.Referral1
FROM dbo.HACList INNER JOIN
dbo.HACType ON dbo.HACList.HacTypeID = dbo.HACType.HACTypeID INNER JOIN dbo.vEncounter INNER JOIN dbo.tblFacility ON dbo.vEncounter.Facility = dbo.tblFacility.AiceFacility ON dbo.HACList.AcctNum = dbo.vEncounter.PatAcctNum

Chuck W

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-14 : 09:21:43

You just need to include it both the top and bottom

FROM dbo.vEncounter INNER JOIN
dbo.tblFacility ON dbo.vEncounter.Facility = dbo.tblFacility.AiceFacility INNER JOIN
dbo.HAC_DVTPE ON dbo.vEncounter.PatAcctNum = dbo.HAC_DVTPE.AcctNum LEFT OUTER JOIN dbo.HACReferral ON dbo.HAC_DVTPE.Referred = dbo.HACReferral.ReferralID LEFT OUTER JOIN dbo.tblPayerType ON dbo.HAC_DVTPE.PayerType = dbo.tblPayerType.PayerTypeID
WHERE
@HAC = dbo.HACList.HAC
/* don't put the orde rby here!*/
UNION ALL
SELECT dbo.HACList.HAC, dbo.HACType.HacType, dbo.HACType.HacCategory, dbo.vEncounter.DischDate, dbo.tblFacility.FacilityCode AS Hospital,
dbo.vEncounter.PatAcctNum, dbo.HACList.PayerType, dbo.HACList.SpecificOpportunitiesIdentified, dbo.HACList.FinalOutcome, dbo.HACList.Referral1
FROM dbo.HACList INNER JOIN
dbo.HACType ON dbo.HACList.HacTypeID = dbo.HACType.HACTypeID INNER JOIN dbo.vEncounter INNER JOIN dbo.tblFacility ON dbo.vEncounter.Facility = dbo.tblFacility.AiceFacility ON dbo.HACList.AcctNum = dbo.vEncounter.PatAcctNum
WHERE
@HAC =HAC_DVTPE.HospitalAcquired

/* put the order by here*/

Jim

Everyday I learn something that somebody else already knew