Hello, I want to do a query simi

[earme]

Hello,

I want to do a query similar to the following:

SELECT CUSTFILE.CustNo, COUNT(MASTER.PONo) AS Expr1
 FROM dbo.CUSTFILE LEFT OUTER JOIN MASTER ON CUSTFILE.CustNo = MASTER.CustNo
 WHERE (MASTER.OrderDate > date1) OR (MASTER.OrderDate < date2)
GROUP BY CUSTFILE.CustNo
HAVING (COUNT(MASTER.PONo) = 0)

So, the point of this is to find all customers who didn't place orders in the timeframe.

Problem 1: The query doesn't return any records because we're already getting rid of everyone who hasn't placed an order. So, this means I need to break this up into 2 queries. One to find all the customers that placed orders and another to remove those from the list of all customers.

Problem 2: I want to be able to define what date1 and date2 are at the time the query is run. So, this means I need to do a stored procedure.

So, the query that returns all customers that have not placed an order in the timeframe needs to call the stored procedure, or I need to have 1 stored procedure call another.

I'm stuck, I don't know how to make this work the way I want it to or even if it's possible.

ANY ideas at all?
Thank you,
Evie

 

[bperry]

Would you like to get the query working first, and then deal with getting it into a SP.

I am thinking one of these might do it:
----------------------------------
Select c.CustNo
FROM dbo.CUSTFILE c
WHERE NOT EXISTS
(Select * from MASTER m
WHERE m.CustNo = c.CustNo
and m.OrderDate BETWEEN date1 and date2)
-----------------------------
SELECT CUSTFILE.CustNo,
COUNT(MASTER.PONo) AS Expr1
FROM dbo.CUSTFILE
LEFT OUTER JOIN MASTER
ON CUSTFILE.CustNo = MASTER.CustNo
AND
(
MASTER.OrderDate IS NULL OR
(MASTER.OrderDate BETWEEN date1 and date2)
)
GROUP BY CUSTFILE.CustNo
HAVING (COUNT(MASTER.PONo) = 0)
----------------------------

 

[essa2000]

Dear Evie ;

I hope it will work for you ;

Create Procedure stp_CUST_GetProspectCustomers
@P_DateFrom datetime ,
@P_DateFrom datetime
As

SET NOCOUNT ON
BEGIN

SELECT CustNo From dbo.CUSTFILE WHERE
CustNo NOT IN (
SELECT DISTINCT CUSTFILE.CustNo
FROM dbo.CUSTFILE LEFT OUTER JOIN MASTER
ON CUSTFILE.CustNo = MASTER.CustNo
WHERE Convert(varchar(10), MASTER.OrderDate , 101) BETWEEN Convert(varchar(10), @P_DateFrom , 101)
AND Convert(varchar(10) , @P_DateTo ,101)
)

END

Regards,
essa2000

 

[bperry]

earme:
I have a feeling that essa2000's suggested Select query (in the SP) will not return you the records you wanted. It's kind of a long explanation, but basically: putting the date restriction on the right-hand table (MASTER) into the WHERE clause effectively turns your Left Join into an Inner Join.

If so, you may want to try one of the two that I suggested (but i didn't test.) The NOT EXISTS query is probably the most straight-forward; but the Left Join may perform better with your data.

(Notice in my Left Join suggestion, the date criteria is in the ON clause, not the Where clause.)

I've been looking into this Left Join stuff lately, so I'd be interesetd in hearing what works for you.

thanx
bperry

 

[earme]

bperry,

I ended up using the first of your examples. Once I had the first one working I got curious and tried the second one, it seems to also work. And, still being curious, I also atempted essa2000's suggestion and as you said, it did not work. I don't quite understand why it didn't work, but it returned customers that had and hadn't placed orders. It was very odd.
At any rate I have a sp that will work now, which is my first step.

Thank you!
Evie

 

[bperry]

Okay, that's great.
Those are tricky queries; I get tripped up by them all the time too.

bp