Run procedure without cursor or loop

• valeryk2000

  SSCarpal Tunnel

  Points: 4245

  More actions

  November 17, 2018 at 7:12 am

  #416498

  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

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  November 17, 2018 at 8:12 am

  #2013855

  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

• valeryk2000

  SSCarpal Tunnel

  Points: 4245

  More actions

  November 17, 2018 at 9:26 am

  #2013858

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

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  November 17, 2018 at 10:43 am

  #2013860

  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.

• valeryk2000

  SSCarpal Tunnel

  Points: 4245

  More actions

  November 17, 2018 at 11:03 am

  #2013863

  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' 😉

• Jeff Moden

  SSC Guru

  Points: 1003867

  More actions

  December 18, 2018 at 12:19 pm

  #2016723

  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)
  

• valeryk2000

  SSCarpal Tunnel

  Points: 4245

  More actions

  December 18, 2018 at 12:25 pm

  #2016724

  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