Count & Sum Question

  • 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

     

  • 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

  • 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

     

  •  

     

     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

  • 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