August 24, 2005 at 11:26 am
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
August 25, 2005 at 3:23 pm
I am not sure about your needs but COUNT(*) is the only aggregate function in SQL Server that will NOT ignore NULLs so you may need
COUNT (*) and SUM together because your tables allow NULLS on the Dates. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 25, 2005 at 4:07 pm
Thanks for your response. The issue that I'm having is described below:
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...
Let me know what you think,
Thanks
August 25, 2005 at 4:35 pm
Try the link below and scroll down to U through Z for sample code using CUBE and ROLLUP operators. CUBE and ROLLUP are sometimes called super aggregates and I think ROLLUP removes duplicates so you may want to try it with SUM but I could be wrong so try both. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1l4j.asp
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
August 25, 2005 at 4:37 pm
Thanks, I'll check out the link that might be just what I need...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply