Help with sql null issue - 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

General SQL Server Forums

New to SQL Server Programming

Help with sql null issue

Author

Topic

Newb2SQL
Starting Member

1 Post

Posted - 2011-10-28 : 15:30:09

Table customer:
customer # Customer Cus_Address
#A CusA addressA
#B CusB addressB
#C CusC addressc
#D CusD..etc addressD..etc

Table visits:
Customer # Date visit
#A sept 1, 3, 4,5,6,7
#B july 1, 3, 4, sept 3
#C aug 1, 3, 2,
#D Sept 1, 4, 2

i wan to return every customer whether they have visited in sept. If they have not visited in sept, i still want to return them
but with a null value.

Results desired:

Customer # Date Visit
#A sept 1, 3, 4,5,6,7
#B sept 3
#C NULL
#D Sept 1, 4, 2

Here is what i have.. and it pull everything i want expect i am not getting my NULL customers.

SELECT
CUSTOMER #,
DATE VISITS

FROM
CUSTOMER INNER JOIN VISITS ON (CUSTOMER.CUSTOMER # = VISITS.CUSTOMER#)

WHERE
DATE VISITS BETWEEN '09/01/11' AND '09/30/11'

Here is what i also tired:

SELECT
CUSTOMER #,
DATE VISIT,
CASE
WHEN COUNT(DATE VISIT) = 0 THEN 'NULL'
ELSE DATE VISIT
END AS VISIT

FROM
CUSTOMER INNER JOIN VISITS ON (CUSTOMER.CUSTOMER # = VISITS.CUSTOMER#)

WHERE
DATE VISITS BETWEEN '09/01/11' AND '09/30/11'

Thanks for the help in advance!!

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 16:27:54

This....does not work



CREATE TABLE myTable99(CUST_NO varchar(20), CUST varchar(20), ADDR varchar(20))
CREATE TABLE myTable98(CUST_NO varchar(20), DATE varchar(20), VISIT varchar(20))
GO

INSERT INTO myTable99(CUST_NO, CUST, ADDR)
SELECT '#A', 'CusA', 'addressA' UNION ALL
SELECT '#B', 'CusB', 'addressB' UNION ALL
SELECT '#C', 'CusC', 'addressc' UNION ALL
SELECT '#D', 'CusD', 'addressD'
GO

INSERT INTO myTable98(CUST_NO, DATE, VISIT)
SELECT '#A', 'sept', '1, 3, 4,5,6,7' UNION ALL
SELECT '#B', 'july', '1, 3, 4, sept 3' UNION ALL
SELECT '#C', 'aug',  '1, 3, 2'  UNION ALL
SELECT '#D', 'Sept', '1, 4, 2'
GO

SELECT *
  FROM (SELECT DISTINCT [DATE] FROM myTable98) AS myDate
FULL JOIN myTable98 b ON b.[DATE] = myDate.[Date]
LEFT JOIN myTable99 c ON c.CUST_NO = b.CUST_NO
GO

DROP TABLE myTable99, myTable98
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 00:51:34

why are you storing visit dates like this? this will make searching based on dates difficult. Read about normalisation and first normal form.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources