Remove negative values from results

Transact-SQL
1

In my new role I have inherited the following SQL statement:

DECLARE @StartDate DATETIME = '2014-12-1'

SELECT 
 ROW_NUMBER() OVER(ORDER BY ORDERS.ORDERDATE, PatientProfile.searchname) as RowNumber
 , PatientProfile.searchname as PatientName
 , PatientProfile.Birthdate
 , ORDERS.ORDERDATE as OrderDate
 , DATEDIFF(D, ORDERS.ORDERDATE, ORDERS.DB_UPDATED_DATE) AS DaysToCurrentStatus
 , ORDERS.DESCRIPTION as ReferralType
 , CASE WHEN ORDERS.STATUS = 'U' Then 'Unsigned' 
	WHEN ORDERS.STATUS = 'H' Then 'Admin Hold'
	WHEN ORDERS.STATUS = 'S' Then 'InProcess'
	WHEN ORDERS.STATUS = 'C' Then 'Complete'
	WHEN ORDERS.STATUS = 'X' Then 'Cancelled'
	ELSE NULL END AS Status
 , df.ListName as OrderingProvider
 , ORDERS.DB_UPDATED_DATE as LastUpdated
 , ORDERS.CLINCOMMENTS as Comments
 , RIGHT(RTRIM(REPLACE(REPLACE(ORDERS.CLINCOMMENTS,CHAR(10),''),CHAR(13),'')), 18) as AppointmentDate /* Get rid of carriage returns, line breaks, and trailing spaces */
 , Coordinator = CASE
	WHEN ORDERS.ADMINCOMMENTS like 'Leticia%' then 'Leticia'
	WHEN ORDERS.ADMINCOMMENTS = 'Je%' then 'Jessica'
	WHEN ORDERS.ADMINCOMMENTS = 'Da%' then 'Dahlia'
	WHEN ORDERS.ADMINCOMMENTS = 'sandra' OR ORDERS.ADMINCOMMENTS = 'sc' THEN 'Sandra'
	ELSE NULL END
 , LOCREG.ABBREVNAME as Location
 INTO #TEMP
 FROM   (CentricityPS.dbo.PatientProfile PatientProfile 
 INNER JOIN CentricityPS.dbo.ORDERS ORDERS 
	ON PatientProfile.PId=ORDERS.PID) 
 INNER JOIN DoctorFacility df
	ON ORDERS.AUTHBYUSRID = DF.PVId
 INNER JOIN CentricityPS.dbo.LOCREG LOCREG 
	ON DF.LocationId=LOCREG.LOCID
 WHERE  ORDERS.ORDERDATE >= @StartDate
	AND ORDERS.ORDERTYPE='R' /*Referral*/
        
	AND ORDERS.XID= 1000000000000000000
	
	ORDER BY OrderingProvider
	
/* Cast only valid dates as AppointmentDates*/
SELECT RowNumber, CAST(AppointmentDate as DateTime) as AppointmentDate
INTO #Appts
FROM #TEMP
WHERE ISDATE(AppointmentDate) = 1

SELECT
	t.PatientName
	, t.Birthdate
	, t.OrderDate
	, t.ReferralType
	, t.Status
	, t.OrderingProvider
	, t.Comments
	, a.AppointmentDate as AppointmentDate
	, DATEDIFF(D, a.AppointmentDate, GETDATE()) as DaysSinceApptDate 
	, t.Coordinator
	, t.Location
		
FROM #TEMP t
	LEFT JOIN #Appts a
		ON a.RowNumber = t.RowNumber
	


DROP TABLE #TEMP, #Appts

The results I receive from this line ', DATEDIFF(D, a.AppointmentDate, GETDATE()) as DaysSinceApptDate' in the query returns negative values. I do not wish to have negative values in my results. Is there a way to re-write this statement without affecting the rest of the data?

2

add

where DATEDIFF(D, a.AppointmentDate, GETDATE()) >=0
3

Thank you for your help but that is not working. I get the message: Incorrect syntax near '>'

4

Then you typed it incorrectly. You need to post your revised query, e.g.

select 'yes'
from (values (getdate()-1)) v(mydate)
where DATEDIFF(D, mydate , GETDATE()) >=0

works fine

5 
DECLARE @StartDate DATETIME = '2014-12-1'

