How to print null when no sales is present

Question

How to print null when no sales is present

Shree-903371

SSCommitted

Points: 1930
More actions
October 29, 2008 at 2:09 pm

#200617

I have Sales table
CustomerNumberSalesDate
1234$4.002008/08/03
4567$7.002008/08/09
I have Customer table
CustomerNumberChain
1234ABC
4567DEF
8901GHI
In my query I need if a CustomerNumber is entered that does not have sales for a date range, then it should print like ( for example 8901 as it does not have sales)
ChainCustomerNumberSales
GHI8901$0.00
SELECT
C.Chain,
C.CustomerNumber,
S.Sales
FROM tblSales S WITH (NOLOCK)
LEFT OUTER JOIN tblCustomer
ON C.CustomerNumber=S.CustomerNumber
WHERE S.Date BETWEEN '2008/08/01' AND '2008/08/10'
AND C.CustomerNumber='8901'
I tried with Left outer Join but the query gives me null value. Can anybody help me?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

Try ISNULL(S.Sales,0) in the SELECT.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Shree-903371 SSCommitted Points: 1930 More actions · Answer 2

Shree-903371

SSCommitted

Points: 1930

October 29, 2008 at 2:28 pm

#891481

I tried then also its not working.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 3

Try this.

SELECT

C.Chain,

C.CustomerNumber,

ISNULL(S.Sales,0) Sales

FROM tblSales S WITH (NOLOCK)

RIGHT OUTER JOIN tblCustomer C

ON C.CustomerNumber=S.CustomerNumber

WHERE ((S.Date BETWEEN '2008/08/01' AND '2008/08/10') OR S.Date Is NULL)

AND C.CustomerNumber='8901'

Shree-903371 SSCommitted Points: 1930 More actions · Answer 4

Shree-903371

SSCommitted

Points: 1930

October 29, 2008 at 2:56 pm

#891495

Its not working Ken.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 5

Here is the sample code to create the test tables. I seem to be getting the results as expected. Try running the entire code sample and see if this is the results you would like.

Create Table #tblSales

(CustomerNumber int, Sales int, Date datetime)

Create Table #tblCustomer

(CustomerNumber int, Chain varchar(50))

Insert into #tblCustomer Values (1234, 'ABC')

Insert into #tblCustomer Values (4567, 'DEF')

Insert into #tblCustomer Values (8901, 'GHI')

INSERT INTO #tblSales Values(1234, 4, '2008/08/03')

INSERT INTO #tblSales Values(4567, 7, '2008/08/09')

SELECT

C.Chain,

C.CustomerNumber,

ISNULL(S.Sales,0) Sales

FROM #tblSales S WITH (NOLOCK)

RIGHT OUTER JOIN #tblCustomer C

ON C.CustomerNumber=S.CustomerNumber

WHERE ((S.Date BETWEEN '2008/08/01' AND '2008/08/10') OR S.Date Is NULL)

AND C.CustomerNumber='8901'

Drop Table #tblSales

Drop Table #tblCustomer

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Heh... try this...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Snidow SSCoach Points: 16108 More actions · Answer 7

Shree, I tried a direct copy and paste of your code, commented out the WHERE clause, and the result was as follows:

ABC12344

DEF45677

GHI89010

When you say "print", what exactly do you mean? Are you talking about the results in QA, or a report, or maybe an app?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Garadin One Orange Chip Points: 29613 More actions · Answer 8

The reason it worked when you commented out the WHERE clause is because the WHERE clause is the whole problem in the first place. Putting criteria in your WHERE that references your LEFT JOINED table prettymuch negates the entire purpose of left joining. You will no longer get all rows from customer, you will only get the rows in which Sales matches the criteria you put on the query.

Ken took this into account with his query by adding the OR. His should work fine if you copied it correctly. I've placed your original script, Ken's (slightly modified to use my table variables) and my own method in a script below for testing purposes.

Shree, try either Ken's or Mine, paying very close attention to the details, and as previously requested, please post questions in the manner described in the link Jeff gave you (or in my signature) in the future. (You will have to modify mine slightly to replace your original table names)

[font="Courier New"]DECLARE @Sales TABLE(

CustomerNumber VARCHAR(10),

Sales money,

Date DATETIME)

INSERT INTO @Sales(CustomerNumber, Sales, Date)

SELECT 1234,4.00,'2008/08/03' UNION ALL

SELECT 4567,7.00,'2008/08/09'

DECLARE @Cust TABLE(

CustomerNumber VARCHAR(10),

Chain VARCHAR(10))

INSERT INTO @Cust(CustomerNumber, Chain)

SELECT 1234,'ABC' UNION ALL

SELECT 4567,'DEF' UNION ALL

SELECT 8901,'GHI'

------- Your original Method

SELECT

C.Chain,

C.CustomerNumber,

S.Sales

FROM @Cust C

LEFT OUTER JOIN @Sales S ON C.CustomerNumber=S.CustomerNumber

