ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
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)
Who is Participating?
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)
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
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
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)
But you have "Entered IS NOT NULL", so I think the issue is that is a non-null value which does not meet the criteria. Are you wanting a row even if the count is zero?
In addition, use this to go between dates, noting direct comparisons exclude NULLs already.
Entered >= DATEADD(DD, -31, CONVERT(DATE, GETDATE()))
AND Entered <= CONVERT(DATE, GETDATE())
If you are not in SQL 2008, or higher, compatibility, you can accomplish the same thing CONVERT(DATE, ...) is doing with DATEDIFF:
e.g., DATEADD(DD, -31, DATEDIFF(DD, 0, GETDATE()))
If you want row even when there is nothing matching the date range, then use a conditional aggregate.
In addition, use this to go between dates, noting direct comparisons exclude NULLs already.
Entered >= DATEADD(DD, -31, CONVERT(DATE, GETDATE()))
AND Entered <= CONVERT(DATE, GETDATE())
If you are not in SQL 2008, or higher, compatibility, you can accomplish the same thing CONVERT(DATE, ...) is doing with DATEDIFF:
e.g., DATEADD(DD, -31, DATEDIFF(DD, 0, GETDATE()))
If you want row even when there is nothing matching the date range, then use a conditional aggregate.
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)
;
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"
Question has a verified solution.
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.
All Courses
From novice to tech pro — start learning today.
FROM dbo.HRXS
WHERE
NewRx=1
AND PharmID ='VA'
and TransType not in('Q','B')
and (CCType IS NULL OR CCType ='C')
and Entered >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 31, 0)
and Entered < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
GROUP BY CONVERT(char(10),Entered,1