Hrcm View Schema

View Name:AnnotationView

Description:

This view Describes Document Annotations.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[AnnotationView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[AnnotationView]
AS
select AccountNumber,DocumentId,MedicalConcept,MedicalConceptType,Finding,History,StartId,EndId,'Annotation' AnnotationType from NLP_MedicalConcept
union 
select AccountNumber,DocumentId,SectionHeader,'Heading',NULL,NULL,StartId,EndId,'SectionHeader' AnnotationType from NLP_SectionHeader
;
GO

======================================================

View Name:CDIWorkQueueView_Audit

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_Audit]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_Audit]
AS
select distinct Top 100 v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(dbo.fnFormatDate(v.DischargeDate,'MM/DD/YYYY'),'') DischargeDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,'' ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
'Missing CDS' CDS,
case when isnull(hd.DocumentCompletionStatusID,'') in (1,2)
            then 'Final Discharge Summary on '+dbo.fnFormatDate(hd.EditDateTime,'MM/DD/YYYY')
            else 'Final Discharge Summary not updated'   end FinalD,
case when isnull(hd.DocumentCompletionStatusID,'') in (1,2) then 0 else 1 end DocState,'Missing Auditor' Auditor,'Missing SuperAuditor' SuperAuditor
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
inner join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join HIM_Insurance hin on v.VisitID=hin.VisitID and hin.SetID=1
left join MST_Insurance_PrimaryDRGGrouper pdg on hin.InsurancePlanID=pdg.InsurancePlanID
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
left join (select distinct AccountNumber,Max(DocumentCompletionStatusID) DocumentCompletionStatusID,max(EditDateTime) EditDateTime from HIM_Document where isnull(DocumentCompletionStatusID,'')
in (1,2) and DocumentTypeID=6 group by AccountNumber) hd on hd.AccountNumber=v.accountnumber
/*
--Need to handle following where clause
where V.DischargeDate is not null and v.cds is not null
and v.dischargedate>='11/25/2018'
and ( (@queueType='unassigned' and v.AuditProcessStatus='unassigned') or
      (@queueType='nonaudit' and (V.Auditor=@username or @usertype='superauditor' or @username='All' or @username='mohamed.ibrahim') and v.AuditProcessStatus='nonaudited') or 
      (@queueType='Audited' and (V.Auditor=@username or V.cds=@username or @usertype='superauditor' or @username='All' or @username='mohamed.ibrahim') and v.AuditProcessStatus='audited' ) or
      (@queueType='postAudited'   and (V.Auditor=@username or @usertype='superauditor' or @username='All' or @username='mohamed.ibrahim') and v.AuditProcessStatus='postaudited' and v.DischargeDate>= dateadd(Day,-15,getdate())) or 
       (@queueType='')
)
order by dischargedate desc
*/
;
GO

======================================================

View Name:CDIWorkQueueView_ConcurrentAudit

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_ConcurrentAudit]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_ConcurrentAudit]
AS
select distinct top 100 v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(dbo.fnFormatDate(v.DischargeDate,'MM/DD/YYYY'),'') DischargeDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,'' ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
'Missing CDS' CDS,'Missing AttDocName' AttPhysician,
case when isnull(hd.DocumentCompletionStatusID,'') in (1,2) 
                              then 'Final Discharge Summary on '+dbo.fnFormatDate(hd.EditDateTime,'MM/DD/YYYY')
                              else 'Final Discharge Summary not updated'  end FinalD,
case when isnull(hd.DocumentCompletionStatusID,'') in (1,2) then 0 else 1 end DocStatep
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
inner join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
left join HIM_Document hd on hd.AccountNumber=v.AccountNumber and hd.DocumentTypeID=6
/*
Need to handle following where clause
where cad.concurrentauditor=@Username and sa.rank1=1
and 
  ((sa.sender=cad.concurrentauditor and @type='concareviewed' and v.DischargeDate is null)
  or (sa.sender!=cad.concurrentauditor and @type='cdsreviewed' and v.DischargeDate is null)
  or (v.DischargeDate is not null and @type='concadischarge' and v.DischargeDate is null)
  )
and v.AdmitDate>='01/25/2018'
*/
;
GO

======================================================

View Name:CDIWorkQueueView_Discharged

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_Discharged]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_Discharged]
AS
select distinct top 100 v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(dbo.fnFormatDate(v.DischargeDate,'MM/DD/YYYY'),'') DischargeDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,'' ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
'Missing CDS' CDS,'Missing AttDocName' AttPhysician,isnull(CONVERT(VARCHAR(25),'Missing CDSLastReview',101),'NA') CDSLastReview,
case when isnull(hd.DocumentCompletionStatusID,'') in (1,2) 
                              then 'Final Discharge Summary on '+dbo.fnFormatDate(hd.EditDateTime,'MM/DD/YYYY')
                              else 'Final Discharge Summary not updated'  end FinalD,
case when isnull(hd.DocumentCompletionStatusID,'') in (1,2) then 0 else 1 end DocStatep
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
left join HIM_Insurance hin on v.VisitID=hin.VisitID and hin.SetID=1
left join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join MST_Insurance_PrimaryDRGGrouper pdg on hin.InsurancePlanID=pdg.InsurancePlanID
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
left join HIM_Document hd on hd.AccountNumber=v.AccountNumber and hd.DocumentTypeID=6
/*
Need to handle following where clause
where (V.CDS=@Username or  (@Username='All' and v.cds is not null))
and V.DischargeDate is not null and isnull(v.AccountProcessStatus,'')!='Complete'
order by DocStatep,dischargedate desc
*/
;
GO

======================================================

View Name:CDIWorkQueueView_Followups

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_Followups]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_Followups]
AS
select distinct Top 100 v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,'' ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
'Missing CDS' CDS,'Missing AttPhysician' AttPhysician,'Missing CDSLastReview' CDSLastReview
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
inner join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join HIM_Insurance hin on v.VisitID=hin.VisitID and hin.SetID=1
left join MST_Insurance_PrimaryDRGGrouper pdg on hin.InsurancePlanID=pdg.InsurancePlanID
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
/*
--Need to handle following where clause
where (V.CDS=@Username or (@Username='All' and v.cds is not null)) and v.DischargeDate is null
order by priorityorder
*/
;
GO

======================================================

