Your question, your audience. Choose who sees your identity—and your question—with question security.
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?
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)
CONVERT(CHAR(10),ISNULL(Entered,GETDATE()-60),101) between CONVERT(CHAR(10),GETDATE()-31,101) and CONVERT(CHAR(10),GETDATE(),101)
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 'VA'
, CONVERT(char(10),Entered,101)
/* if you have Entered values greater than today,
then add the second part to date filter; otherwise, it is unnecessary. */
, COUNT(CASE WHEN Entered >= DATEADD(DD, -31, CONVERT(DATE, GETDATE())) THEN RXNO END)
FROM HRXS
WHERE NewRx = 1
AND PharmID = 'VA'
AND TransType NOT IN ('Q','B')
AND (CCType IS NULL OR CCType = 'C')
GROUP BY CONVERT(char(10),Entered,101)
;
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join the community of 500,000 technology professionals and ask your questions.