Solved

Sql Query- Null values

Posted on 2014-01-08
9
503 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
Comment Utility
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
Comment Utility
Surendra,
Your code is what I have posted as question.Thanks..
0
 

Author Comment

by:Star79
Comment Utility
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
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 250 total points
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now