View Name:CDIWorkQueueView_InProcess

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_InProcess]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_InProcess]
AS
select distinct Top 100 v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,'' ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
'Missing CDS' CDS,'Missing AttDocName' AttPhysician
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
inner join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join HIM_Insurance hin on v.VisitID=hin.VisitID and hin.SetID=1
left join MST_Insurance_PrimaryDRGGrouper pdg on hin.InsurancePlanID=pdg.InsurancePlanID--Not Used
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
/*
Need to handle following where clause
where ((@Username='All' and v.cds is not null) or V.CDS=@Username )
and V.DischargeDate is null and afp1.AccountNo is null and isnull(v.AccountProcessStatus,'')!='Complete'
order by priorityorder
*/
;
GO

======================================================

View Name:CDIWorkQueueView_New

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_New]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_New]
AS
select distinct top 100  v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(dbo.fnFormatDate(v.DischargeDate,'MM/DD/YYYY'),'') DischargeDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,'' ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
'Missing CDS' CDS,'Missing AttDocName' AttPhysician
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
left join HIM_Insurance hin on v.VisitID=hin.VisitID and hin.SetID=1
left join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join MST_Insurance_PrimaryDRGGrouper pdg on hin.InsurancePlanID=pdg.InsurancePlanID
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
/*
--Need to Implement following where clause
where (V.CDS=@Username or (@Username='All' and v.cds is not null)) 
and (vl.accountno is null and ar.accountno is null) -- and v.DischargeDate is null
and isnull(v.AccountProcessStatus,'')!='complete'
*/
;
GO

======================================================

View Name:CDIWorkQueueView_PQueries

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_PQueries]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_PQueries]
AS
select distinct Top 100 v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,'' ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
'Missing CDS' CDS,'Missing AttPhysician' AttPhysician
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
left join HIM_Insurance hin on v.VisitID=hin.VisitID and hin.SetID=1
left join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join MST_Insurance_PrimaryDRGGrouper pdg on hin.InsurancePlanID=pdg.InsurancePlanID
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
/*
--Need to handle following where clause
where dc.QueryResponse=0 and dc.Effectivedate>='09/01/2018' 
and dc.username!='KPAI' and (V.CDS=@Username or (@Username='All' and v.cds is not null)) 
order by priorityorder,Querydate
*/
;
GO

======================================================