SELECT 
 ROW_NUMBER() OVER(ORDER BY ORDERS.ORDERDATE, PatientProfile.searchname) as RowNumber
 , PatientProfile.searchname as PatientName
 , PatientProfile.Birthdate
 , ORDERS.ORDERDATE as OrderDate
 , DATEDIFF(D, ORDERS.ORDERDATE, ORDERS.DB_UPDATED_DATE) AS DaysToCurrentStatus
 , ORDERS.DESCRIPTION as ReferralType
 , CASE WHEN ORDERS.STATUS = 'U' Then 'Unsigned' 
	WHEN ORDERS.STATUS = 'H' Then 'Admin Hold'
	WHEN ORDERS.STATUS = 'S' Then 'InProcess'
	WHEN ORDERS.STATUS = 'C' Then 'Complete'
	WHEN ORDERS.STATUS = 'X' Then 'Cancelled'
	ELSE NULL END AS Status
 , df.ListName as OrderingProvider
 , ORDERS.DB_UPDATED_DATE as LastUpdated
 , ORDERS.CLINCOMMENTS as Comments
 , RIGHT(RTRIM(REPLACE(REPLACE(ORDERS.CLINCOMMENTS,CHAR(10),''),CHAR(13),'')), 18) as AppointmentDate /* Get rid of carriage returns, line breaks, and trailing spaces */
 , Coordinator = CASE
	WHEN ORDERS.ADMINCOMMENTS like 'Leticia%' then 'Leticia'
	WHEN ORDERS.ADMINCOMMENTS = 'Je%' then 'Jessica'
	WHEN ORDERS.ADMINCOMMENTS = 'Da%' then 'Dahlia'
	WHEN ORDERS.ADMINCOMMENTS = 'sandra' OR ORDERS.ADMINCOMMENTS = 'sc' THEN 'Sandra'
	ELSE NULL END
 , LOCREG.ABBREVNAME as Location
 INTO #TEMP
 FROM   (CentricityPS.dbo.PatientProfile PatientProfile 
 INNER JOIN CentricityPS.dbo.ORDERS ORDERS 
	ON PatientProfile.PId=ORDERS.PID) 
 INNER JOIN DoctorFacility df
	ON ORDERS.AUTHBYUSRID = DF.PVId
 INNER JOIN CentricityPS.dbo.LOCREG LOCREG 
	ON DF.LocationId=LOCREG.LOCID
 WHERE  ORDERS.ORDERDATE >= @StartDate
	AND ORDERS.ORDERTYPE='R' /*Referral*/
        
	AND ORDERS.XID= 1000000000000000000
	
	ORDER BY OrderingProvider
	
/* Cast only valid dates as AppointmentDates*/
SELECT RowNumber, CAST(AppointmentDate as DateTime) as AppointmentDate
INTO #Appts
FROM #TEMP
WHERE ISDATE(AppointmentDate) = 1

SELECT
	t.PatientName
	, t.Birthdate
	, t.OrderDate
	, t.ReferralType
	, t.Status
	, t.OrderingProvider
	, t.Comments
	, a.AppointmentDate as AppointmentDate
	, DATEDIFF(D, a.AppointmentDate, GETDATE()) >=0
	 as DaysSinceApptDate 
	, t.Coordinator
	, t.Location
		
FROM #TEMP t
	LEFT JOIN #Appts a
		ON a.RowNumber = t.RowNumber
	


DROP TABLE #TEMP, #Appts

I typed it correctly - please see above.

6

You are using it in a select statement where as @gbritton suggested it in a WHERE clause

7

Thank you for your response. I apologize - I don't understand this query very well. I do not know where to add the Where statement. I think I tried that earlier and it did not work. I am quite sure it is user error. I am still very new at SQL queries.

8

After giving this a little thought what I really need that statement to do is change any negative values to 0. Otherwise, I believe with a where statement those results will be removed from the dataset. I need the rest of the data.

I apologize for any confusion. If I need to post the query again I will be happy to do so.

9 
...

case when a.AppoinmentDate <= getdate() then DATEDIFF(D, a.AppointmentDate, GETDATE())
        else 0 end  as DaysSinceApptDate 
...
10

Thank you again for your help. I have tried your example but the query is still showing an error:

DECLARE @StartDate DATETIME = '2014-12-1'

