Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.
Course Of The Month
3 days, 16 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)
Active 1 day ago
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)
;
Active 1 day ago
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"
Active 1 day ago
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
Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder. The folder can be on the local hard drive or on a network share. This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month3 days, 16 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.