Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
COURSE of the MONTH
13 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sql Query- Null values
Posted on 2014-01-08
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)
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2- +2
9 Comments
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)
Active 5 days ago
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)
Active 1 day ago
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.
Active 5 days ago
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"
Active today
SELECT 'VA', CONVERT(char(10),Entered,1
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
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
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Introduction:
When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database.
Problems start when you try to combine that with some "user input" pass…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month13 days, 19 hours left to enroll
801 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.