SELECT 
 ROW_NUMBER() OVER(ORDER BY ORDERS.ORDERDATE, PatientProfile.searchname) as RowNumber
 , PatientProfile.searchname as PatientName
 , PatientProfile.Birthdate
 , ORDERS.ORDERDATE as OrderDate
 , DATEDIFF(D, ORDERS.ORDERDATE, ORDERS.DB_UPDATED_DATE) AS DaysToCurrentStatus
 , ORDERS.DESCRIPTION as ReferralType
 , CASE WHEN ORDERS.STATUS = 'U' Then 'Unsigned' 
	WHEN ORDERS.STATUS = 'H' Then 'Admin Hold'
	WHEN ORDERS.STATUS = 'S' Then 'InProcess'
	WHEN ORDERS.STATUS = 'C' Then 'Complete'
	WHEN ORDERS.STATUS = 'X' Then 'Cancelled'
	ELSE NULL END AS Status
 , df.ListName as OrderingProvider
 , ORDERS.DB_UPDATED_DATE as LastUpdated
 , ORDERS.CLINCOMMENTS as Comments
 , RIGHT(RTRIM(REPLACE(REPLACE(ORDERS.CLINCOMMENTS,CHAR(10),''),CHAR(13),'')), 18) as AppointmentDate /* Get rid of carriage returns, line breaks, and trailing spaces */
 , Coordinator = CASE
	WHEN ORDERS.ADMINCOMMENTS like 'Leticia%' then 'Leticia'
	WHEN ORDERS.ADMINCOMMENTS = 'Je%' then 'Jessica'
	WHEN ORDERS.ADMINCOMMENTS = 'Da%' then 'Dahlia'
	WHEN ORDERS.ADMINCOMMENTS = 'sandra' OR ORDERS.ADMINCOMMENTS = 'sc' THEN 'Sandra'
	ELSE NULL END
 , LOCREG.ABBREVNAME as Location
 INTO #TEMP
 FROM   (CentricityPS.dbo.PatientProfile PatientProfile 
 INNER JOIN CentricityPS.dbo.ORDERS ORDERS 
	ON PatientProfile.PId=ORDERS.PID) 
 INNER JOIN DoctorFacility df
	ON ORDERS.AUTHBYUSRID = DF.PVId
 INNER JOIN CentricityPS.dbo.LOCREG LOCREG 
	ON DF.LocationId=LOCREG.LOCID
 WHERE  ORDERS.ORDERDATE >= @StartDate
	AND ORDERS.ORDERTYPE='R' /*Referral*/
        
	AND ORDERS.XID= 1000000000000000000
	
	ORDER BY OrderingProvider
	
/* Cast only valid dates as AppointmentDates*/
SELECT RowNumber, CAST(AppointmentDate as DateTime) as AppointmentDate
INTO #Appts
FROM #TEMP
WHERE ISDATE(AppointmentDate) = 1

SELECT
	t.PatientName
	, t.Birthdate
	, t.OrderDate
	, t.ReferralType
	, t.Status
	, t.OrderingProvider
	, t.Comments
	, a.AppointmentDate as AppointmentDate
	, (case when a.AppoinmentDate <= getdate() then  DATEDIFF(D, a.AppointmentDate, GETDATE()) else 0 end)  as DaysSinceApptDate 
	, t.Coordinator
	, t.Location
	

		
FROM #TEMP t
	LEFT JOIN #Appts a
		ON a.RowNumber = t.RowNumber
	


DROP TABLE #TEMP, #Appts

I have tried this exactly as you have it typed but receive errors. I have tried adding () but still receive errors.

11

What is the error message?

12

When I type in

 ,case when a.AppoinmentDate <= getdate() then  DATEDIFF(D, a.AppointmentDate, GETDATE()) else 0 end)  as DaysSinceApptDate

I receive an error Invalid column name AppointmentDate. If I add in parens I receive a CASE error message. I have tried adding in parens as various parts of the script but receive errors no matter what I do.

13

Aha, I see your column name:

is misspelled. My code uses the correct spelling (AppointmentDate) so causes an error. Just take my code and make sure the column names match your table

1 Like
14

You are right and you are awesome!!!!! Thank you so very much for your help. The script now executes beautifully! Thank you again.

15

I **hate** markdown :frowning: