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

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: