Board index » delphi » Clever sql statement

Clever sql statement


2004-08-11 03:59:02 AM
delphi122
Hi
For example let say I am using the DBDEMOS database.
I have the customers.db and the orders.db related m/d.
I want to have a select sql statement that will return
all the customers that didn't make any order since a given date.
Is it possible?
Thanks Sorin
 
 

Re:Clever sql statement

Thanks Bill but it doesn't give the expected result.
if a customer have all the orders date during 1994 and before
i expect to see this customer on the result set if i set the
:SOMEDATE = '01/01/1995' and it doesn't appear.
Again, the result expected is
all the customers whom all the orders are before somedate and
no more orders after this date.
Thanks again
Sorin
Quote
SELECT C.CUST_NO FROM CUSTOMER C
LEFT OUTER JOIN ORDERS O
O.ORDER_DATE IS NULL OR O.ORDER_DATE>:SOMEDATE


--
Bill (TeamB)
(TeamB cannot respond to questions received via email)
 

Re:Clever sql statement

Hershcu Sorin writes:
Quote
Again, the result expected is
all the customers whom all the orders are before somedate and
no more orders after this date.

>SELECT C.CUST_NO FROM CUSTOMER C
>LEFT OUTER JOIN ORDERS O
>O.ORDER_DATE IS NULL OR O.ORDER_DATE>:SOMEDATE
Can you not see where to change this? Just switch the test from>to <.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
Light is faster than sound, which is why some folks appear bright
before they speak.
 

Re:Clever sql statement

Yes I see and I try this. I change to < but still it is not return the
expected result.
Lets say there is a customer who have both orders_date, before and after
the the
date I assign to the somedate. This customer will come out on the result set
and this is not what I want.
Sorin
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Hershcu Sorin writes:
>Again, the result expected is
>all the customers whom all the orders are before somedate and
>no more orders after this date.
>
>>SELECT C.CUST_NO FROM CUSTOMER C
>>LEFT OUTER JOIN ORDERS O
>>O.ORDER_DATE IS NULL OR O.ORDER_DATE>:SOMEDATE

Can you not see where to change this? Just switch the test from>to <.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
Light is faster than sound, which is why some folks appear bright
before they speak.


 

Re:Clever sql statement

Hershcu Sorin writes:
Quote
Yes I see and I try this. I change to < but still it is not return the
expected result.
Lets say there is a customer who have both orders_date, before and
after the the
date I assign to the somedate. This customer will come out on the
result set and this is not what I want.
>>>SELECT C.CUST_NO FROM CUSTOMER C
>>>LEFT OUTER JOIN ORDERS O
>>>O.ORDER_DATE IS NULL OR O.ORDER_DATE>:SOMEDATE
Ok, sorry. In this case a subquery should work (and the query as quoted is
also incomplete anyway).
As written above - with a left join - the query would also return customers
with NO orders. Is this what you want?
SELECT C.CUST_NO FROM CUSTOMER C
WHERE NOT EXIST (
SELECT 1 FROM ORDERS O
WHERE O.CUST_NO = C.CUSTNO
AND O.ORDER_DATE>:SOMEDATE
)
If you do not want customers with no orders, add an INNER JOIN on orders
above the Where clause.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
It used to be that other people's achievements were considered an
inspiration, not a grievance.
 

Re:Clever sql statement

Hi!
Here is an alternative. The following query will return customers with no
orders after the specified date, and customers with no orders at all:
SELECT C.CUSTNO
FROM CUSTOMER C
LEFT JOIN ORDERS O ON O.CUSTNO = C.CUSTNO
GROUP BY C.CUSTNO
HAVING MAX(O.SALEDATE) < '01.01.1990'
OR MAX(O.SALEDATE) IS NULL
If you only want customers with orders before this date, use the following
query:
SELECT C.CUSTNO
FROM CUSTOMER C
JOIN ORDERS O ON O.CUSTNO = C.CUSTNO
GROUP BY C.CUSTNO
HAVING MAX(O.SALEDATE) < '01.01.1990'
Good luck!
--
SverreEH
Using Opera's revolutionary e-mail client: www.opera.com/m2/
 

Re:Clever sql statement

On Tue, 10 Aug 2004 21:59:02 +0200, Hershcu Sorin writes:
Quote
For example let say I am using the DBDEMOS database.
I have the customers.db and the orders.db related m/d.
Off the top of my head:
select *
from customers c
where c.cust_no not in
(select cust_no from orders o
where o.order_date>= :somedate )
--
Marc Rohloff [TeamB]
marc rohloff at myrealbox dot com