View Name:CDIWorkQueueView_UA

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[CDIWorkQueueView_UA]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CDIWorkQueueView_UA]
AS
select distinct v.AccountNumber,rtrim(p.FirstName)+space(1)+rtrim(p.LastName) as Patientname,
dbo.fnFormatDate(v.AdmitDate,'MM/DD/YYYY') AdmitDate,
isnull(dbo.fnFormatDate(v.DischargeDate,'MM/DD/YYYY'),'') DischargeDate,
isnull(nd.MSDRG,'') MSDRG,isnull(nd.APRDRG,'') APRDRG,
case when nd.MSDRGWeight is null then '' else cast(cast(round(nd.MSDRGWeight,2) as numeric(36,2)) as varchar) end MSDRGWeight,
case when nd.APRDRGWeight is null then '' else cast(cast(round(nd.APRDRGWeight,2) as numeric(36,2)) as varchar) end APRDRGWeight,
isnull(nd.MSDRGGLOS,'') MSDRGGLOS,isnull(nd.APRDRGGLOS,'') APRDRGGLOS,
cast(round(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) < 1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end,2) as numeric(36,1)) as LOS,
case when nd.MSDRGGLOS is null then '' else cast(cast(round(nd.MSDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_MSDRG,
case when nd.APRDRGGLOS is null then '' else cast(cast(round(nd.APRDRGGLOS-(case when cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float)<1 then 1 
else cast(datediff(minute,v.admitdate,isnull(v.dischargedate,getdate())) as float)/cast((60*24) as float) end ),2) as numeric(36,1)) as varchar) end OUStay_APRDRG,
isnull(nd.soi,'') SOI,isnull(nd.rom,'') ROM,
TotalDocs = (select count(*) from HIM_Document (nolock) where AccountNumber=v.AccountNumber),
isnull(nd.MSDRGDesc,'') MSDRGDesc,
isnull(nd.APRDRGDesc,'') APRDRGDesc,
isnull(V.patientClass,'') as PatientType,
isnull(hsc.ServiceName,'') ServiceType,
Isnull(fc.FinancialClassAliasName,'') FinClass,
case when v.FinancialClass in ('17','50','0017','0050') then 'APR' else 'DRG' end DRGType,
replace(isnull(v.ReasonForVisit,''),'^','') VisitReason,
v.LocationRoom,case when fd.AccountNumber is null then 'No' else 'Yes' end Coded_In_Facility,
'Missing CDS' CDS,'Missing AttDocName' AttPhysician,'Missing PatientCategory' PatientCTG
from HIM_Visit v
inner join HIM_Patient p on v.PatientID=p.PatientId
inner join HospitalServiceView hsv on v.AccountNumber=hsv.AccountNumber
left join HIM_Insurance hin on v.VisitID=hin.VisitID and hin.SetID=1
left join MST_FinancialClass fc on v.FinancialClass=fc.FinancialClassID
left join MST_Insurance_PrimaryDRGGrouper pdg on hin.InsurancePlanID=pdg.InsurancePlanID--Not Used
left join NLP_DRG nd on v.AccountNumber=nd.AccountNumber
left join MST_HospitalService hsc on v.HospitalService=hsc.HospitalServiceCode
left join FTP_DXCode fd on v.AccountNumber=fd.AccountNumber
;
GO

======================================================

View Name:DayLabView

Description:

This view Describes day wise lab results for each and every AccountNumber.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[DayLabView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DayLabView]
AS
SELECT AccountNumber,DocumentId,TestName,Result,NumericResult,Unit,MinRange,MaxRange,LabTestDate,LOINCCode,SectionHeader,HospitalDay,LabDay,Deviation,UniqueId,TestMnemonic,SourceType,
CDI,LabId
from (
select DENSE_RANK() OVER (PARTITION BY AccountNumber,TestName ORDER BY TestName,convert(datetime,convert(varchar(10),Labtestdate,101))) as LabDay,
RANK() OVER (PARTITION BY AccountNumber,TestName,convert(datetime,convert(varchar(10),Labtestdate,101)) ORDER BY TestName,Status desc,convert(datetime,convert(varchar(10),Labtestdate,101)),Deviation desc) as rank1,
AccountNumber,DocumentId,TestName,Result,NumericResult,Unit,MinRange,MaxRange,LabTestDate,LOINCCode,SectionHeader,HospitalDay,Deviation,UniqueId,TestMnemonic,SourceType,CDI,LabId
from(
select distinct AccountNumber,DocumentId,TestName,Result,NumericResult,Unit,MinRange,MaxRange,LabTestDate,LOINCCode,SectionHeader,HospitalDay,UniqueId,TestMnemonic,SourceType,CDI,
Status= case when NumericResult IS NULL then 0 else case when NumericResult not between MinRange and MaxRange then 1   when NumericResult between MinRange and MaxRange then 0 end END,
Deviation,LabId
--Deviation=case when NumericResult is null then 0 else case when NumericResult <= MinRange then abs(NumericResult- MinRange) when NumericResult >= MaxRange then NumericResult - MaxRange else 0 end End 
from LabTestView where RESULT is not null

) a group by AccountNumber,DocumentId,TestName,Result,NumericResult,Unit,MinRange,MaxRange,LabTestDate,LOINCCode,SectionHeader,HospitalDay,Status,Deviation,UniqueId,TestMnemonic,SourceType,CDI,LabId
) b where LabDay<4 and rank1=1
;
GO

======================================================

View Name:DayVitalView

Description:

This view Describes day wise vital signs for each and every AccountNumber.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[DayVitalView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DayVitalView]
AS
select AccountNumber,GETDATE() DTime,VitalSign,
case when NumericResult>MaxRange then 'High' when NumericResult between MinRange and MaxRange then 'Normal' else 'ND' end DeviationType,NumericResult,
NULL NumericResult2,SourceType,UniqueId,'Max' VSValType,MinRange,MaxRange,VitalSignID,VSignDay
from(
select AccountNumber,VitalSign,NumericResult,SourceType,UniqueId,MinRange,MaxRange,VitalSignID,vitalSignDate,VSignDay,
RANK() Over(Partition By AccountNumber,VitalSign,VSignDay order by NumericResult desc,vitalSignDate desc) Rank
from(
select AccountNumber,VitalSign,NumericResult,SourceType,UniqueId,MinRange,MaxRange,VitalSignID,vitalSignDate,
DENSE_RANK() Over (Partition By AccountNumber,VitalSign order by VitalSign,convert(datetime,convert(varchar(10),VitalSignDate,101))) as VSignDay
from VitalSignView where VitalSign in ('Temperature','Pulse','Respiratory')
)a where a.VSignDay<4
)b where b.Rank=1
union
select AccountNumber,GETDATE() DTime,VitalSign,
case when NumericResult<MinRange then 'Low' when NumericResult between MinRange and MaxRange then 'Normal' else 'ND' end DeviationType,NumericResult,
NULL NumericResult2,SourceType,UniqueId,'Min' VSValType,MinRange,MaxRange,VitalSignID,VSignDay
from(
select AccountNumber,VitalSign,NumericResult,SourceType,UniqueId,MinRange,MaxRange,VitalSignID,vitalSignDate,VSignDay,
RANK() Over(Partition By AccountNumber,VitalSign,VSignDay order by NumericResult,vitalSignDate desc) Rank
from(
select AccountNumber,VitalSign,NumericResult,SourceType,UniqueId,MinRange,MaxRange,VitalSignID,vitalSignDate,
DENSE_RANK() Over (Partition By AccountNumber,VitalSign order by VitalSign,convert(datetime,convert(varchar(10),VitalSignDate,101))) as VSignDay
from VitalSignView where VitalSign in ('Temperature','Pulse','Respiratory')
)a where a.VSignDay<4
)b where b.Rank=1
union
select AccountNumber,GETDATE() Dtime,VitalSign,'ND',NumericResult,NumericResult2,SourceType,UniqueId,'NA',MinRange,MaxRange,VitalSignID,VSignDay
from(
select AccountNumber, VitalSign,NumericResult,NumericResult2,sdif+ddif totaldev,MaxRange,MinRange,PanicMaxRange,PanicMinRange,SourceType,UniqueId,VitalSignID,VitalSignDate,VSignDay,
RANK() Over(Partition By AccountNumber,VSignDay order by sdif+ddif desc, vitalSignDate desc) Rank
from
(
select AccountNumber, VitalSign,NumericResult,NumericResult2,
case when NumericResult<MinRange then MinRange-NumericResult when NumericResult>MaxRange then NumericResult-MaxRange else 0 end sdif,
case when NumericResult2<PanicMinRange then PanicMinRange-NumericResult2 when NumericResult2>PanicMaxRange then NumericResult2-PanicMaxRange else 0 end ddif,
MaxRange,MinRange,PanicMaxRange,PanicMinRange,SourceType,UniqueId,VitalSignID,VitalSignDate,
DENSE_RANK() Over (Partition By AccountNumber order by convert(datetime,convert(varchar(10),VitalSignDate,101))) as VSignDay
from VitalSignView where NumericResult2 is not null and VitalSign='bp'
)a where a.VSignDay<4
)b where b.Rank=1
/*
union
select AccountNumber,DTime,VitalSign,'ND',NumericResult,NumericResult2,SourceType,UniqueId,'NA',MinRange,MaxRange,VitalSignID,VSignDay
from(
select AccountNumber,GETDATE() DTime,VitalSign,NumericResult,NULL NumericResult2,SourceType,UniqueId,MinRange,MaxRange,VitalSignID,VitalSignDate,VSignDay,
RANK() Over(Partition By AccountNumber,VitalSign,VSignDay order by vitalSignDate desc) Rank
from (
select AccountNumber,GETDATE() DTime,VitalSign,NumericResult,NULL NumericResult2,SourceType,UniqueId,MinRange,MaxRange,VitalSignID,VitalSignDate,
DENSE_RANK() Over (Partition By AccountNumber,VitalSign order by VitalSign,convert(datetime,convert(varchar(10),VitalSignDate,101))) as VSignDay
from VitalSignView where VitalSign in('Height','Weight')
)a where a.VSignDay<4
)b where b.Rank=1
*/
;
GO

======================================================

View Name:DocumentView

Description:

This view describes complete information about patient documents and its related data.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[DocumentView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DocumentView]
AS
select distinct hd.MRN,hd.AccountNumber,hd.DocumentID,hd.DocumentTypeID,hd.PhysicianID,
dbo.convertUtcToTimeZone('IST',hd.ActivityDateTime) ActivityDateTime,
dbo.convertUtcToTimeZone('IST',hd.OriginationDateTime) OriginationDateTime,
dbo.convertUtcToTimeZone('IST',hd.TranscriptionDateTime) TranscriptionDateTime,
dbo.convertUtcToTimeZone('IST',hd.EditDateTime) EditDateTime,
hd.UniqueDocumentNumber,hd.ParentDocumentNumber,hd.UniqueDocumentFileName,
hd.DocumentCompletionStatusID,hd.DocumentRunningStatusID,
dbo.convertUtcToTimeZone('IST',hd.InitialRunDate) InitialRunDate,
dbo.convertUtcToTimeZone('IST',hd.LastRunDate) LastRunDate,
hd.InsertedDate,hd.ModifiedDate,hd.HospitalDay,hd.Formatted,hdd.Document,
mdt.DocumentName,mdt.DocumentAliasName,mdt.DocumentMnemonic,idt.InternalDocumentAliasName,idt.InternalDocumentTypeID,
isnull(dr.ReviewStatus,0) DocumentReviewStatus,dbo.fngetPhysician(hd.PhysicianID) PhysicianName,idt.InternalDocumentName
from dbo.HIM_DocumentData hdd
inner join dbo.HIM_Document hd on hdd.DocumentID=hd.DocumentID
inner join dbo.MST_DocumentType mdt on mdt.DocumentTypeID=hd.DocumentTypeID
inner join dbo.MST_InternalDocumentType idt on idt.InternalDocumentTypeID=mdt.InternalDocumentTypeID
left join dbo.USER_DocumentReview dr on hd.AccountNumber=dr.AccountNumber and hd.DocumentID=dr.DocumentId and dr.ReviewStatus=1

;
GO

======================================================

View Name:DQFilterColumnView

Description:

This view describes dynamic query filtered columns to Dynamic Query View.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[DQFilterColumnView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DQFilterColumnView]
AS
select distinct dqf.DqViewId,dqc.DqColumnId,dqc.DBName,dqc.TableName,dqc.ColumnName,dqf.Clause,dqf.Mandatory,dqf.Value1,dqf.Value2,dqc.AliasName,dqc.ShortName,dqc.ColumnType
from MST_DqColumn dqc
inner join User_DqFilteredColumn dqf on dqc.DqColumnId=dqf.DqColumnId
;
GO

======================================================

View Name:DQOrderColumnView

Description:

This view describes dynamic query ordered columns to Dynamic Query View.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[DQOrderColumnView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DQOrderColumnView]
AS
select distinct dqo.DqViewId,dqc.DqColumnId,dqc.DBName,dqc.TableName,dqc.ColumnName,dqo.Mandatory,dqo.SelectedOrder,dqc.AliasName,dqc.ShortName,dqo.Clause
from MST_DqColumn dqc
inner join User_DqOrderedColumn dqo on dqc.DqColumnId=dqo.DqColumnId
;
GO

======================================================

View Name:DQSelectColumnView

Description:

This view describes dynamic query selected columns to Dynamic Query View.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[DQSelectColumnView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DQSelectColumnView]
AS
select distinct dqs.DqViewId,dqc.DqColumnId,dqc.DBName,dqc.TableName,dqc.ColumnName,dqs.Mandatory,dqs.SelectedOrder,dqc.ColumnType,dqc.AliasName,dqc.ShortName,dqc.AliasColumnName
from MST_DqColumn dqc
inner join User_DqSelectedColumn dqs on dqc.DqColumnId=dqs.DqColumnId
;
GO

======================================================

View Name:DRGView

Description:

This view describes complete information about Reimbursement.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[DRGView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DRGView]
AS
select hd.AccountNumber,MSDRG,MSDRGDesc,MSDRGWeight,MSDRGMDC,MSDRGGLOS,MSDRGALOS,APRDRG,APRDRGDesc,APRDRGWeight,APRDRGMDC,APRDRGGLOS,APRDRGALOS,SOI,ROM,TotalCharge,
TotalCost,VarianceCharge,VarianceCost,ExpectedReimbursement,TotalExpected,NULL PrimaryDxCode,NULL PrimaryPCSCode,UserName,hd.InsertedDate,hd.ModifiedDate,mpdrg.PrimaryDRGGrouperAliasName,
round(cast(datediff(minute,hv.AdmitDate,isnull(hv.DischargeDate,getdate())) as float)/cast((60*24) as float),2) as LOS,'HIM' SourceType,NULL DischargePosition
from dbo.HIM_DRG hd
inner join dbo.HIM_Visit hv on hd.AccountNumber=hv.AccountNumber
left join HIM_Insurance hn on hv.VisitID=hn.VisitID and hn.SetID=1
left join MST_Insurance_PrimaryDRGGrouper pdrg on hn.InsurancePlanID=pdrg.InsurancePlanID
left join MST_PrimaryDRGGrouper mpdrg on mpdrg.PrimaryDRGGrouperID=pdrg.PrimaryDRGGrouperID
Union
select fd.AccountNumber,MSDRG,MSDRGDesc,MSDRGWeight,MSDRGMDC,MSDRGGLOS,MSDRGALOS,APRDRG,APRDRGDesc,APRDRGWeight,APRDRGMDC,APRDRGGLOS,APRDRGALOS,SOI,ROM,TotalCharge,
TotalCost,VarianceCharge,VarianceCost,ExpectedReimbursement,TotalExpected,NULL PrimaryDxCode,NULL PrimaryPCSCode,UserName,fd.InsertedDate,fd.ModifiedDate,mpdrg.PrimaryDRGGrouperAliasName,
round(cast(datediff(minute,hv.AdmitDate,isnull(hv.DischargeDate,getdate())) as float)/cast((60*24) as float),2) as LOS,'FTP' SourceType,NULL DischargePosition
from dbo.FTP_DRG fd
inner join dbo.HIM_Visit hv on fd.AccountNumber=hv.AccountNumber
left join HIM_Insurance hn on hv.VisitID=hn.VisitID and hn.SetID=1
left join MST_Insurance_PrimaryDRGGrouper pdrg on hn.InsurancePlanID=pdrg.InsurancePlanID
left join MST_PrimaryDRGGrouper mpdrg on mpdrg.PrimaryDRGGrouperID=pdrg.PrimaryDRGGrouperID
Union
select nd.AccountNumber,MSDRG,MSDRGDesc,MSDRGWeight,MSDRGMDC,MSDRGGLOS,MSDRGALOS,APRDRG,APRDRGDesc,APRDRGWeight,APRDRGMDC,APRDRGGLOS,APRDRGALOS,SOI,ROM,TotalCharge,
TotalCost,VarianceCharge,VarianceCost,ExpectedReimbursement,TotalExpected,PrimaryDxCode,PrimaryPCSCode,UserName,nd.InsertedDate,nd.ModifiedDate,mpdrg.PrimaryDRGGrouperAliasName,
round(cast(datediff(minute,hv.AdmitDate,isnull(hv.DischargeDate,getdate())) as float)/cast((60*24) as float),2) as LOS,'NLP' SourceType,NULL DischargePosition
from dbo.NLP_DRG nd
inner join dbo.HIM_Visit hv on nd.AccountNumber=hv.AccountNumber
left join HIM_Insurance hn on hv.VisitID=hn.VisitID and hn.SetID=1
left join MST_Insurance_PrimaryDRGGrouper pdrg on hn.InsurancePlanID=pdrg.InsurancePlanID
left join MST_PrimaryDRGGrouper mpdrg on mpdrg.PrimaryDRGGrouperID=pdrg.PrimaryDRGGrouperID
union
select ud.AccountNumber,MSDRG,MSDRGDesc,MSDRGWeight,MSDRGMDC,MSDRGGLOS,MSDRGALOS,APRDRG,APRDRGDesc,APRDRGWeight,APRDRGMDC,APRDRGGLOS,APRDRGALOS,SOI,ROM,TotalCharge,
TotalCost,VarianceCharge,VarianceCost,ExpectedReimbursement,TotalExpected,PrimaryDxCode,PrimaryPCSCode,dbo.fngetUserName(ApplicationUserId,'UserName'),ud.InsertedDate,ud.ModifiedDate,
mpdrg.PrimaryDRGGrouperAliasName,round(cast(datediff(minute,hv.AdmitDate,isnull(hv.DischargeDate,getdate())) as float)/cast((60*24) as float),2) as LOS,'User' SourceType,ud.DischargePosition
from dbo.User_Drg ud
inner join dbo.HIM_Visit hv on ud.AccountNumber=hv.AccountNumber
left join HIM_Insurance hn on hv.VisitID=hn.VisitID and hn.SetID=1
left join MST_Insurance_PrimaryDRGGrouper pdrg on hn.InsurancePlanID=pdrg.InsurancePlanID
left join MST_PrimaryDRGGrouper mpdrg on mpdrg.PrimaryDRGGrouperID=pdrg.PrimaryDRGGrouperID
;
GO

======================================================

View Name:HospitalServiceView

Description:

This view describes information about facility service AccountNumbers.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[HospitalServiceView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[HospitalServiceView]
AS
select AccountNumber,AdmitDate,DischargeDate,PatientClass,HospitalService
from HIM_Visit where PatientClass='I' and HospitalService not in ('13','16','30','40','71','94','96','97','VE','VB') 
and AdmitDate<=GETDATE() and LocationRoom!='' and AdmitDate is not null and AdmitDate>'08/01/2018'
and AccountNumber in ('350583303','350584076','350584528','350584632','350584828','350584941','350585030','350585466','350585519','350585529','350585536','350585537','350585553','350585613','350585729','350585763','350585767','350585770','350585788','350585807','350585835','350585840','350585841','350585844','350585859','350585861','350585894','350585895','350585908','350585936','350585946')
;
GO

======================================================

View Name:ICDCodeView

Description:

This view describes complete information about Diagnosis Codes and its related.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[ICDCodeView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ICDCodeView]
AS
select hdx.AccountNumber,hdx.DXCode,hdx.DXDesc ActualDesc,hdx.POA,hdx.DXType,hdx.DXPriority,cc.CCMCC,hdx.PhysicianID,hdx.DXDate,NULL DxCodeSource,NULL DxExclusionTypeId,hdx.UserName,
hdx.InsertedDate,hdx.ModifiedDate,case when hc.HStatus='Yes' then 'Y' else 'N' end HStatus,'HIM' SourceType,hdx.DXCodeID,0 AuditType,cdx.ShortDesc,NULL as DXCodeWeight
from dbo.HIM_DXCode hdx
inner join HIM_Visit hv on hdx.AccountNumber=hv.AccountNumber
left join MasterHRCM.dbo.CMS_CCMCCCode cc on hdx.DXCode=cc.DxCode and isnull(hv.DischargeDate,GETDATE()) between cc.EffectiveYearFrom and cc.EffectiveYearTo
left join MasterHRCM.dbo.CMS_HCCDXCode hc on hdx.DXCode=hc.DxCode
left join MasterHRCM.dbo.CMS_DxCodeDesc cdx on hdx.DXCode=cdx.ICD10Code and cdx.[Status]=1
Union
select fdx.AccountNumber,fdx.DXCode,fdx.DXDesc ActualDesc,fdx.POA,fdx.DXType,fdx.DXPriority,cc.CCMCC,fdx.PhysicianID,fdx.DXDate,NULL DxCodeSource,NULL DxExclusionTypeId,fdx.UserName,
fdx.InsertedDate,fdx.ModifiedDate,case when hc.HStatus='Yes' then 'Y' else 'N' end HStatus,'FTP' SourceType,fdx.DXCodeID,0 AuditType,cdx.ShortDesc,NULL as DXCodeWeight
from dbo.FTP_DXCode fdx
inner join HIM_Visit hv on fdx.AccountNumber=hv.AccountNumber
left join MasterHRCM.dbo.CMS_CCMCCCode cc on fdx.DXCode=cc.DxCode and isnull(hv.DischargeDate,GETDATE()) between cc.EffectiveYearFrom and cc.EffectiveYearTo
left join MasterHRCM.dbo.CMS_HCCDXCode hc on fdx.DXCode=hc.DxCode
left join MasterHRCM.dbo.CMS_DxCodeDesc cdx on fdx.DXCode=cdx.ICD10Code and cdx.[Status]=1
Union
select ndx.AccountNumber,ndx.DxCode,ndx.DxDesc ActualDesc,ndx.Poa,ndx.DxType,ndx.DxPriority,cc.CCMCC,ndx.PhysicianId,ndx.DxDate,ndx.DxCodeSource,ndx.DxExclusionTypeId,ndx.UserName,
ndx.InsertedDate,ndx.ModifiedDate,case when hc.HStatus='Yes' then 'Y' else 'N' end HStatus,'NLP' SourceType,ndx.DxCodeId,isnull(dxA.AuditType,0) AuditType,cdx.ShortDesc,
ad.MSDRGWeight as DXCodeWeight
from dbo.NLP_DXCode ndx
inner join HIM_Visit hv on ndx.AccountNumber=hv.AccountNumber
left join MasterHRCM.dbo.CMS_CCMCCCode cc on ndx.DXCode=cc.DxCode and isnull(hv.DischargeDate,GETDATE()) between cc.EffectiveYearFrom and cc.EffectiveYearTo
left join MasterHRCM.dbo.CMS_HCCDXCode hc on ndx.DXCode=hc.DxCode
left join MasterHRCM.dbo.CMS_DxCodeDesc cdx on ndx.DXCode=cdx.ICD10Code and cdx.[Status]=1
left join NLP_DxCode_Audit dxA on ndx.AccountNumber=dxA.AccountNumber and ndx.DxCode=dxA.DxCode--For Upvote/DownVite Status
left join NLP_AllICDDRG ad on ad.AccountNumber=ndx.AccountNumber and ndx.DxCode=SUBSTRING(ad.PrimaryDxCode,1,LEN(ad.PrimaryDxCode)-2)--Individual DxCode Weight
Union
select udx.AccountNumber,udx.DXCode,udx.DXDesc ActualDesc,udx.POA,udx.DXType,udx.DXPriority,cc.CCMCC,udx.PhysicianID,udx.DXDate,NULL DxCodeSource,NULL DxExclusionTypeId,
dbo.fngetUserName(ApplicationUserId,'UserName') UserName,udx.InsertedDate,udx.ModifiedDate,case when hc.HStatus='Yes' then 'Y' else 'N' end HStatus,'User' SourceType,udx.DXCodeID,
case when udx.SourceType='nlp' then 1 else 0 end AuditType,cdx.ShortDesc,udx.DXCodeWeight as DXCodeWeight
from dbo.User_DxCode udx
inner join HIM_Visit hv on udx.AccountNumber=hv.AccountNumber
left join MasterHRCM.dbo.CMS_CCMCCCode cc on udx.DXCode=cc.DxCode and isnull(hv.DischargeDate,GETDATE()) between cc.EffectiveYearFrom and cc.EffectiveYearTo
left join MasterHRCM.dbo.CMS_HCCDXCode hc on udx.DXCode=hc.DxCode
left join MasterHRCM.dbo.CMS_DxCodeDesc cdx on udx.DXCode=cdx.ICD10Code and cdx.[Status]=1
;
GO

======================================================

View Name:InsuranceView

Description:

This view describes complete information about patient insurance and its related.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[InsuranceView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE View [dbo].[InsuranceView]
AS
select distinct hv.AccountNumber,hn.InsuranceCompanyName,fc.FinancialClassName,mpdrg.PrimaryDRGGrouperAliasName
from HIM_Visit hv
left join HIM_Insurance hn on hv.VisitID=hn.VisitID and hn.SetID=1
left join MST_FinancialClass fc on hv.FinancialClass=fc.FinancialClassID
left join MST_Insurance_PrimaryDRGGrouper pdrg on hn.InsurancePlanID=pdrg.InsurancePlanID
left join MST_PrimaryDRGGrouper mpdrg on mpdrg.PrimaryDRGGrouperID=pdrg.PrimaryDRGGrouperID
;
GO

======================================================

View Name:LabTestView

Description:

This view describes whole lab results to that AccountNumbers.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[LabTestView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[LabTestView]
AS
select hlb.AccountNumber,NULL DocumentId,ld.TestName,[Result],NumericResult,hlb.Unit,ReferenceRange,LabTestDate,hlb.LOINCCode,NULL SectionHeader,HospitalDay,hlb.InsertedDate,
hlb.ModifiedDate,'HIM' SourceType,ld.UniqueId,ld.Gender,ld.TestMnemonic,hlb.MinRange,hlb.MaxRange,lts.LabTestSynonym,hlb.Deviation,hlb.DeviationType,ld.CDI,hlb.LabID
from dbo.HIM_Lab hlb
inner join dbo.HIM_Visit v on v.AccountNumber=hlb.AccountNumber
inner join dbo.HIM_Patient p on p.PatientId=v.PatientID
inner join dbo.MST_LabTestSynonym lts on lts.LabTestSynonym=hlb.TestName
inner join dbo.MST_LabDictionary ld on ld.UniqueId=lts.UniqueId
Union
select flb.AccountNumber,NULL DocumentId,ld.TestName,[Result],NumericResult,flb.Unit,ReferenceRange,LabTestDate,flb.LOINCCode,NULL SectionHeader,HospitalDay,flb.InsertedDate,
flb.ModifiedDate,'FTP' SourceType,ld.UniqueId,ld.Gender,ld.TestMnemonic,flb.MinRange,flb.MaxRange,lts.LabTestSynonym,flb.Deviation,flb.DeviationType,ld.CDI,flb.LabID
from dbo.FTP_Lab flb
inner join dbo.HIM_Visit v on v.AccountNumber=flb.AccountNumber
inner join dbo.HIM_Patient p on p.PatientId=v.PatientID
inner join dbo.MST_LabTestSynonym lts on lts.LabTestSynonym=flb.TestName
inner join dbo.MST_LabDictionary ld on ld.UniqueId=lts.UniqueId
Union
select nlb.AccountNumber,DocumentId,ld.TestName,[Result],NumericResult,nlb.Unit,ReferenceRange,LabTestDate,nlb.LOINCCode,SectionHeader,HospitalDay,nlb.InsertedDate,
nlb.ModifiedDate,'NLP' SourceType,ld.UniqueId,ld.Gender,ld.TestMnemonic,nlb.MinRange,nlb.MaxRange,lts.LabTestSynonym,nlb.Deviation,nlb.DeviationType,ld.CDI,nlb.LabID
from dbo.NLP_Lab nlb
inner join dbo.HIM_Visit v on v.AccountNumber=nlb.AccountNumber
inner join dbo.HIM_Patient p on p.PatientId=v.PatientID
inner join dbo.MST_LabTestSynonym lts on lts.LabTestSynonym=nlb.TestName
inner join dbo.MST_LabDictionary ld on ld.UniqueId=lts.UniqueId
union
select clb.AccountNumber,DocumentId,ld.TestName,ActualValue,cast([Result] as float) [Result],NULL Unit,RefRange,NULL LabTestDate,NULL LOINCCode,NULL SectionHeader,HospitalDay,clb.InsertedDate,
clb.ModifiedDate,'CDA' SourceType,ld.UniqueId,ld.Gender,ld.TestMnemonic,clb.MinRange,clb.MaxRange,lts.LabTestSynonym,clb.Deviation,clb.DeviationType,ld.CDI,clb.LabID
from dbo.CDA_Lab clb
inner join dbo.HIM_Visit v on v.AccountNumber=clb.AccountNumber
inner join dbo.HIM_Patient p on p.PatientId=v.PatientID
inner join dbo.MST_LabTestSynonym lts on lts.LabTestSynonym=clb.TestName
inner join dbo.MST_LabDictionary ld on ld.UniqueId=lts.UniqueId
where ISNUMERIC([Result])=1
;
GO

======================================================

View Name:MedicationView

Description:

This view describes complete information about medication's and its related.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[MedicationView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MedicationView]
AS
select AccountNumber,NULL DocumentID,MedicationName,MedicationType,Dose,Route,Frequency,StartDate,EndDate,PRN,Administration,GenericName,BrandName,DrugID,HospitalDay,
InsertedDate,ModifiedDate,NULL ReportDate,NULL HomeMedication,NULL PRNReason,0 StartId,0 EndId,NULL SectionHeader,0 RStatus,NULL Medication,NULL OrderStatus,
NULL OrderBy,NULL ProcStatus,'HIM_IPMed' SourceType,UniqueId,IPMedicationID from dbo.HIM_IPMedication
Union
select AccountNumber,NULL DocumentID,MedicationName,MedicationType,Dose,Route,Frequency,StartDate,EndDate,PRN,Administration,GenericName,BrandName,DrugID,HospitalDay,
InsertedDate,ModifiedDate,NULL ReportDate,NULL HomeMedication,NULL PRNReason,0 StartId,0 EndId,NULL SectionHeader,0 RStatus,NULL Medication,NULL OrderStatus,
NULL OrderBy,NULL ProcStatus,'HIM_HomeMed' SourceType,UniqueId,HomeMedicationID from dbo.HIM_HomeMedication
Union
select AccountNumber,NULL DocumentID,MedicationName,MedicationType,Dose,Route,Frequency,StartDate,EndDate,PRN,Administration,GenericName,BrandName,DrugID,HospitalDay,
InsertedDate,ModifiedDate,ReportDate,NULL HomeMedication,NULL PRNReason,0 StartId,0 EndId,NULL SectionHeader,0 RStatus,NULL Medication,NULL OrderStatus,
NULL OrderBy,NULL ProcStatus,'FTP_IPMed' SourceType,UniqueId,IPMedicationID from dbo.FTP_IPMedication
Union
select AccountNumber,DocumentID,NULL MedicationName,NULL MedicationType,NULL Dose,NULL Route,Frequency,NULL StartDate,NULL EndDate,NULL PRN,NULL Administration,
NULL GenericName,NULL BrandName,NULL DrugID,HospitalDay,InsertedDate,ModifiedDate,NULL ReportDate,NULL HomeMedication,NULL PRNReason,StartId,EndId,SectionHeader,RStatus,
Medication,OrderStatus,OrderBy,ProcStatus,'CDA_IPMed' SourceType,ISNULL(UniqueId,0),IPMedicationID from dbo.CDA_IPMedication
Union
select AccountNumber,DocumentID,NULL MedicationName,NULL MedicationType,Dose,Route,Frequency,StartDate,NULL EndDate,PRN,NULL Administration,NULL GenericName,NULL BrandName,
NULL DrugID,HospitalDay,InsertedDate,ModifiedDate,NULL ReportDate,HomeMedication,PRNReason,StartId,EndId,SectionHeader,RStatus,NULL Medication,NULL OrderStatus,
NULL OrderBy,NULL ProcStatus,'CDA_HomeMed' SourceType,ISNULL(UniqueId,0),HomeMedicationId from dbo.CDA_HomeMedication
;
GO

======================================================

View Name:NurseNoteView

Description:

This view describes information about Nursing data.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[NurseNoteView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[NurseNoteView]
AS
select distinct AccountNumber,nnd.EventType,nnd.EventDesc,nnd.EventResult,NurseNoteDate,HospitalDay,fn.InsertedDate,fn.ModifiedDate,fn.ReportDate,'FTP' SourceType,nnd.UniqueId,
fn.NurseNoteID
from dbo.FTP_NurseNote fn
inner join MST_NurseNoteDictionary nnd on nnd.UniqueId=fn.UniqueId
union
select distinct AccountNumber,nnd.EventType,nnd.EventDesc,nnd.EventResult,NurseNoteDate,HospitalDay,hn.InsertedDate,hn.ModifiedDate,NULL ReportDate,'HIM' SourceType,nnd.UniqueId,
hn.NurseNoteID
from dbo.HIM_NurseNote hn
inner join MST_NurseNoteDictionary nnd on nnd.UniqueId=hn.UniqueId
;
GO

======================================================

View Name:PatientView

Description:

This view describes complete information about PatientData.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[PatientView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PatientView]
AS
select v.MRN,v.AccountNumber,p.FirstName,p.MiddleName,p.LastName,p.Gender,
cast(dbo.convertUtcToTimeZone('IST',p.DOB) as date) DOB,
dbo.convertUtcToTimeZone('IST',v.AdmitDate) AdmitDate,
dbo.convertUtcToTimeZone('IST',v.DischargeDate) DischargeDate,v.FinancialClass,v.HospitalService,v.LocationID,
v.LocationRoom,v.LocationBed,
round(cast(datediff(minute,dbo.convertUtcToTimeZone('IST',v.AdmitDate),dbo.convertUtcToTimeZone('IST', v.DischargeDate)) as float)/cast((60*24) as float),2) as LOS,
DATEDIFF(MONTH,dbo.convertUtcToTimeZone('IST',p.DOB),dbo.convertUtcToTimeZone('IST',v.AdmitDate))/12 as Age,
DATEDIFF(DAY,dbo.convertUtcToTimeZone('IST',p.DOB),dbo.convertUtcToTimeZone('IST',v.AdmitDate)) as AgeInDays
from dbo.HIM_Visit v inner join dbo.HIM_Patient p on v.PatientID=p.PatientId
;
GO

======================================================

View Name:PCSCodeView

Description:

This view describes complete information about Procedure Coding System.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[PCSCodeView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PCSCodeView]
AS
select AccountNumber,PCSCode,PCSDesc ActualDesc,PCSType,PCSPriority,PhysicianID,PCSDate,NULL PCSExclusionTypeId,UserName,InsertedDate,ModifiedDate,'HIM' SourceType,hpc.PCSCodeID,
0 AuditType,pdsc.ShortDesc
from dbo.HIM_PCSCode hpc
left join MasterHRCM.dbo.CMS_PcsCodeDesc pdsc on hpc.PCSCode=pdsc.CPTCode and pdsc.[Status]=1
Union
select AccountNumber,PCSCode,PCSDesc ActualDesc,PCSType,PCSPriority,PhysicianID,PCSDate,NULL PCSExclusionTypeId,UserName,InsertedDate,ModifiedDate,'FTP' SourceType,fpc.PCSCodeID,
0 AuditType,pdsc.ShortDesc
from dbo.FTP_PCSCode fpc
left join MasterHRCM.dbo.CMS_PcsCodeDesc pdsc on fpc.PCSCode=pdsc.CPTCode and pdsc.[Status]=1
Union
select npc.AccountNumber,npc.PCSCode,npc.PCSDesc ActualDesc,PCSType,PCSPriority,PhysicianID,PCSDate,PCSExclusionTypeId,UserName,npc.InsertedDate,npc.ModifiedDate,'NLP' SourceType,
npc.PCSCodeID,isnull(pca.AuditType,0) AuditType,pdsc.ShortDesc
from dbo.NLP_PCSCode npc
left join MasterHRCM.dbo.CMS_PcsCodeDesc pdsc on npc.PCSCode=pdsc.CPTCode and pdsc.[Status]=1
left join NLP_PcsCode_Audit pca on npc.AccountNumber=pca.AccountNumber and npc.PCSCode=pca.PCSCode--For Upvote/DownVite Status
union
select AccountNumber,PCSCode,upc.PCSDesc ActualDesc,PCSType,PCSPriority,PhysicianID,PCSDate,NULL PCSExclusionTypeId,dbo.fngetUserName(upc.ApplicationUserId,'FullName') UserName,
InsertedDate,ModifiedDate,'User' SourceType,upc.PCSCodeID,case when upc.SourceType='nlp' then 1 else 0 end AuditType,pdsc.ShortDesc
from dbo.User_PCSCode upc
left join MasterHRCM.dbo.CMS_PcsCodeDesc pdsc on upc.PCSCode=pdsc.CPTCode and pdsc.[Status]=1
;
GO

======================================================

View Name:PhysicianView

Description:

This view describes complete information about Physician details.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[PhysicianView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PhysicianView]
AS
select distinct mp.PhysicianID,mp.FirstName,mp.LastName,mp.MiddleName,mp.Designation,mp.Phone,mp.Fax,mp.Address,mp.Speciality,mp.Speciality2,mp.Speciality3,
mp.Department,mp.NPI,hpv.PhysicianTypeID,hpv.PhysicianTypeDesc,hpv.AccountNumber,mp.InsertedDate,mp.ModifiedDate from dbo.MST_Physician mp
left join dbo.HIM_Physician_Visit hpv on hpv.PhysicianID=mp.PhysicianID
;
GO

======================================================

View Name:ReminderHistoryView

Description:

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[ReminderHistoryView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [dbo].[ReminderHistoryView]
AS
select distinct ar.AccountNumber,ar.Clause,ar.Value1,ar.Value2,ar.ClinicalDay,ar.ClinicalUnit,ar.Note,ar.CreatedBy,ar.ModuleId,ar.PageId,
rt.ReminderType,au.UserName,
case 
when ld.TestName is not null then ld.TestName
when vd.VitalSign is not null then vd.VitalSign
when md.MedicationName is not null then md.MedicationName
when idt.InternalDocumentAliasName is not null then idt.InternalDocumentAliasName
end as ReminderItemName
from User_AccountReminder ar
inner join MST_ReminderType rt on ar.ReminderTypeId=rt.ReminderTypeId
inner join MasterHrcm.dbo.MST_ApplicationUser au on ar.CreatedBy=au.ApplicationUserId
left join MST_LabDictionary ld on ar.ClinicalFieldId=ld.UniqueId and ar.ClinicalFieldType='Lab'
left join MST_VitalSignDictionary vd on ar.ClinicalFieldId=vd.UniqueId and ar.ClinicalFieldType='VitalSign'
left join MST_MedicalDictionary md on ar.ClinicalFieldId=md.UniqueId and ar.ClinicalFieldType='Medication'
left join MST_InternalDocumentType idt on ar.ClinicalFieldId=idt.InternalDocumentTypeID and ar.ClinicalFieldType='NewDocument'
;
GO

======================================================

View Name:VitalSignView

Description:

This view describes complete information about Vital Signs and it's related Data.

Schema:

USE [HRCM]
GO
/****** Object:  View [dbo].[VitalSignView]    Script Date: 08/23/2019 18:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[VitalSignView]
AS
select hvs.AccountNumber,NULL DocumentId,mvsd.VitalSign,[Result],NumericResult,NumericResult2,NULL Unit,VitalSignDate,hvs.LoincCode,HospitalDay,hvs.InsertedDate,hvs.ModifiedDate,NULL ReportDate,
NULL StartId,NULL EndId,NULL SectionHeader,'HIM' SourceType,mvsd.Gender,mvsd.MinRange,mvsd.MaxRange,mvsd.PanicMinRange,mvsd.PanicMaxRange,mvsd.UniqueId,hvs.VitalSignID
from dbo.HIM_VitalSign hvs
inner join HIM_Visit v on v.AccountNumber=hvs.AccountNumber
inner join HIM_Patient p on p.PatientId=v.PatientID
inner join MST_VitalSignSynonym mvs on mvs.VitalSignSynonym=hvs.VitalSign
inner join MST_VitalSignDictionary mvsd on mvsd.UniqueId=mvs.UniqueId and mvsd.Gender=p.Gender
union
select fvs.AccountNumber,NULL DocumentId,mvsd.VitalSign,[Result],NumericResult,NumericResult2,NULL Unit,VitalSignDate,fvs.LOINCCode,HospitalDay,fvs.InsertedDate,fvs.ModifiedDate,ReportDate,
NULL StartId,NULL EndId,NULL SectionHeader,'FTP' SourceType,mvsd.Gender,mvsd.MinRange,mvsd.MaxRange,mvsd.PanicMinRange,mvsd.PanicMaxRange,mvsd.UniqueId,fvs.VitalSignID
from dbo.FTP_VitalSign fvs
inner join HIM_Visit v on v.AccountNumber=fvs.AccountNumber
inner join HIM_Patient p on p.PatientId=v.PatientID
inner join MST_VitalSignSynonym mvs on mvs.VitalSignSynonym=fvs.VitalSign
inner join MST_VitalSignDictionary mvsd on mvsd.UniqueId=mvs.UniqueId and mvsd.Gender=p.Gender
union
select nvs.AccountNumber,DocumentId,mvsd.VitalSign,[Result],NumericResult,NumericResult2,Unit,VitalSignDate,nvs.LOINCCode,HospitalDay,nvs.InsertedDate,nvs.ModifiedDate,NULL ReportDate,
StartId,EndId,SectionHeader,'NLP' SourceType,mvsd.Gender,mvsd.MinRange,mvsd.MaxRange,mvsd.PanicMinRange,mvsd.PanicMaxRange,mvsd.UniqueId,nvs.VitalSignID
from dbo.NLP_VitalSign nvs
inner join HIM_Visit v on v.AccountNumber=nvs.AccountNumber
inner join HIM_Patient p on p.PatientId=v.PatientID
inner join MST_VitalSignSynonym mvs on mvs.VitalSignSynonym=nvs.VitalSign
inner join MST_VitalSignDictionary mvsd on mvsd.UniqueId=mvs.UniqueId and mvsd.Gender=p.Gender
;
GO