May 30, 2014 at 8:03 am
I have a stored procedure (inherited) that I am changing. The procedure calculates Actual and Budget info for 30+ departments. This info is pulled and aggregated from several other tables and one line is inserted into a different table for each department. There was one particular line of code that was duplicated 30+ times (once for each department).
Here is part of the original insert:
INSERT INTO CEOStatisticsV8a
---------------------------
--'Sugery Cases - InHouse'--
---------------------------
SELECT
DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()-1)) AS StatisticsDate,
26 AS Seq,
1 AS Format,
'Surgery Cases - Inhouse' AS Stats,
SUM(InHouseCases) AS Actual,
(SELECT
ROUND((DATEDIFF(d, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0),DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0)))
+1 )* DailyBudget,0)
FROM DATABASE.dbo.vwCEODashboardBudget
WHERE MonthEndDate=DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()-1)+1,0))))
AND StatisticsName='Surgery Cases - Inhouse')
AS Budget,
(SELECT
(SUM(InHouseCases) -
ROUND((DATEDIFF(d, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0),DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0)))
+1 )* DailyBudget,0))
FROM DATABASE.dbo.vwCEODashboardBudget
WHERE MonthEndDate=DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()-1)+1,0))))
AND StatisticsName='Surgery Cases - Inhouse')
AS Variance,
(SELECT
SUM(InHouseCases) AS Actual
FROM DATABASE.dbo.vwSurgicalVolumes
WHERE
ServiceDate BETWEEN DATEADD(yy,-1,(DATEADD(mm, DATEDIFF(mm,0,getdate()-1), 0)))
AND DATEADD(yy,-1,GETDATE()-1)
)
AS PriorYear
FROM DATABASE.dbo.vwSurgicalVolumes
WHERE ServiceDate
BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0)
AND DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0))
UNION ALL
The line after the SELECT statement is replicated for each department. I thought it would be more efficient to determine StatisticsDate just once, so I changed the code to below, I also incorporated changes to how the number of business days are calculated for select departments:
DECLARE @OPBusDays INT;
/**** Get yesterdays date with zero time, just do this once, instead of with each Select ****/
SELECT DATEADD(Day,0,DATEDIFF(Day,0,GETDATE()-1)) AS StatisticsDate;
/**** Code Below calculates the number of outpatient business days for the Month To Date ****/
SELECT @OPBusDays = (SELECT COUNT(*) from [dbo].[CalDates]
WHERE CalDates.IsBusinessDay = 'Y'
AND MMDDYYYY <= StatisticsDate
AND MonthStart < StatisticsDate
AND NextMonth > StatisticsDate);
INSERT INTO CEOStatisticsV8b
---------------------------
--'Sugery Cases - InHouse'--
---------------------------
SELECT
StatisticsDate,
26 AS Seq,
1 AS Format,
'Surgery Cases - Inhouse' AS Stats,
SUM(InHouseCases) AS Actual,
(SELECT
ROUND((DATEDIFF(d, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0),DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0)))
+1 )* DailyBudget,0)
FROM DATABASE.dbo.vwCEODashboardBudget
WHERE MonthEndDate=DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()-1)+1,0))))
AND StatisticsName='Surgery Cases - Inhouse')
AS Budget,
(SELECT
(SUM(InHouseCases) -
ROUND((DATEDIFF(d, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0),DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0)))
+1 )* DailyBudget,0))
FROM DATABASE.dbo.vwCEODashboardBudget
WHERE MonthEndDate=DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()-1)+1,0))))
AND StatisticsName='Surgery Cases - Inhouse')
AS Variance,
(SELECT
SUM(InHouseCases) AS Actual
FROM DATABASE.dbo.vwSurgicalVolumes
WHERE
ServiceDate BETWEEN DATEADD(yy,-1,(DATEADD(mm, DATEDIFF(mm,0,getdate()-1), 0)))
AND DATEADD(yy,-1,GETDATE()-1)
)
AS PriorYear
FROM DATABASE.dbo.vwSurgicalVolumes
WHERE ServiceDate
BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0)
AND DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0))
UNION ALL
Syntactically, all looked good, but I had execution errors:
Msg 207, Level 16, State 1, Procedure CEODailySurgeryStatsTest, Line 13
Invalid column name 'StatisticsDate'.
Msg 207, Level 16, State 1, Procedure CEODailySurgeryStatsTest, Line 14
Invalid column name 'StatisticsDate'.
Msg 207, Level 16, State 1, Procedure CEODailySurgeryStatsTest, Line 15
Invalid column name 'StatisticsDate'.
Msg 207, Level 16, State 1, Procedure CEODailySurgeryStatsTest, Line 24
So I added a local variable and got it to work, but if anyone can explain why it failed, it would be helpful. Thanks.
Here is what works:
/****Procedure changed to calculate outpatient statistics based on business days, which do not include ****/
/****Sundays or Holidays. The number of business days Month-to-date are determined from the CalDates Table ****/
DECLARE @OPBusDays INT,
@StatisticsDate DATETIME;
/**** Get yesterdays date with zero time, just do this once, instead of with each Select ****/
SELECT DATEADD(Day,0,DATEDIFF(Day,0,GETDATE()-1)) AS StatisticsDate;
SET @StatisticsDate = DATEADD(Day,0,DATEDIFF(Day,0,GETDATE()-1));
/**** Code Below calculates the number of outpatient business days for the Month To Date ****/
SELECT @OPBusDays = (SELECT COUNT(*) from [dbo].[CalDates]
WHERE CalDates.IsBusinessDay = 'Y'
AND MMDDYYYY <= @StatisticsDate
AND MonthStart < @StatisticsDate
AND NextMonth > @StatisticsDate);
INSERT INTO CEOStatisticsV8b
---------------------------
--'Sugery Cases - InHouse'--
---------------------------
SELECT
@StatisticsDate AS StatisticsDate,
26 AS Seq,
1 AS Format,
'Surgery Cases - Inhouse' AS Stats,
SUM(InHouseCases) AS Actual,
(SELECT
ROUND((DATEDIFF(d, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0),DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0)))
+1 )* DailyBudget,0)
FROM DATABASE.dbo.vwCEODashboardBudget
WHERE MonthEndDate=DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()-1)+1,0))))
AND StatisticsName='Surgery Cases - Inhouse')
AS Budget,
(SELECT
(SUM(InHouseCases) -
ROUND((DATEDIFF(d, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0),DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0)))
+1 )* DailyBudget,0))
FROM DATABASE.dbo.vwCEODashboardBudget
WHERE MonthEndDate=DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()-1)+1,0))))
AND StatisticsName='Surgery Cases - Inhouse')
AS Variance,
(SELECT
SUM(InHouseCases) AS Actual
FROM DATABASE.dbo.vwSurgicalVolumes
WHERE
ServiceDate BETWEEN DATEADD(yy,-1,(DATEADD(mm, DATEDIFF(mm,0,getdate()-1), 0)))
AND DATEADD(yy,-1,GETDATE()-1)
)
AS PriorYear
FROM DATABASE.dbo.vwSurgicalVolumes
WHERE ServiceDate
BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()-1), 0)
AND DATEADD(s,-1,DATEADD(D, DATEDIFF(D,0,GETDATE()),0))
UNION ALL
May 30, 2014 at 8:14 am
Most likely because there's a significant difference between the meaning of @StatisticsDate and StatisticsDate in your statement.
@StatisticsDate is always a variable. When the parser sees "StatisticsDate" it has to figure out what it is - a column name, an alias, a function or something else. Turns out it's not a variable or a function, so it fails.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply