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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Can't Get Proper Count

Author  Topic 

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 HD
Database:
ADR

Author Date Action
--------- -------- -----------------------------------------


Notes: Use fnFacilityChildred 452, CurDate to get facility hierarchy
452 as the value of @StartFACID refers to corporate and shows all.
************************************************************************/
declare @CurrentDate as datetime
set @CurrentDate = getdate()

declare @FacilityID int
set @FacilityID=452

if(@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 Information
DECLARE @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.CurrentQtrOutPtNoADR
GO


In 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 CurrentQtrOutPtAcceptd

However, Since ADRStatus Code can have 3 values, sub query below will return a result like this:

FacilityID CurrentQtrAcceptdTotal ADRStatusCode CurrentQtrRefusedTotal

1659 45 V 89
1659 85 R 252
1659 5 F 56

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

Basically, 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!
   

- Advertisement -