Help me with this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Help me with this query

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2013-06-05 : 08:46:12

I have two tables :
First one has Customer specification :

CUSTOMER
====================
ID - Name - city - tel - address

The second is the history of selling

SELL
======================
ID - CustomerID - Date

i want to write a query to show the customers which has no bought before special date, and show the last date of bought

I wrote this query but it shows the max(date)=null


 select  Customer.Name ,Customer.city ,Customer.tel ,Customer.address,max(Sell.Date) as 'date' from Sell right join Customer
 on Sell.CustomerID = Customer.ID 
group by Sell.CustomerID,Customer.Name,Customer.ID ,Customer.city ,Customer.tel,Customer.address
having max(Date)< '2013/03/01'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-05 : 09:09:00

You would see nulls if you had customers who did not buy anything at all. Is that the case, or do you see customers that you know bought something, but the result still shows null for the date?

Exir
Posting Yak Master

151 Posts

Posted - 2013-06-05 : 09:14:27

It shows the customers who has not bought since 2013/03/01 (having max(Date)< '2013/03/01' ) but shows the 'date' = NULL [max(Sell.Date) as 'date'] for all customers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 14:19:50

are you sure this is the full query? if its filtering on max(Date)< '2013/03/01' then max(Date) can never be NULL. comparison operators like <,> etc ignore NULL values under default ANSI_NULL setting in t-sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources