Crystal Reports Question
Hi,
I have a Date defined in my SQL Server 2005 stored procedure as DateTime. The field is coming over as String in the Crystal Reports! Why is this happening and how can I fix it so the date comes over as DateTime in the report.
Thanks
I have a Date defined in my SQL Server 2005 stored procedure as DateTime. The field is coming over as String in the Crystal Reports! Why is this happening and how can I fix it so the date comes over as DateTime in the report.
Thanks
Can you post the SP?
mlmcc
mlmcc
ASKER
It is the Admit Date andDischarge Date - they come out as String in Crystal Reports XI. Thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[hsp_eMAR_Downtime]
as
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #tmpClient
(
ClientGUID numeric(16,0) NULL,
VisitGUID numeric(16,0),
ChartGUID numeric(16,0) NULL,
ClientDisplayName varchar(50) NULL,
CurrentLocation varchar(50) NULL,
CurrentUnit varchar(50) NULL,
ProviderDisplayName varchar(50) NULL,
VisitStatus char(3) NULL,
VisitIDCode char(20) NULL,
AdmitDtm datetime,
DischargeDtm datetime,
TypeCode varchar(50) NULL,
BirthYearNum int,
BirthMonthNum int,
BirthDayNum int,
GenderCode varchar(15) NULL,
IDCode char(20) NULL
)
CREATE TABLE #tmpTasks
(
ClientGUID numeric(16,0) NULL,
ClientVisitGUID numeric(16,0) NULL,
OrderGUID numeric(16,0),
OrderID char(9) NULL,
OrderName varchar(125) NULL,
OrderSignificantDtm datetime NULL,
OrderSummaryLine text NULL,
OrderIsConditional bit,
ConditionsText varchar(255) NULL,
OrderRequestedBy varchar(83) NULL,
OrderCatalogMasterItemGUID
OrderAdminInstructions varchar(2000) NULL,
OrderStart datetime,
OrderStop datetime,
OrderTaskGUID numeric(16,0),
CatalogItemTaskGUID numeric(16,0) NULL,
ScheduleTypeCode varchar(30) NULL,
TaskName varchar(370) NULL,
TaskStartDtm datetime NULL,
TaskStopDtm datetime NULL,
TaskScheduleTypeSeqNum int,
TaskReviewCategory varchar(30) NULL,
TaskReviewCategorySeqNum int,
OrderHeading varchar(255) NULL,
TaskSeqNum int,
PrimaryParentOccurrenceGUI
)
CREATE TABLE #ResultSet (
ClientGUID numeric(16,0),
VisitGUID numeric(16,0),
ChartGUID numeric(16,0),
ClientDisplayName varchar(50),
CurrentLocation varchar(50),
CurrentUnit varchar(50),
ProviderDisplayName varchar(50),
VisitStatus varchar(3),
VisitIDCode varchar(20),
AdmitDtm datetime,
DischargeDtm datetime,
TypeCode varchar(50),
BirthYearNum int,
BirthMonthNum int,
BirthDayNum int,
GenderCode varchar(15),
IDCode varchar(20),
ClientGUIDVar varchar(16),
OrderGUIDVar varchar(16),
OrderTaskGUIDVar varchar(16),
CodeNum int,
Code varchar(255),
Text varchar(463),
SvrtyLevelCode varchar(400),
AllergyTypeCode varchar(15),
AllergyDeclarationType int,
CodeGUID numeric(16,0),
OrderGUID numeric(16,0),
OrderID varchar(9),
OrderName varchar(125),
OrderSignificantDtm datetime,
OrderSummaryLine varchar(2000),
OrderIsConditional int,
ConditionsText varchar(255),
OrderRequestedBy varchar(83),
OrderCatalogMasterItemGUID
OrderTaskGUID numeric(16,0),
CatalogItemTaskGUID numeric(16,0),
ScheduleTypeCode varchar(30),
TaskName varchar(370),
TaskStartDtm datetime,
TaskStopDtm datetime,
TaskScheduleTypeSeqNum int,
TaskReviewCategory varchar(30),
TaskReviewCategorySeqNum int,
OrderAdminInstructions varchar(2000),
OrderHeading varchar(255),
TaskSeqNum int,
PrimaryParentOccurrenceGUI
Height decimal(6,1),
Weight decimal(6,1),
BSA decimal(6,2),
OrderStart datetime,
OrderStop datetime)
INSERT INTO #tmpClient
SELECT /* Get a list of all visit info */
ClientGUID = cavl.ClientGUID,
VisitGUID = cv.GUID,
ChartGUID = cavl.ChartGUID,
cavl.ClientDisplayName,
cavl.CurrentLocation,
cavl.LocnGroupName,
cavl.ProviderDisplayName,
cavl.VisitStatus,
Replace(cavl.VisitIDCode, '-', '') as VisitIDCode,
cavl.AdmitDtm,
cavl.DischargeDtm,
cavl.TypeCode,
cavl.BirthYearNum,
cavl.BirthMonthNum,
cavl.BirthDayNum,
cavl.GenderCode,
Replace(cavl.IDCode, '-', '') as IDCode
FROM CV3ActiveVisitList cavl WITH(NOLOCK)
JOIN CV3ClientVisit cv WITH(NOLOCK)
ON cv.ClientGUID = cavl.ClientGUID
and cv.ChartGUID = cavl.ChartGUID
and cv.GUID = cavl.GUID
WHERE
cavl.VisitStatus = 'ADM'
and cavl.TypeCode in ('Inpatient', 'Emergency')
--select * from #tmpClient
INSERT INTO #tmpTasks
SELECT
cv.ClientGUID,
cv.VisitGUID,
o.GUID,
o.IDCode,
o.Name,
o.SignificantDtm,
o.SummaryLine,
o.IsConditional,
o.ConditionsText,
RequestedBy =
CASE
WHEN o.CareProviderGUID IS NOT NULL THEN
(SELECT ISNULL(DisplayName,'') + ' (' + ISNULL(OccupationCode,'') + ')'
FROM CV3User
WHERE CV3User.GUID = o.CareProviderGUID)
ELSE NULL
END,
o.OrderCatalogMasterItemGU
AdminInstructions,
o.RequestedDtm,
o.StopDtm,
ot.GUID,
ot.CatalogItemTaskGUID,
TaskScheduleType = ot.ScheduleTypeCode,
TaskName =
CASE
when ot.TaskName = '<OrderName + SummaryLine>' then o.Name
else ot.TaskName
END,
ot.StartDtm,
ot.StopDtm,
TaskScheduleTypeSeqNum = st.SeqNum,
TaskReviewCategory = trc.Code,
TaskReviewCategorySeqNum = trc.SeqNum,
OrderHeading = Convert(varchar(255), o.Name + isnull(' ' + o.SummaryLine,'')),
ot.TaskSeqNum,
ot.PrimaryParentOccurrence
FROM
#tmpClient cv
JOIN CV3Order o WITH(NOLOCK)
on o.ClientGUID = cv.ClientGUID
and o.ChartGUID = cv.ChartGUID
and o.ClientVisitGUID = cv.VisitGUID
and o.ClientVisitGUID = cv.VisitGUID
JOIN SXAMMOrder mmo WITH(NOLOCK) --limit to medication orders
on o.GUID = mmo.OrderGUID
LEFT OUTER JOIN CV3MedicationExtension m WITH(NOLOCK)
ON (o.GUID = m.GUID)
JOIN CV3OrderTask ot WITH(NOLOCK)
on ot.ClientGUID = o.ClientGUID
and ot.ChartGUID = o.ChartGUID
and ot.OrderGUID = o.GUID
JOIN CV3TaskScheduleType st WITH(NOLOCK)
ON ( ot.ScheduleTypeCode = st.Code )
JOIN CV3CatalogItemTask cit WITH(NOLOCK)
ON ( ot.CatalogItemTaskGUID = cit.GUID)
JOIN CV3TaskReviewCategory trc WITH(NOLOCK)
ON ( cit.TaskReviewCategoryGUID
WHERE
Convert(Varchar, ot.StopDtm, 101) >= Convert(Varchar, GetDate(), 101)
-- oto.TaskStatusCode in ()
-- and trc.Code in ()
--select * from #tmpTasks
INSERT INTO #ResultSet
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
cast(t.OrderGUID as varchar(16)) as OrderGUIDVar,
cast(t.OrderTaskGUID as varchar(16)) as OrderTaskGUIDVar,
CodeNum = 4, /* Task Occurrence Info */
Code = '',
Text = '',
SvrtyLevelCode = '',
TypeCode = '',
AllergyDeclarationType = (
select isnull(min(type), 4)
from CV3AllergyDeclaration WITH(NOLOCK)
where #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
-- and #tmpClient.VisitGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
CodeGUID = 0,
t.OrderGUID,
t.OrderID,
t.OrderName,
t.OrderSignificantDtm,
t.OrderSummaryLine,
t.OrderIsConditional,
t.ConditionsText,
t.OrderRequestedBy,
t.OrderCatalogMasterItemGU
t.OrderTaskGUID,
t.CatalogItemTaskGUID,
t.ScheduleTypeCode,
t.TaskName,
t.TaskStartDtm,
t.TaskStopDtm,
t.TaskScheduleTypeSeqNum,
t.TaskReviewCategory,
t.TaskReviewCategorySeqNum
t.OrderAdminInstructions,
OrderHeading,
t.TaskSeqNum,
t.PrimaryParentOccurrenceG
Height = NULL,
Weight = NULL,
BSA = NULL,
t.OrderStart,
t.OrderStop
FROM #tmpClient
LEFT OUTER JOIN #tmpTasks t
ON ( #tmpClient.ClientGUID = t.ClientGUID
AND #tmpClient.VisitGUID = t.ClientVisitGUID )
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* Standard Allergy Info */
Code = CV3Allergen.Code,
Text = ISNULL(CV3Allergen.Code,''
CV3AllergyDeclaration.Svrt
CV3Allergen.TypeCode,
AllergyDeclarationType = 0, --(select min(type) from CV3AllergyDeclaration where #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
CodeGUID = CV3AllergyDeclaration.GUID
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
JOIN CV3AllergyDeclaration WITH(NOLOCK)
JOIN CV3Allergen WITH(NOLOCK)
ON ( CV3Allergen.GUID = CV3AllergyDeclaration.Alle
ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
AND CV3AllergyDeclaration.Type
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* '<Other>' Allergy Info */
Code = CV3EnumReference.Reference
Text = Convert(varchar(50),CV3All
CV3AllergyDeclaration.Svrt
CV3AllergyDeclaration.Type
AllergyDeclarationType = CV3AllergyDeclaration.Type
CodeGUID = CV3AllergyDeclaration.GUID
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
JOIN CV3AllergyDeclaration WITH(NOLOCK)
JOIN CV3EnumReference WITH(NOLOCK)
ON ( CV3EnumReference.EnumValue
AND Cv3EnumReference.TableName
AND CV3EnumReference.Columnnam
ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
AND CV3AllergyDeclaration.Type
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* 'AllergyStatusUnknown' or 'UnknownAllergies' Allergy Info */
HVCEnvProfile.Value as Code,
NULL as Text,
NULL as SvrtyLevelCode,
NULL as TypeCode, /* put info on this value since if this value is empty no allergy prints */
AllergyDeclarationType = CV3AllergyDeclaration.Type
CodeGUID = CV3AllergyDeclaration.GUID
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
JOIN CV3AllergyDeclaration WITH(NOLOCK)
JOIN CV3EnumReference WITH(NOLOCK)
ON ( CV3EnumReference.EnumValue
AND Cv3EnumReference.TableName
AND CV3EnumReference.Columnnam
JOIN HVCEnvProfile WITH(NOLOCK)
on( HVCEnvProfile.HierarchyCod
and HVCEnvProfile.Code = CV3EnumReference.Reference
ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
AND CV3AllergyDeclaration.Type
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 2, /* Height/Weight information */
NULL,
NULL,
NULL,
NULL,
AllergyDeclarationType = (
select isnull(min(type), 4)
from CV3AllergyDeclaration WITH(NOLOCK)
where #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
-- and #tmpClient.VisitGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
CodeGUID = NULL,
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = Convert(decimal(6,1),HT.te
Weight = convert(decimal(6,1),conve
BSA = CASE
WHEN (Year(getdate())-#tmpClien
THEN Convert(decimal(6,2),(Powe
ELSE Convert(decimal(6,2),SQRT(
END,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
LEFT OUTER JOIN CV3PhysicalNoteDeclaration
on #tmpClient.ClientGUID = HT.ClientGUID
--AND #tmpClient.ChartGUID = HT.ChartGUID --UNCOMMENT WHEN MOVING TO LIVE
--AND #tmpClient.VisitGUID = HT.ClientVisitGUID --UNCOMMENT WHEN MOVING TO LIVE
AND HT.Status = 'Active'
AND HT.TypeCode = 'Height'
AND HT.Text is NOT NULL
AND HT.Text <> '0'
LEFT OUTER JOIN CV3PhysicalNoteDeclaration
on #tmpClient.ClientGUID = WT.ClientGUID
--AND #tmpClient.ChartGUID = WT.ChartGUID --UNCOMMENT WHEN MOVING TO LIVE
-- AND #tmpClient.VisitGUID = WT.ClientVisitGUID --UNCOMMENT WHEN MOVING TO LIVE
AND WT.Status = 'Active'
AND WT.TypeCode = 'Weight'
-- Compensate when no allergy information has been added to visit
INSERT INTO #ResultSet
SELECT c.*,
cast(c.ClientGUID as varchar(16)) as ClientGUIDVar,
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* When no allergy info has been added to patient chart, return row stating this */
'Allergy Info Not Entered' as Code,
NULL as Text,
NULL as SvrtyLevelCode,
NULL as AllergyTypeCode,
AllergyDeclarationType = 4,
CodeGUID = NULL,
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient c
where not exists (SELECT VisitGUID from #ResultSet rs where rs.VisitGUID = c.VisitGUID and rs.CodeNum = 1)
select *
FROM #ResultSet
order by
ClientDisplayName, VisitGUID, CodeNum, ScheduleTypeCode, TaskReviewCategory , TaskName
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[hsp_eMAR_Downtime]
as
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #tmpClient
(
ClientGUID numeric(16,0) NULL,
VisitGUID numeric(16,0),
ChartGUID numeric(16,0) NULL,
ClientDisplayName varchar(50) NULL,
CurrentLocation varchar(50) NULL,
CurrentUnit varchar(50) NULL,
ProviderDisplayName varchar(50) NULL,
VisitStatus char(3) NULL,
VisitIDCode char(20) NULL,
AdmitDtm datetime,
DischargeDtm datetime,
TypeCode varchar(50) NULL,
BirthYearNum int,
BirthMonthNum int,
BirthDayNum int,
GenderCode varchar(15) NULL,
IDCode char(20) NULL
)
CREATE TABLE #tmpTasks
(
ClientGUID numeric(16,0) NULL,
ClientVisitGUID numeric(16,0) NULL,
OrderGUID numeric(16,0),
OrderID char(9) NULL,
OrderName varchar(125) NULL,
OrderSignificantDtm datetime NULL,
OrderSummaryLine text NULL,
OrderIsConditional bit,
ConditionsText varchar(255) NULL,
OrderRequestedBy varchar(83) NULL,
OrderCatalogMasterItemGUID
OrderAdminInstructions varchar(2000) NULL,
OrderStart datetime,
OrderStop datetime,
OrderTaskGUID numeric(16,0),
CatalogItemTaskGUID numeric(16,0) NULL,
ScheduleTypeCode varchar(30) NULL,
TaskName varchar(370) NULL,
TaskStartDtm datetime NULL,
TaskStopDtm datetime NULL,
TaskScheduleTypeSeqNum int,
TaskReviewCategory varchar(30) NULL,
TaskReviewCategorySeqNum int,
OrderHeading varchar(255) NULL,
TaskSeqNum int,
PrimaryParentOccurrenceGUI
)
CREATE TABLE #ResultSet (
ClientGUID numeric(16,0),
VisitGUID numeric(16,0),
ChartGUID numeric(16,0),
ClientDisplayName varchar(50),
CurrentLocation varchar(50),
CurrentUnit varchar(50),
ProviderDisplayName varchar(50),
VisitStatus varchar(3),
VisitIDCode varchar(20),
AdmitDtm datetime,
DischargeDtm datetime,
TypeCode varchar(50),
BirthYearNum int,
BirthMonthNum int,
BirthDayNum int,
GenderCode varchar(15),
IDCode varchar(20),
ClientGUIDVar varchar(16),
OrderGUIDVar varchar(16),
OrderTaskGUIDVar varchar(16),
CodeNum int,
Code varchar(255),
Text varchar(463),
SvrtyLevelCode varchar(400),
AllergyTypeCode varchar(15),
AllergyDeclarationType int,
CodeGUID numeric(16,0),
OrderGUID numeric(16,0),
OrderID varchar(9),
OrderName varchar(125),
OrderSignificantDtm datetime,
OrderSummaryLine varchar(2000),
OrderIsConditional int,
ConditionsText varchar(255),
OrderRequestedBy varchar(83),
OrderCatalogMasterItemGUID
OrderTaskGUID numeric(16,0),
CatalogItemTaskGUID numeric(16,0),
ScheduleTypeCode varchar(30),
TaskName varchar(370),
TaskStartDtm datetime,
TaskStopDtm datetime,
TaskScheduleTypeSeqNum int,
TaskReviewCategory varchar(30),
TaskReviewCategorySeqNum int,
OrderAdminInstructions varchar(2000),
OrderHeading varchar(255),
TaskSeqNum int,
PrimaryParentOccurrenceGUI
Height decimal(6,1),
Weight decimal(6,1),
BSA decimal(6,2),
OrderStart datetime,
OrderStop datetime)
INSERT INTO #tmpClient
SELECT /* Get a list of all visit info */
ClientGUID = cavl.ClientGUID,
VisitGUID = cv.GUID,
ChartGUID = cavl.ChartGUID,
cavl.ClientDisplayName,
cavl.CurrentLocation,
cavl.LocnGroupName,
cavl.ProviderDisplayName,
cavl.VisitStatus,
Replace(cavl.VisitIDCode, '-', '') as VisitIDCode,
cavl.AdmitDtm,
cavl.DischargeDtm,
cavl.TypeCode,
cavl.BirthYearNum,
cavl.BirthMonthNum,
cavl.BirthDayNum,
cavl.GenderCode,
Replace(cavl.IDCode, '-', '') as IDCode
FROM CV3ActiveVisitList cavl WITH(NOLOCK)
JOIN CV3ClientVisit cv WITH(NOLOCK)
ON cv.ClientGUID = cavl.ClientGUID
and cv.ChartGUID = cavl.ChartGUID
and cv.GUID = cavl.GUID
WHERE
cavl.VisitStatus = 'ADM'
and cavl.TypeCode in ('Inpatient', 'Emergency')
--select * from #tmpClient
INSERT INTO #tmpTasks
SELECT
cv.ClientGUID,
cv.VisitGUID,
o.GUID,
o.IDCode,
o.Name,
o.SignificantDtm,
o.SummaryLine,
o.IsConditional,
o.ConditionsText,
RequestedBy =
CASE
WHEN o.CareProviderGUID IS NOT NULL THEN
(SELECT ISNULL(DisplayName,'') + ' (' + ISNULL(OccupationCode,'') + ')'
FROM CV3User
WHERE CV3User.GUID = o.CareProviderGUID)
ELSE NULL
END,
o.OrderCatalogMasterItemGU
AdminInstructions,
o.RequestedDtm,
o.StopDtm,
ot.GUID,
ot.CatalogItemTaskGUID,
TaskScheduleType = ot.ScheduleTypeCode,
TaskName =
CASE
when ot.TaskName = '<OrderName + SummaryLine>' then o.Name
else ot.TaskName
END,
ot.StartDtm,
ot.StopDtm,
TaskScheduleTypeSeqNum = st.SeqNum,
TaskReviewCategory = trc.Code,
TaskReviewCategorySeqNum = trc.SeqNum,
OrderHeading = Convert(varchar(255), o.Name + isnull(' ' + o.SummaryLine,'')),
ot.TaskSeqNum,
ot.PrimaryParentOccurrence
FROM
#tmpClient cv
JOIN CV3Order o WITH(NOLOCK)
on o.ClientGUID = cv.ClientGUID
and o.ChartGUID = cv.ChartGUID
and o.ClientVisitGUID = cv.VisitGUID
and o.ClientVisitGUID = cv.VisitGUID
JOIN SXAMMOrder mmo WITH(NOLOCK) --limit to medication orders
on o.GUID = mmo.OrderGUID
LEFT OUTER JOIN CV3MedicationExtension m WITH(NOLOCK)
ON (o.GUID = m.GUID)
JOIN CV3OrderTask ot WITH(NOLOCK)
on ot.ClientGUID = o.ClientGUID
and ot.ChartGUID = o.ChartGUID
and ot.OrderGUID = o.GUID
JOIN CV3TaskScheduleType st WITH(NOLOCK)
ON ( ot.ScheduleTypeCode = st.Code )
JOIN CV3CatalogItemTask cit WITH(NOLOCK)
ON ( ot.CatalogItemTaskGUID = cit.GUID)
JOIN CV3TaskReviewCategory trc WITH(NOLOCK)
ON ( cit.TaskReviewCategoryGUID
WHERE
Convert(Varchar, ot.StopDtm, 101) >= Convert(Varchar, GetDate(), 101)
-- oto.TaskStatusCode in ()
-- and trc.Code in ()
--select * from #tmpTasks
INSERT INTO #ResultSet
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
cast(t.OrderGUID as varchar(16)) as OrderGUIDVar,
cast(t.OrderTaskGUID as varchar(16)) as OrderTaskGUIDVar,
CodeNum = 4, /* Task Occurrence Info */
Code = '',
Text = '',
SvrtyLevelCode = '',
TypeCode = '',
AllergyDeclarationType = (
select isnull(min(type), 4)
from CV3AllergyDeclaration WITH(NOLOCK)
where #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
-- and #tmpClient.VisitGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
CodeGUID = 0,
t.OrderGUID,
t.OrderID,
t.OrderName,
t.OrderSignificantDtm,
t.OrderSummaryLine,
t.OrderIsConditional,
t.ConditionsText,
t.OrderRequestedBy,
t.OrderCatalogMasterItemGU
t.OrderTaskGUID,
t.CatalogItemTaskGUID,
t.ScheduleTypeCode,
t.TaskName,
t.TaskStartDtm,
t.TaskStopDtm,
t.TaskScheduleTypeSeqNum,
t.TaskReviewCategory,
t.TaskReviewCategorySeqNum
t.OrderAdminInstructions,
OrderHeading,
t.TaskSeqNum,
t.PrimaryParentOccurrenceG
Height = NULL,
Weight = NULL,
BSA = NULL,
t.OrderStart,
t.OrderStop
FROM #tmpClient
LEFT OUTER JOIN #tmpTasks t
ON ( #tmpClient.ClientGUID = t.ClientGUID
AND #tmpClient.VisitGUID = t.ClientVisitGUID )
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* Standard Allergy Info */
Code = CV3Allergen.Code,
Text = ISNULL(CV3Allergen.Code,''
CV3AllergyDeclaration.Svrt
CV3Allergen.TypeCode,
AllergyDeclarationType = 0, --(select min(type) from CV3AllergyDeclaration where #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
CodeGUID = CV3AllergyDeclaration.GUID
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
JOIN CV3AllergyDeclaration WITH(NOLOCK)
JOIN CV3Allergen WITH(NOLOCK)
ON ( CV3Allergen.GUID = CV3AllergyDeclaration.Alle
ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
AND CV3AllergyDeclaration.Type
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* '<Other>' Allergy Info */
Code = CV3EnumReference.Reference
Text = Convert(varchar(50),CV3All
CV3AllergyDeclaration.Svrt
CV3AllergyDeclaration.Type
AllergyDeclarationType = CV3AllergyDeclaration.Type
CodeGUID = CV3AllergyDeclaration.GUID
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
JOIN CV3AllergyDeclaration WITH(NOLOCK)
JOIN CV3EnumReference WITH(NOLOCK)
ON ( CV3EnumReference.EnumValue
AND Cv3EnumReference.TableName
AND CV3EnumReference.Columnnam
ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
AND CV3AllergyDeclaration.Type
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* 'AllergyStatusUnknown' or 'UnknownAllergies' Allergy Info */
HVCEnvProfile.Value as Code,
NULL as Text,
NULL as SvrtyLevelCode,
NULL as TypeCode, /* put info on this value since if this value is empty no allergy prints */
AllergyDeclarationType = CV3AllergyDeclaration.Type
CodeGUID = CV3AllergyDeclaration.GUID
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
JOIN CV3AllergyDeclaration WITH(NOLOCK)
JOIN CV3EnumReference WITH(NOLOCK)
ON ( CV3EnumReference.EnumValue
AND Cv3EnumReference.TableName
AND CV3EnumReference.Columnnam
JOIN HVCEnvProfile WITH(NOLOCK)
on( HVCEnvProfile.HierarchyCod
and HVCEnvProfile.Code = CV3EnumReference.Reference
ON ( #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
AND CV3AllergyDeclaration.Type
UNION ALL
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 2, /* Height/Weight information */
NULL,
NULL,
NULL,
NULL,
AllergyDeclarationType = (
select isnull(min(type), 4)
from CV3AllergyDeclaration WITH(NOLOCK)
where #tmpClient.ClientGUID = CV3AllergyDeclaration.Clie
-- and #tmpClient.VisitGUID = CV3AllergyDeclaration.Clie
AND CV3AllergyDeclaration.Stat
CodeGUID = NULL,
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = Convert(decimal(6,1),HT.te
Weight = convert(decimal(6,1),conve
BSA = CASE
WHEN (Year(getdate())-#tmpClien
THEN Convert(decimal(6,2),(Powe
ELSE Convert(decimal(6,2),SQRT(
END,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient
LEFT OUTER JOIN CV3PhysicalNoteDeclaration
on #tmpClient.ClientGUID = HT.ClientGUID
--AND #tmpClient.ChartGUID = HT.ChartGUID --UNCOMMENT WHEN MOVING TO LIVE
--AND #tmpClient.VisitGUID = HT.ClientVisitGUID --UNCOMMENT WHEN MOVING TO LIVE
AND HT.Status = 'Active'
AND HT.TypeCode = 'Height'
AND HT.Text is NOT NULL
AND HT.Text <> '0'
LEFT OUTER JOIN CV3PhysicalNoteDeclaration
on #tmpClient.ClientGUID = WT.ClientGUID
--AND #tmpClient.ChartGUID = WT.ChartGUID --UNCOMMENT WHEN MOVING TO LIVE
-- AND #tmpClient.VisitGUID = WT.ClientVisitGUID --UNCOMMENT WHEN MOVING TO LIVE
AND WT.Status = 'Active'
AND WT.TypeCode = 'Weight'
-- Compensate when no allergy information has been added to visit
INSERT INTO #ResultSet
SELECT c.*,
cast(c.ClientGUID as varchar(16)) as ClientGUIDVar,
NULL as OrderGUIDVar,
NULL as OrderTaskGUIDVar,
CodeNum = 1, /* When no allergy info has been added to patient chart, return row stating this */
'Allergy Info Not Entered' as Code,
NULL as Text,
NULL as SvrtyLevelCode,
NULL as AllergyTypeCode,
AllergyDeclarationType = 4,
CodeGUID = NULL,
OrderGUID = NULL,
OrderID = NULL,
OrderName = NULL,
OrderSignificantDtm = NULL,
OrderSummaryLine = NULL,
OrderIsConditional = NULL,
ConditionsText = NULL,
OrderRequestedBy = NULL,
OrderCatalogMasterItemGUID
TaskGUID = NULL,
CatalogItemTaskGUID = NULL,
TaskScheduleType = NULL,
TaskName = NULL,
TaskStartDtm = NULL,
TaskStopDtm = NULL,
TaskScheduleTypeSeqNum = NULL,
TaskReviewCategory = NULL,
TaskReviewCategorySeqNum = NULL,
OrderAdminInstructions = NULL,
OrderHeading = NULL,
TaskSeqNum = 0,
PrimaryParentOccurrenceGUI
Height = NULL,
Weight = NULL,
BSA = NULL,
OrderStart = NULL,
OrderStop = NULL
FROM #tmpClient c
where not exists (SELECT VisitGUID from #ResultSet rs where rs.VisitGUID = c.VisitGUID and rs.CodeNum = 1)
select *
FROM #ResultSet
order by
ClientDisplayName, VisitGUID, CodeNum, ScheduleTypeCode, TaskReviewCategory , TaskName
I don't see where you are selecting the date fields in the final select statement.
mlmcc
mlmcc
ASKER
The dates are in:
INSERT INTO #ResultSet
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
The dates are part of Client Temp table. The code above is ater INSERT INTO #tmpTasks .
INSERT INTO #ResultSet
SELECT #tmpClient.*,
cast(#tmpClient.ClientGUID
The dates are part of Client Temp table. The code above is ater INSERT INTO #tmpTasks .
But how does Crystal see them. It should only recognize the final result set you are selecting.
mlmcc
mlmcc
ASKER
Crystal sees it as String 30! When I run the SP in SQL Server, it returns as DateTime!!!
I know they are supposed to be temporary tables but have you made sure they are deleted after you run the procedure?
What types are te fields in CV3ActiveVisitList, since that is the source.
How are you connecting the report to the SP?
OLE, ODBC, other?
mlmcc
What types are te fields in CV3ActiveVisitList, since that is the source.
How are you connecting the report to the SP?
OLE, ODBC, other?
mlmcc
ASKER
The fields are defined as DateTime in tables; I am connecting via ODBC to Crystal.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much. I tried to use OLD but it did not work either!
So, I just created a brand new rpt and brought in the SP and it works just fine!!! Very odd....
So, I just created a brand new rpt and brought in the SP and it works just fine!!! Very odd....
One thing we didn't try on the original report if you have it, VERIFY DATABASE
Since a new report gets it correctly, I wonder if there was a change to the tables and Cystal will only pick it up if you force it with a VERIFY DATABASE
mlmcc
Since a new report gets it correctly, I wonder if there was a change to the tables and Cystal will only pick it up if you force it with a VERIFY DATABASE
mlmcc
mlmcc may have a point. Were those columns originally string, instead of datetime? If so, that could be the problem. I haven't had data types change often, so I don't think I've run into that specific situation, but I had a column that was made longer at one point, and all of my reports that used that column showed the old length until I did a "verify database". FWIW, almost all of my reports use stored procedures, so they were getting the column from a stored procedure, like your report (if that matters).
James
James
I was thinking more along the line that a field was added/deleted from the list of fields in the query and Crystal was using the position rather than the name of the field to determine which element to use. At one Crystal stored the position of the field rather than the name of the field in the query (thus the need for VERIFY DATABASE).
I have heard they changed the method to use the name but I don't know when they did that. CR8 used positions in the query list so even if you just changed the order it could cause problems.
mlmcc
I have heard they changed the method to use the name but I don't know when they did that. CR8 used positions in the query list so even if you just changed the order it could cause problems.
mlmcc
Yeah, I guess that could be a possibility too.
James
James
Which version of CR are you using?