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 list of Consult detail data
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [dbo].[uspRptDetOneConsults]
@beginDate datetime,
@endDate datetime,
@visnIds varchar(max),
@stationIds varchar(max),
@statusTypeIds varchar(max)

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);


SELECT [OneConsultID]
,Visns.VisnCode
,ST.DisplayName
,STS.StatusTypeName
,RS.RequestServiceName
,[BatchLogID]
,[ConsultSID]
,[ProvisionalDiagnosisText]
,[ProvisionalDiagnosisCode]
,[HasProvisionalDiagnosis]
,[FileEntryDateTime]
,[DateSynchronized]
FROM [CBOPC_EPRS].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
INNER JOIN dbo.StatusTypes STS ON STS.StatusTypeID = OC.StatusTypeID
WHERE (dbo.Visns.VisnID In(SELECT [Value] FROM dbo.ufnSplit(',', @visnIds)) OR @visnIds IS NULL)
AND (OC.StationID In(SELECT [Value] FROM dbo.ufnSplit(',', @stationIds)) OR @stationIds IS NULL)
AND (STS.StatusTypeID In(SELECT [Value] FROM dbo.ufnSplit(',', @statusTypeIds)) OR @statusTypeIds IS NULL)
AND OC.FileEntryDateTime >= @beginDate
AND OC.FileEntryDateTime < @endDate
ORDER By dbo.Visns.VisnID, OC.StationID;

END TRY
BEGIN CATCH
PRINT 'There was an error while getting OneConsult detail report' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC dbo.uspRaiseErrorInfo;
END CATCH
END
GO