Solved

Sql Query- Null values

Posted on 2014-01-08
9
512 Views
Last Modified: 2014-01-20
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)

Open in new window

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?
0
Comment
Question by:Star79
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39766197
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)

Open in new window

0
 

Author Comment

by:Star79
ID: 39766200
Surendra,
Your code is what I have posted as question.Thanks..
0
 

Author Comment

by:Star79
ID: 39766205
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,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)
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39766259
then why don't you just do this

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)

Open in new window

0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39766285
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) 

Open in new window

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 39766315
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.

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)
;

Open in new window

0
 

Author Comment

by:Star79
ID: 39766326
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.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39766396
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"
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39766716
SELECT 'VA', CONVERT(char(10),Entered,101), COUNT(RXNO)
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,101)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server: SNAPSHOT replication to include a newly added table. 2 29
SQL Help 27 45
Query Syntax 17 36
How to debug a store procedure in MS SQL 2008? 3 10
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question