October 29, 2008 at 2:09 pm
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?
October 29, 2008 at 2:22 pm
Try ISNULL(S.Sales,0) in the SELECT.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 29, 2008 at 2:28 pm
I tried then also its not working.
October 29, 2008 at 2:45 pm
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'
October 29, 2008 at 2:56 pm
Its not working Ken.
October 29, 2008 at 3:14 pm
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
October 29, 2008 at 7:12 pm
Heh... try this...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2008 at 8:34 pm
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.
October 29, 2008 at 10:35 pm
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]
October 30, 2008 at 8:47 am
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.
October 30, 2008 at 9:51 am
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...
October 30, 2008 at 11:49 am
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.
October 30, 2008 at 12:51 pm
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'
October 30, 2008 at 1:10 pm
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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply