Star79
asked on
Sql Query- Null values
Hello I have the below query
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)
Somewhere in the data there is null value for the entered field and so its returning no data,but if I comment the where condition on the entered field its returning data.How to handle this?
ASKER
Surendra,
Your code is what I have posted as question.Thanks..
Your code is what I have posted as question.Thanks..
ASKER
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,1 01),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,1 01) between CONVERT(char(10),GETDATE() -31,101) and CONVERT(char(10),GETDATE() ,101)
GROUP BY CONVERT(char(10),Entered,1 01)
SELECT 'VA', CONVERT(char(10),Entered,1
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,1
GROUP BY CONVERT(char(10),Entered,1
then why don't you just do this
add the below code inplace of that between
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window