Run procedure without cursor or loop

  • Stored procedure sp_calc_MedRec calculates monthly counts for different measures. The code below shows just one measure, calculations and insert into

    Create

    PROCEDURE [dbo].[sp_calc_MedRec]

    @StartDate date,

    @EndDate date

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @Num float, @den float

    )

    SELECT @Num = count(AdmitReviewed)

    FROM [EPIC_data].[dbo].[vw_MedRec]

    WHERE AdmitReviewed = 1 and Hospital like 'BMH'

    and VisitEndDateTime >= @StartDate AND VisitEndDateTime <= @EndDate

    and (not accommodationtype like 'Newborn'

    and not accommodationtype like 'NICU%')

    and (not visittypecode like 'Newborn'

    and not visittypecode like '%Psyc%'

    and not visittypecode like 'HSPC IP'

    and not visittypecode like 'Inpatient Re'

    and not visittypecode like 'HSPC IP'

    and not visittypecode like 'Extended Out'

    )

    SELECT @den = count(AdmitReviewed)

    FROM [EPIC_data].[dbo].[vw_MedRec] WHERE Hospital like 'BMH'

    and VisitEndDateTime >= @StartDate AND VisitEndDateTime <= @EndDate

    and (not accommodationtype like 'Newborn'

    and not accommodationtype like 'NICU%')

    and (not visittypecode like 'Newborn'

    and not visittypecode like '%Psyc%'

    and not visittypecode like 'HSPC IP'

    and not visittypecode like 'Inpatient Re'

    and not visittypecode like 'HSPC IP'

    and not visittypecode like 'Extended Out')

    INSERT INTO tblMeasureCount

    Select 135, -- measureId

    'Medication Reconciliation Admission' , ---- measure name 

    'NMH',   ---- hospital name 

    @StartDate,

    @EndDate,

    @Num ,

    @den

    END

     
    The calculations has to be inserted for several months that are listed in another table (see attached Excel file). One choice is to run the procedure repeatedly for every month (@StartDate= FirstDateOfMonth, @EndDate=LastDayOfMont) using CURSOR - too long
    But there should be better solution. Am I right?
    Val

  • CREATE PROCEDURE [dbo].[sp_calc_MedRec] AS
    BEGIN
        SET NOCOUNT ON;
        INSERT INTO tblMeasureCount
        SELECT 135, -- measureId
               'Medication Reconciliation Admission', ---- measure name
               'NMH', ---- hospital name
               d.FirstDayOfMonth,
               d.LastDayOfMonth,
               SUM(IIF(m.AdmitReviewed = 1, 1, 0)) AS Num,
               COUNT(m.AdmitReviewed) AS Den
          FROM [EPIC_data].[dbo].[vw_MedRec] m
         INNER JOIN myDatesTable d
                 ON m.VisitEndDateTime >= d.FirstDayOfMonth
                AND m.VisitEndDateTime < DATEADD(dd, 1, d.LastDayOfMonth)
         WHERE m.Hospital LIKE 'BMH'
           AND m.accommodationtype NOT LIKE 'Newborn'
           AND m.accommodationtype NOT LIKE 'NICU%'
           AND m.visittypecode     NOT LIKE 'Newborn'
           AND m.visittypecode     NOT LIKE '%Psyc%'
           AND m.visittypecode     NOT LIKE 'HSPC IP'
           AND m.visittypecode     NOT LIKE 'Inpatient Re'
           AND m.visittypecode     NOT LIKE 'HSPC IP'
           AND m.visittypecode     NOT LIKE 'Extended Out'
         GROUP BY d.FirstDayOfMonth, d.LastDayOfMonth;
    END
    GO

  • Thanks a lot. Will check it (btw IIf does not exist in SQL Server, I'll use case )
    Val

  • valeryk2000 - Saturday, November 17, 2018 9:26 AM

    Thanks a lot. Will check it (btw IIf does not exist in SQL Server, I'll use case )
    Val

    Yes, you're right. It was introduced in SQL Server 2012 and you are using 2008.
    Case will do the same it just takes slightly more typing.

  • Thanks. Good to know. We are upgrading to SQL Server 2016. I thik IIf is not the only useful addition (IIf is used in JET-SQL, so not a big news for me)
    AND  asking this simple questions  reveals that I do not belong to the 'Hall of Fame' 😉

  • valeryk2000 - Saturday, November 17, 2018 11:03 AM

    Thanks. Good to know. We are upgrading to SQL Server 2016. I thik IIf is not the only useful addition (IIf is used in JET-SQL, so not a big news for me)
    AND  asking this simple questions  reveals that I do not belong to the 'Hall of Fame' 😉

    Don't both converting existing code, though.  If you look at the execution plans, you'll find that IIF resolves to a CASE statement behind the scenes.  The ONLY advantage of IIF is less typing and (sometimes) an easier read of the code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, December 18, 2018 12:19 PM

    valeryk2000 - Saturday, November 17, 2018 11:03 AM

    Thanks. Good to know. We are upgrading to SQL Server 2016. I thik IIf is not the only useful addition (IIf is used in JET-SQL, so not a big news for me)
    AND  asking this simple questions  reveals that I do not belong to the 'Hall of Fame' 😉

    Don't both converting existing code, though.  If you look at the execution plans, you'll find that IIF resolves to a CASE statement behind the scenes.  The ONLY advantage of IIF is less typing and (sometimes) an easier read of the code.

    Thanks Jeff

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply