Sql Query- Null values

try the below code

SELECT 'VA',  CONVERT(char(10),Entered,101),COUNT(RXNO) FROM  HRXS
 WHERE 
-- AND  
 isnull(NewRx,'')=1 
 AND isnull(PharmID,'') ='VA' and isnull(TransType,'') not in('Q','B') and  (CCType IS NULL OR CCType ='C') 
 and Entered is not null and  CONVERT(char(10),Entered,101) between CONVERT(char(10),GETDATE()-31,101) and CONVERT(char(10),GETDATE(),101)
 
GROUP BY CONVERT(char(10),Entered,101)

Select allOpen in new window

Surendra,
Your code is what I have posted as question.Thanks..

When I comment the convert function on Entered it brings data..as entered somewhere has null data in it


SELECT 'VA',  CONVERT(char(10),Entered,101),COUNT(RXNO) FROM  HRXS
 WHERE
-- AND  
 NewRx=1
 AND PharmID ='VA' and TransType not in('Q','B') and  (CCType IS NULL OR CCType ='C')
 and Entered is not null --and  CONVERT(char(10),Entered,101) between CONVERT(char(10),GETDATE()-31,101) and CONVERT(char(10),GETDATE(),101)
 
GROUP BY CONVERT(char(10),Entered,101)

then why don't you just do this

add the below code inplace of that between

CONVERT(CHAR(10),ISNULL(Entered,GETDATE()-60),101) between CONVERT(CHAR(10),GETDATE()-31,101) and CONVERT(CHAR(10),GETDATE(),101)

Select allOpen in new window

Your where-clause is saying "don't show me nulls on the Entered field"... I suspect you want either a null or a range-test:

SELECT	'VA'
,	CONVERT(char(10),Entered,101)
,	COUNT(RXNO) 
FROM	HRXS
WHERE	NewRx=1 
AND	PharmID ='VA'
AND	TransType not in ('Q','B') 
AND 	(	CCType IS NULL
	OR	CCType ='C'
	) 
AND	(	Entered IS NULL
	OR	CONVERT(char(10),Entered,101)
			between	CONVERT(char(10),GETDATE()-31,101)
			and	CONVERT(char(10),GETDATE(),101)
	)
GROUP
BY	CONVERT(char(10),Entered,101) 

Select allOpen in new window

Surendra you code did not help.
John,
I want data that has no null on the entered field..and its has to be in the date range.

Perhaps your data is too old, and that is the reason why you are not getting the records you are expecting.  Your where-clause is saying "only show me records where the Entered date is within the last 31 days"