WHERE S.Date BETWEEN '2008/08/01' AND '2008/08/10'

------- Ken's Method --------

SELECT

C.Chain,

C.CustomerNumber,

ISNULL(S.Sales,0) Sales

FROM @Sales S

RIGHT OUTER JOIN @Cust C

ON C.CustomerNumber=S.CustomerNumber

WHERE ((S.Date BETWEEN '2008/08/01' AND '2008/08/10') OR S.Date IS NULL)

------- My Method -----------

SELECT

C.Chain,

C.CustomerNumber,

ISNULL(S.Sales,0)

FROM @Cust C

LEFT OUTER JOIN @Sales S ON C.CustomerNumber=S.CustomerNumber AND S.Date BETWEEN '2008/08/01' AND '2008/08/10'[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Shree-903371 SSCommitted Points: 1930 More actions · Answer 9

Create Table #tblSales

(CustomerNumber int, Productnumber int, Sales int, Date datetime)

Create Table #tblCustomer

(CustomerNumber int, Chain varchar(50))

Create Table #tblProduct

(ProductNumber int)

Insert into #tblCustomer Values (1234, 'ABC')

Insert into #tblCustomer Values (4567, 'DEF')

Insert into #tblCustomer Values (8901, 'GHI')

INSERT INTO #tblSales Values(1234,1, 4, '2008/08/03')

INSERT INTO #tblSales Values(4567,2, 7, '2008/08/09')

Insert into #tblCustomer Values (1)

Insert into #tblCustomer Values (2)

In the morning, my User asked me she wants to enter just CustomerNumber or CustomerNumber and ProductNumber.

When we enter only CustomerNumber ( for example 8901 as it does not have sales)

Chain CustomerNumber ProductNumber Sales

GHI 8901 Null or 0 $0.00

SELECT

C.Chain,

C.CustomerNumber,

ISNULL(S.Sales,0)

FROM #tblcustomer C

LEFT OUTER JOIN #tblSales S ON C.CustomerNumber=S.CustomerNumber

AND S.deliveryDate BETWEEN '2008/08/01' AND '2008/08/10'

LEFT OUTER JOIN #tblProduct P ON P.ProductNumber = S.ProductNumber

where c.customernumber='8901'

This code works only when CustomerNumber is entered.

Its not working when both CustomerNumber and ProductNumber is entered like

SELECT

C.Chain,

C.CustomerNumber,

ISNULL(P.ProductNumber,0),

ISNULL(S.Sales,0)

FROM #tblcustomer C

LEFT OUTER JOIN #tblSales S ON C.CustomerNumber=S.CustomerNumber

AND S.deliveryDate BETWEEN '2008/08/01' AND '2008/08/10'

LEFT OUTER JOIN #tblProduct P ON P.ProductNumber = S.ProductNumber

where c.customernumber='1234' and P.ProductNumber='2'

I need it in

Chain CustomerNumber ProductNumber Sales

GHI 1234 2 $0.00

Can you suggest me how to do it? Thanks a lot for helping me.

Garadin One Orange Chip Points: 29613 More actions · Answer 10

Putting criteria in your WHERE that references your LEFT JOINED table prettymuch negates the entire purpose of left joining.

Heh, you just came full circle on this one and did it again. The Products table is LEFT JOINED...

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Shree-903371 SSCommitted Points: 1930 More actions · Answer 11

Yes Garadin, I understood what you meant but I couldn’t see an other way to do it. I was thinking to create fake data and do it. But it is very time consuming. So that’s why I thought you might help me.

Garadin One Orange Chip Points: 29613 More actions · Answer 12

Yes Garadin, I understood what you meant but I couldn’t see an other way to do it. I was thinking to create fake data and do it. But it is very time consuming. So that’s why I thought you might help me.

I don't think you did. My point was after we fixed what broke it in the first place (by moving the criteria from the WHERE clause into the join), you added another table in exactly the same fashion and put criteria back into the WHERE clause which broke it in exactly the same fashion that we just fixed.

Moving the criteria back out of the WHERE clause and into the join should fix it. I don't think you want P.ProductID with that join structure, you want S.ProductID. (Assuming you want to see how many of productid 2 was purchased by customer 1234)

DECLARE @ProductNumber int

SET @ProductNumber = 2

SELECT

C.Chain,

C.CustomerNumber,

COALESCE(S.ProductNumber,@ProductNumber)

ISNULL(S.Sales,0)

FROM #tblcustomer C

LEFT OUTER JOIN #tblSales S ON C.CustomerNumber=S.CustomerNumber AND S.Date BETWEEN '2008/08/01' AND '2008/08/10' AND S.ProductNumber=@ProductNumber

where c.customernumber='1234'

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Shree-903371 SSCommitted Points: 1930 More actions · Answer 13

Thanks Garadin, but I need to join on Product table as I have to get few other fields like Description etc from it. For simplicity I didn’t add other fields in the table.