gphillips_2000
Starting Member
1 Post |
Posted - 2005-08-25 : 17:53:57
|
I have a total of 2 tables that are involved in this query.tblAdmissionSummary: holds unique PatientAccountNumbers. As the name implies, it is a summary of patient data.CREATE TABLE [dbo].[tblAdmissionSummary] ( [PatientAccountNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FacilityID] [int] NOT NULL , [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [AdmitDate] [datetime] NOT NULL , [AdmissionStatusID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PatientStatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [MedicalRecordNumber] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]tblADRActivity: can hold multiple records for the same PatientAccountNumber. Records that exist in both tables give the ability to view the value of the ADRStatusCode for a patient. By joining on PatientAccountNumber, one can see if a patient has accepted, refused or revoked an adr. Those patients who exist in tblAdmissionSummary but not in this table have no adr.CREATE TABLE [dbo].[tblADRActivity] ( [ADRID] [uniqueidentifier] NOT NULL , [FacilityID] [int] NOT NULL , [PatientAccountNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ScanDate] [datetime] NOT NULL , [SignatureDate] [datetime] NULL , [OriginalSignatureDate] [datetime] NULL , [ADRStatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DocPath] [varchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]The requirement is to generate a report showing the number of accepted, refused, and no adrs for a specified current quarter, previous quarter or rolling 1 year.I plan on making three temp tables for this and then unioning them together to get my data.What I have now I think is pretty close to what I need for quarter counts but not quite. Or I may be way off, I'm not sure. Any advice on this is appreciated.The current sproc is below:CREATE procedure dbo.spGetADRSummaryInfo @PeriodType varchar(10), @StartDate varchar(24) AS/***********************************************************************Purpose: Retrieves summary ADR information. Uses HPAS Referral System for HDDatabase: ADR Author Date Action--------- -------- -----------------------------------------Notes: Use fnFacilityChildred 452, CurDate to get facility hierarchy452 as the value of @StartFACID refers to corporate and shows all.************************************************************************/declare @CurrentDate as datetimeset @CurrentDate = getdate()declare @FacilityID intset @FacilityID=452if(@PeriodType='Quarter') declare @QuarterStartDate varchar(24) declare @QuarterEndDate varchar(24) set @QuarterStartDate=@StartDate set @QuarterEndDate=dateadd(m,2,@StartDate )--Declare Table used to store Summary InformationDECLARE @ADRSummary TABLE (FacilityType varchar(25), FacilityID int, FacilityName varchar(75), FacilityAliasID varchar(4), lvl int, ParentID int, oppath varchar(100), aliaspath varchar(100), namepath varchar(255), State char(2), CurrentQuarterTotal int, CurrentQuarterAccepted int, CurrentQuarterRefused int, CurrentQuarterNoADR int, PreviousQuarterTotal int, PreviousQuarterAccepted int, PreviousQuarterRefused int, PreviousQuarterNoADR int, TwoYearTotal int, TwoYearAccepted int, TwoYearRefused int, TwoYearNoADR int)--***************************************************************************************************************************************************************************--Create table for current period type (quarterly)--***************************************************************************************************************************************************************************DECLARE @CurrentPeriodType TABLE( FacilityID int, CurrentQtrAdmitTotal int, CurrentQtrAccepted int, CurrentQtrAcceptedInPt int, CurrentQtrAcceptedOutPt int)--INSERT INTO @CurrentPeriodType SELECT fac.facility_id as FacilityID, --return current quarter admit totals & admit totals for InPt & OutPt COUNT(s.PatientAccountNumber) AS CurrentQtrAdmitTotal, SUM(CASE WHEN s.PatientStatusCode='I' THEN 1 ELSE 0 END) AS CurrentQtrInPtAdmitTotal, SUM(CASE WHEN s.PatientStatusCode='O' THEN 1 ELSE 0 END) AS CurrentQtrOutPtAdmitTotal, ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --return current quarter accepted totals and totals for InPt & OutPt ADRA.CurrentQtrAcceptdTotal As CurrentQtrAcceptdTotal, SUM(CASE WHEN ADRA.ADRStatusCode='A' AND s.PatientStatusCode='I' THEN 1 ELSE 0 END) AS CurrentQtrInPtAcceptd, SUM(CASE WHEN ADRA.ADRStatusCode='A' AND s.PatientStatusCode='O' THEN 1 ELSE 0 END) AS CurrentQtrOutPtAcceptd, ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --return current quarter refused totals and totals for InPt & OutPt ADRA.CurrentQtrRefusedTotal AsCurrentQtrRefusedTotal, SUM(CASE WHEN ADRStatusCode='R' OR ADRStatusCode='V' AND s.PatientStatusCode='I' THEN 1 ELSE 0 END) AS CurrentQtrInPtRefused, SUM(CASE WHEN ADRStatusCode='R' OR ADRStatusCode='V' AND s.PatientStatusCode='O' THEN 1 ELSE 0 END) AS CurrentQtrOutPtRefused, ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --return current quarter refused totals and totals for InPt & OutPt NoADR.CurrentQtrNoADRTotal As CurrentQtrNoADRTotal, NoADR.CurrentQtrInPtNoADR As CurrentQtrInPtNoADR, NoADR.CurrentQtrOutPtNoADR As CurrentQtrOutPtNoADR FROM dbo.tblAdmissionSummary s INNER JOIN FacilityMaster.dbo.fnGetHierarchyWithNames(@FacilityID,@CurrentDate) fac on fac.facility_id = s.FacilityID JOIN (SELECT FacilityID, ADRStatusCode, SUM(CASE WHEN ADRStatusCode='A' THEN 1 ELSE 0 END) AS CurrentQtrAcceptdTotal, SUM(CASE WHEN ADRStatusCode='R' OR ADRStatusCode='V' THEN 1 ELSE 0 END) AS CurrentQtrRefusedTotal FROM dbo.tblADRActivity GROUP BY FacilityID, ADRStatusCode) AS ADRA ON s.FacilityID=ADRA.FacilityID JOIN (SELECT FacilityID, COUNT(PatientAccountNumber) As CurrentQtrNoADRTotal, SUM(CASE WHEN PatientStatusCode='I' THEN 1 ELSE 0 END) AS CurrentQtrInPtNoADR, SUM(CASE WHEN PatientStatusCode='O' THEN 1 ELSE 0 END) AS CurrentQtrOutPtNoADR FROM dbo.tblAdmissionSummary WHERE PatientAccountNumber not in (select PatientAccountNumber from tblADRActivity) GROUP BY FacilityID) AS NoADR ON s.FacilityID=NoADR.FacilityID GROUP BY fac.facility_id, ADRA.CurrentQtrAcceptdTotal, CurrentQtrRefusedTotal, NoADR.CurrentQtrNoADRTotal, NoADR.CurrentQtrInPtNoADR, NoADR.CurrentQtrOutPtNoADRGOIn the second join, I use the column ADRStatusCode in the select and the group. By doing this I am able to output these columns: --return current quarter accepted totals and totals for InPt & OutPt ADRA.CurrentQtrAcceptdTotal As CurrentQtrAcceptdTotal, SUM(CASE WHEN ADRA.ADRStatusCode='A' AND s.PatientStatusCode='I' THEN 1 ELSE 0 END) AS CurrentQtrInPtAcceptd, SUM(CASE WHEN ADRA.ADRStatusCode='A' AND s.PatientStatusCode='O' THEN 1 ELSE 0 END) AS CurrentQtrOutPtAcceptdHowever, Since ADRStatus Code can have 3 values, sub query below will return a result like this:FacilityID CurrentQtrAcceptdTotal ADRStatusCode CurrentQtrRefusedTotal1659 45 V 891659 85 R 2521659 5 F 56JOIN (SELECT FacilityID, ADRStatusCode, SUM(CASE WHEN ADRStatusCode='A' THEN 1 ELSE 0 END) AS CurrentQtrAcceptdTotal, SUM(CASE WHEN ADRStatusCode='R' OR ADRStatusCode='V' THEN 1 ELSE 0 END) AS CurrentQtrRefusedTotal FROM dbo.tblADRActivity GROUP BY FacilityID, ADRStatusCode) AS ADRA ON s.FacilityID=ADRA.FacilityIDBasically, I need to be able to return unique facilities in my statement that includes valid counts from those two Sum(Case) statements that are in this post...Thanks! |
|