List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:January 5 2006 6:52pm
Subject:Re: SELECT help.
View as plain text  
Richard,

 >Can someone help me write a query to tell me the customer numbers
 >(C_NO) of those who've had more than 4 transactions but none in
 >the last 6 months?

Something like this?

  SELECT
    c_no,
    COUNT(c_no) AS cnt
  FROM transactions_table
  WHERE NOT EXISTS (
    SELECT c_no
    FROM transaction_table
    WHERE DATEDIFF(NOW(),date)<=182
  )
  GROUP BY c_no
  HAVING cnt > 4;

PB

-----

Richard Reina wrote:

>Can someone help me write a query to tell me the  customer numbers (C_NO) of those
> who've had more than 4 transactions  but none in the last 6 months?
>  
>  |   transactions_table                             |
>  | ID    |    C_NO    |    DATE       |   AMOUT|
>  |2901| 387            |"2003-10-09"|        23.00|
>  
>  
>  Obviously my table has many more entries.
>  
>  Thank you for any help.
>  
>  Sincerely,
>  
>  Richard Reina
>   
>  
>
>
>A people that values its privileges above its principles soon loses both.
> -Dwight D. Eisenhower.
>  
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006
Thread
SELECT help.Richard Reina5 Jan
  • Re: SELECT help.Rhino5 Jan
    • Re: SELECT help.Richard Reina5 Jan
    • Re: SELECT help.SGreen5 Jan
      • Re: SELECT help.Richard Reina7 Jan
  • Re: SELECT help.Michael Stassen5 Jan
  • Re: SELECT help.James Harvard5 Jan
  • Re: SELECT help.Peter Brawley5 Jan