Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Patrick Danso
  Where is Patrick Danso?
 Accra
 Ghana
 Patrick Danso
 Tags
Subject: RE: Only One Record
Thread ID: 345581 Message ID: 345594 # Views: 44 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Thursday, May 24, 2012 6:08:29 PM         
   


> Hi,
> I want to select from a payments table all members who’s payments in the table are less than 3times for a particular type of payment (for example “Tax on Books”).
>
>
> Thank you



CREATE CURSOR Payments (pay_id i UNIQUE, member_id i, pay_date D, amount N(7,2), tax_code I)
INSERT INTO Payments VALUES (1, 21,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (2, 23,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (3, 21,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (4, 22,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (5, 23,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (6, 23,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (7, 21,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (8, 24,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (9, 21,DATE()-70, 12.56,1)
INSERT INTO Payments VALUES (10, 24,DATE()-70, 12.56,1)

CREATE CURSOR Taxes (tax_code i UNIQUE, descr C(20))
INSERT INTO Taxes VALUES (1, 'Tax on books')
INSERT INTO Taxes VALUES (2, 'Tax on bicycles') 

SELECT member_id,COUNT(*),SUM(amount) FROM Payments ;
JOIN Taxes ON Payments.tax_code=Taxes.tax_code ;
WHERE Taxes.descr LIKE 'Tax on books' GROUP BY member_id ;
 HAVING COUNT(*) <3

CREATE CURSOR Members (member_id I UNIQUE, first_name C(6), last_name C(20), address C(10), post_code i)
INSERT INTO Members  VALUES (21, 'John','Smith', 'H Street 4', 12345)
 INSERT INTO Members  VALUES (22, 'Mary','Smith', 'A Street 89', 12399)
INSERT INTO Members  VALUES (23, 'Bob','Brown', 'B Street 34', 12320)
INSERT INTO Members  VALUES (24, 'Jacquie','Black', 'C Street 4', 12300)

SELECT DISTINCT M1.* FROM Members M1 JOIN Payments P1 ;
 ON M1.member_id= P1.member_id ;
 WHERE 3 > (SELECT COUNT(*) FROM Payments P2;
 WHERE P1.member_id = P2.member_id )


-Anders

ENTIRE THREAD

Only One Record Posted by Patrick Danso @ 5/24/2012 4:28:58 PM
RE: Only One Record Posted by Anders Altberg @ 5/24/2012 6:08:29 PM
RE: Only One Record Posted by Patrick Danso @ 5/24/2012 6:26:07 PM