November 17, 2018 at 7:12 am
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 ,
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
November 17, 2018 at 8:12 am
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
November 17, 2018 at 9:26 am
Thanks a lot. Will check it (btw IIf does not exist in SQL Server, I'll use case )
Val
November 17, 2018 at 10:43 am
valeryk2000 - Saturday, November 17, 2018 9:26 AMThanks 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.
November 17, 2018 at 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' 😉
December 18, 2018 at 12:19 pm
valeryk2000 - Saturday, November 17, 2018 11:03 AMThanks. 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
Change is inevitable... Change for the better is not.
December 18, 2018 at 12:25 pm
Jeff Moden - Tuesday, December 18, 2018 12:19 PMvaleryk2000 - Saturday, November 17, 2018 11:03 AMThanks. 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