Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Prashant kulkarni
  Where is Prashant kulkarni?
 Mumbai
 India
 Prashant kulkarni
 Tags
Subject: RE: Help Required
Thread ID: 311147 Message ID: 311167 # Views: 59 # Ratings: 6
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Wednesday, June 29, 2011 12:08:36 PM         
   


> Dear Experts
>
> My Data is As Follows
>
> Table1
> ---------------
> Party_No Eff_DATE Cash Discount
> -------- ----------- --------------
> BS056 01/04/2009 4
> BS056 11/05/2009 4
> BS056 21/07/2010 3
>
>
>
> Table2
>
> Party_No Invoice No Invoice date Cash Discount
> -------- ------------ ----------------- ----------------------
> BS056 INV0001 05/04/2009
> BS056 INV0002 01/09/2010
>
>
> Now what i require is Invoice date of Table2 to be searched in between the eff date of Table 1 and cash discount should be updated in table 2
>
>
> Expected Result
>
> Party_No Invoice No Invoice date Cash Discount
> -------- ------------ ----------------- ----------------------
> BS056 INV0001 05/04/2009 4
> BS056 INV0002 01/09/2010 3
>
>
> Thanks in advance

UPDATE table2 ;
	SET cashDiscount = tmp.cashDiscount ;
	from ;
	(SELECT t1.party_no, ;
	t1.eff_Date as effDateStart, ; 
	MIN(t2.eff_Date) as effDateEnd, ;
	t1.CashDiscount ;
	from table1 t1 ;
	left join table1 t2 on ;
		t1.party_no == t2.party_no and ;
		t2.eff_date > t1.eff_date ;
	group by 1,2,4) tmp ;
  where table2.Party_no == tmp.party_no AND ;
    table2.invoiceDate >= tmp.EffDateStart AND ;
	( table2.invoiceDate < tmp.EffDateEnd OR tmp.EffDateEnd is null)

Cetin Basoz

.Net has got better.Think about moving - check my blog:
My Blog
Blog (mirror) - sounds to be down

ENTIRE THREAD

Help Required Posted by Prashant kulkarni @ 6/29/2011 8:18:56 AM
RE: Help Required Posted by Cetin Basoz @ 6/29/2011 12:08:36 PM