IF object_id('DIXIT_DBR_VAD009','P') IS NOT NULL
DROP PROCEDURE DIXIT_DBR_VAD009
GO
CREATE PROCEDURE [dbo].[DIXIT_DBR_VAD009]
@OrgID int ,
@FROMDATE date ,
@TillDate date ,
@TestCode NVARCHAR(10)
AS
BEGIN
DECLARE @ssql AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @Status NVARCHAR(10) = 'Approve'
DECLARE @YesNo NVARCHAR(1) = 'Y'
DECLARE @ATYPE NVARCHAR(3) = 'GRP'
DECLARE @ParmDefinition nvarchar(500);
SET @TestCode = '%' + @TestCode + '%'
SET @ParmDefinition = N'@TestCode NVARCHAR(10) ,@OrgID int,@FROMDATE date,@TillDate date,@YesNo NVARCHAR(1),@Status NVARCHAR(10), @ATYPE NVARCHAR(3)';
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(Investigationname)
FROM (SELECT DISTINCT
PI.Investigationname
FROM
[PatientInvestigation] PI (NOLOCK)
inner join view_tests671 t
on t.AORGID = PI.GroupID
AND t.OrgID = PI.OrgID
AND t.ATYPE = @ATYPE
WHERE
Cast(PI.CreatedAt as Date ) >= CAST(@FROMDATE as Date)
AND Cast(PI.CreatedAt as Date ) <= CAST(@TillDate as Date)
AND PI.OrgID = @OrgID
AND PI.Status = @Status
AND t.TCODE LIKE @TestCode) AS Investigationnames ;
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'
SELECT
DISTINCT
Patient
, VisitNumber
, VisitDate
, '+@ColumnName+'
FROM
(
SELECT DISTINCT
P.NAME Patient
, PV.VisitNumber
, CAST(PV.VisitDate as Date) VisitDate
, PV.[ExternalVisitID] EmployeeID
, P.[ExternalPatientNumber]
, Location
, P.Age
, P.Sex as Gender
, cm.ClientCode
, cm.ClientName
, IV.Value
, IV.name Investigationname
FROM
[PatientVisit] PV (NOLOCK)
INNER JOIN [LIMS_LIVE].[dbo].[Patient] P (NOLOCK) ON
P.[PatientID] = PV.PatientID
AND P.[OrganizationID] = PV.OrganizationID
INNER join VisitClientMapping VCM With(nolock)
on VCM.VisitID = PV.PatientVisitId
and VCM.OrgID = PV.OrganizationID
and VCM.IsActive is null
inner join ClientMaster Cm With (nolock)
on CM.ClientID = VCM.ClientID
AND CM.OrgID = PV.OrganizationID
inner join [LIMS_LIVE].[dbo].OrganizationAddress OAD (nolock)
ON PV.OrganizationID = OAD.OrganizationID AND PV.OrgAddressID = OAD.AddressID
INNER JOIN [OrderedInvestigations] ORD (NOLOCK) ON
ORD.[VisitID] = PV.PatientVisitID
AND ORD.[OrgID] = PV.OrganizationID
Inner join patientinvestigation PI(nolock) on PI.PatientVisitId = ORD.VisitId
and PI.OrgID = ORD.OrgID and PI.AccessionNumber = ORD.AccessionNumber
Inner join InvestigationValues IV (nolock) on IV.PatientVisitId = PI.PatientVisitId
and IV.OrgID = PI.OrgID
and IV.InvestigationID = PI.InvestigationID
inner join view_tests671 t
on t.AORGID = PI.GroupID
AND t.OrgID = PI.OrgID
AND t.ATYPE = @ATYPE
WHERE
Cast(VisitDate as Date ) >= CAST(@FROMDATE as Date)
AND Cast(VisitDate as Date ) <= CAST(@TillDate as Date)
AND PI.OrgID = @OrgID
AND PI.Status = @Status
AND ISNULL(VCM.[IsActive] , @YesNo) = @YesNo
AND t.TCODE LIKE @TestCode
) Data
Pivot
( min(value) for Investigationname in
(
'+@ColumnName+'
)
) as Pvt' ;
EXEC sp_executesql @DynamicPivotQuery ,@ParmDefinition ,@TestCode=@TestCode,@OrgID=@OrgID ,@FROMDATE=@FROMDATE,@TillDate=@TillDate,@YesNo=@YesNo,@Status=@Status,@ATYPE=@ATYPE;
END
GO
