August 30, 2016 at 9:33 am
I am trying to display to date fields into one field. the highlighted portion below is how I am trying to display the. data.
if the patient had 2 procedures done I would like to display the 2 dates in the one date field.
it is rare, but can happen.
the first 2 highlighted parts are working fine. it is the 3rd highlighted area are the dates.
I was able to display the procedure and code this way, not sure why I cant do this with the date fields.
it is probably simple, but im stumped.
SELECT DISTINCT
T1.SessionID,
T1.Neonate#,
T1.[Total Neonates],
T1.[Last Name],
T1.[First Name],
T1.MRN,
T1.Account,
T1.[Delivery Date],
T1.[Delivery Type],
T1.Anesthesia,
T1.[Anesth. Code],
T1.Complications,
T1.Lacerations,
T1.[PACU Code],
--T1.PACU2,
T1.CDM,
T1.[Procedure],
T1.Proc_Charge,
T1.entrytime,
T2.[PROCEDURE] AS II_PROCEDURE,
case when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
then T1.[Procedure]
when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) <> CAST(T2.[TIME] AS DATE)
then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )
when T2.[PROCEDURE] is null and T1.[Procedure] is not null then T1.[Procedure]
when T2.[PROCEDURE] ='' and T1.[Procedure] <>'' then T1.[Procedure]
when T1.[PROCEDURE] is null and T2.[Procedure] is not null then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )
when T1.[PROCEDURE] ='' and T2.[Procedure]<>'' then (T2.[PROCEDURE]+'----'+ T2.CATEGORY )
--when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
--then T1.[PROCEDURE]+CHAR(10)+CHAR(13)+(T2.[PROCEDURE]+'----'+ T2.CATEGORY )
when t1.[PROCEDURE] like '%tubal%' and t2.[PROCEDURE] like '%tubal%'
and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE) then T1.[PROCEDURE]
when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
[highlight="#ffff11"]then T1.[PROCEDURE]+' -&- ' +(T2.[PROCEDURE]+'----'+ T2.CATEGORY )[/highlight]
else null
end as PROCEDURE_,
case when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
then CAST(T1.Proc_Charge AS VARCHAR(255))
when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) <> CAST(T2.[TIME] AS DATE)
then CAST(T2.CDM AS VARCHAR(255))
when T2.[PROCEDURE] is null and T1.[Procedure] is not null then CAST(T1.Proc_Charge AS VARCHAR(255))
when T2.[PROCEDURE] ='' and T1.[Procedure] <>'' then CAST(T1.Proc_Charge AS VARCHAR(255))
when T1.[PROCEDURE] is null and T2.[Procedure] is not null then CAST(T2.CDM AS VARCHAR(255))
when T1.[PROCEDURE] ='' and T2.[Procedure]<>'' then CAST(T2.CDM AS VARCHAR(255))
--when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
--then T1.[PROCEDURE]+CHAR(10)+CHAR(13)+(T2.[PROCEDURE]+'----'+ T2.CATEGORY )
when t1.[PROCEDURE] like '%tubal%' and t2.[PROCEDURE] like '%tubal%'
and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE) then CAST(T1.Proc_Charge AS VARCHAR(255))
when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
then [highlight="#ffff11"]CAST(T1.Proc_Charge AS VARCHAR(255))+' -&- ' +CAST(T2.CDM AS VARCHAR(255))[/highlight]
else null
end as CDM_,
case when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
then T1.entrytime
when t1.[PROCEDURE] = t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) <> CAST(T2.[TIME] AS DATE)
then T2.[TIME]
when T2.[PROCEDURE] is null and T1.[Procedure] is not null then T1.entrytime
when T2.[PROCEDURE] ='' and T1.[Procedure] <>'' then T1.entrytime
when T1.[PROCEDURE] is null and T2.[Procedure] is not null then CAST(T2.[TIME] AS DATE)
when T1.[PROCEDURE] ='' and T2.[Procedure]<>'' then CAST(T2.[TIME] AS DATE)
--when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
--then T1.[PROCEDURE]+CHAR(10)+CHAR(13)+(T2.[PROCEDURE]+'----'+ T2.CATEGORY )
when t1.[PROCEDURE] like '%tubal%' and t2.[PROCEDURE] like '%tubal%'
and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE) then T1.entrytime
when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
[highlight="#ffff11"]then CAST(T1.entrytime AS VARCHAR)+' -&- ' +CAST(T2.[TIME] AS VARCHAR)
[/highlight]else null
end as PROCEDURE_TIME,
--26952
T2.[TIME] AS II_DATE,
T2.CATEGORY AS II_CATEGORY,
T2.CDM AS II_CDM,
T2.PACU AS II_PACU,
T2.ANESTHESIA AS II_ANESTHESIA,
T2.ANESTH_CHARGE AS II_ANESTH_CHARGE,
T1.[Adhesive Barrier],
T1.Manufacturer,
T1.CatalogNumber,
T1.LotNumber,
T1.Qty,
T1.[Implant Charge],
T1.FacilityName
FROM
DBO.CHARGECAPTURE T1
LEFT OUTER JOIN
DBO.CHARGECAPTURE2 T2
ON T1.SESSIONID = T2.SESSIONID
WHERE T1.entrytime BETWEEN '01/01/2013' AND '08/20/2016'
OR T2.[TIME] BETWEEN '01/01/2013' AND '08/20/2016'
-- GETDATE()
--AND T1.MRN =
ORDER BY T1.SESSIONID
thanks for the help, this forum is a great learning site!!!
August 30, 2016 at 10:00 am
when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.[Delivery Date] AS DATE) = CAST(T2.[TIME] AS DATE)
then CAST(T1.entrytime AS VARCHAR)+' -&- ' +CAST(T2.[TIME] AS VARCHAR)
Looks like you're comparing T1.[Delivery Date] to T2.[TIME] in the WHEN but then trying to concatenate T1.entrytime and T2.[TIME]...are you sure T1.entrytime is NOT NULL?
_____________________________________________________________________
- Nate
August 30, 2016 at 10:29 am
thanks, but that is still generating the error message.
if I remove the --&-- and just have below, it is not always correct.
when t1.[PROCEDURE] <> t2.CATEGORY and CAST(T1.entrytime AS DATE) = CAST(T2.[TIME] AS DATE)
then T1.entrytime +' ' +T2.[TIME]
I get results once in a record 10/11/29 22:42
how can I display the 2 date values in one field?
August 30, 2016 at 11:13 am
Going back to your original statement, if both columns are NOT NULL and CONVERTed to strings then you should be able to concatenate them.
USE tempdb;
DECLARE @Tmp TABLE (Date1 DATETIME, Date2 DATETIME);
INSERT INTO @Tmp(Date1,Date2)
VALUES
(GETDATE(),DATEADD(HOUR,2,GETDATE()))
,(GETDATE(),NULL);
SELECTISNULL(CAST(Date1 AS VARCHAR(50)),'') + '-&-' + ISNULL(CAST(Date2 AS VARCHAR(50)),'')
FROM@Tmp;
_____________________________________________________________________
- Nate
August 30, 2016 at 12:01 pm
It's because of the return type of CASE.
A CASE expression's return data type will be the data type with the highest precedence of all the possible result expressions. See https://msdn.microsoft.com/en-us/library/ms181765.aspx#Anchor_2
Some of the result expressions are CAST as DATE, which seems to be the highest precedence data type in your potential result expressions. See https://msdn.microsoft.com/en-us/library/ms190309.aspx.
The problem then is that with the result of the CASE expression being of data type DATE, the concatenation of two dates is not a valid DATE.
If you really want to be able to concatenate two dates, then you'll need to make sure all result expressions are converted to varchar.
Cheers!
EDIT: Changed the link to the CASE documentation to go straight to the "Return Types" section.
August 30, 2016 at 1:15 pm
All results of a CASE expression must be of compatible data types and the data type with the highest precedence determines the final data type. You return a mix of DATETIME data and VARCHAR data in your CASE expression, and, since DATETIME has a higher precedence than VARCHAR, all of your VARCHAR expressions are converted to DATETIME including 'Aug 30 2016 --&-- Sep 9 2016'. Since that's not a valid date/time, it returns an error.
You need to make sure that you are only returning VARCHAR data in your CASE expression.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 30, 2016 at 1:37 pm
Once you get the data type precedence (stated above) figured out, you'll probably want to format both of those dates so they display in the same format. Whatever you do, don't use the new FORMAT function to do it because of known performance problems. You're better off to use DATEPART to build your string to return.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply