Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =============================================
-- Description: Returns yearly/quarterly/Monthly Summary OneConsults by FY or CY.
-- =============================================
CREATE PROCEDURE dbo.[uspRptSumOneConsultsQrtlyMthly]
@beginDate datetime,
@endDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--Conditional Check
--If End date is less than begin date, set end date = begin date
IF @endDate < @beginDate OR @endDate Is Null
BEGIN
SET @endDate = @beginDate;
END
--When doing a [datefield] >= x and [datefield] < y, y needs to be 1 day greater
SET @endDate = DATEADD(dd, 1, @endDate);
--build a table variable
DECLARE @factTable TABLE
(
OneConsultID BIGINT,
VisnCode VARCHAR(10),
DisplayName VARCHAR(100),
RequestServiceCategory VARCHAR(15),
HasProvisionalDiagnosis BIT,
MonthPeriodKey varchar(7),
CalendarYear INT,
CalendarQuarter INT,
FiscalYear INT,
FiscalQuarter INT
)
INSERT INTO @factTable
(
OneConsultID,
VisnCode,
DisplayName,
RequestServiceCategory,
HasProvisionalDiagnosis,
MonthPeriodKey,
CalendarYear,
CalendarQuarter,
FiscalYear,
FiscalQuarter
)
SELECT OneConsultID, VisnCode, CONCAT(Station6A,' ', DisplayName) AS DisplayName, RequestServiceCategory, HasProvisionalDiagnosis,
CONVERT(INT, CONVERT(VARCHAR(6), OC.FileEntryDateTime, 112)) AS MonthPeriodKey,
--CONVERT(varchar(7),OC.FileEntryDateTime,121) AS MonthPeriodKey,
DATENAME(YEAR,OC.FileEntryDateTime) AS CalendarYear,
DATENAME(QUARTER,OC.FileEntryDateTime) AS CalendarQuarter,
CASE WHEN DATENAME(QUARTER,OC.FileEntryDateTime) = 4
THEN DATENAME(YEAR,OC.FileEntryDateTime) +1
ELSE DATENAME(YEAR,OC.FileEntryDateTime)
END AS FiscalYear,
CASE WHEN DATENAME(QUARTER,OC.FileEntryDateTime) = 4
THEN 1
ELSE DATENAME(QUARTER,OC.FileEntryDateTime) +1
END AS FiscalQuarter
FROM dbo.[OneConsults] OC
INNER JOIN dbo.Stations ST ON ST.StationID = OC.StationID
INNER JOIN dbo.Visns on ST.VisnID = Visns.VisnID
INNER JOIN dbo.RequestServices RS ON RS.RequestServiceID = OC.RequestServiceID
WHERE OC.FileEntryDateTime >= @beginDate
AND OC.FileEntryDateTime < @endDate
GROUP BY OneConsultID, VisnCode, Station6A, DisplayName, RequestServiceCategory, HasProvisionalDiagnosis, FileEntryDateTime
ORDER BY 1 ASC
--select from the @factTable table variable with the counts needed
SELECT DISTINCT CalendarYear
, CalendarQuarter
, FiscalYear
, FiscalQuarter
, MonthPeriodKey
, VisnCode
, DisplayName
, RequestServiceCategory
, COUNT(DISTINCT OneConsultID) AS CountOneConsults
, SUM(CASE WHEN HasProvisionalDiagnosis = 1 THEN 1 ELSE 0 END) as HasProvisionalDiagnosis
FROM @factTable
GROUP BY CalendarYear
, CalendarQuarter
, FiscalYear
, FiscalQuarter
, MonthPeriodKey
, VisnCode
, DisplayName
, RequestServiceCategory
END TRY
BEGIN CATCH
PRINT 'There was an error processing the Sum OneConsult by Quarter and Month Report' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC dbo.uspRaiseErrorInfo;
END CATCH
END