Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: M. Akram Bhatti
  Where is M. Akram Bhatti?
 Riyadh
 Saudi Arabia
 M. Akram Bhatti
 To: Khurram Tahir
  Where is Khurram Tahir?
 Karachi
 Pakistan
 Khurram Tahir
 Tags
Subject: RE: Sql Query for Getting Accounts list
Thread ID: 248600 Message ID: 248711 # Views: 1 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, December 22, 2009 1:47:22 PM         
   


> >
> > Give some more details from which table[s] you want to get these accounts?
> >
> > Regards,
> > akram
>
> Accbal2 - Accounts Table
>
> Name---------,Code
> Accbal2.Accna c(30),Accbal2.Acccd c(8)
>
> Above 4 A/cs one by one added automatically in above list if Sale,Purchase,Sale return,Purchase return invoices found respectively during given period

ldFdt = CTOD('19/12/2009')
ldTdt = CTOD('19/12/2009')

*!*	Tc_AL = Those Accounts choosed whom transaction passed during period

Select Distinct Dracd As acccd,Drana As Accna,SPACE(1) As Prnt,SUBSTR(Dracd,6,3)+Drana As Acccd6Accna From Gjour2 Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Cracd As acccd,Crana As Accna,SPACE(1) As Prnt,SUBSTR(Cracd,6,3)+Crana As Acccd6Accna From Gjour2 Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Salgh 	Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Purgh 	Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Salgrh	Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Purgrh	Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
SELECT Acccd, Accna, "" AS Prnt, "" AS Acccd6Accna FROM AccBal2 WHERE INLIST(Acccd, '47200SLS','47000CHS','47002CHS','47202SLS') AND ;
Acccd NOT IN (SELECT t1.acccd FROM ( ;
Select Distinct Dracd As acccd,Drana As Accna,SPACE(1) As Prnt,SUBSTR(Dracd,6,3)+Drana As Acccd6Accna From Gjour2 Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Cracd As acccd,Crana As Accna,SPACE(1) As Prnt,SUBSTR(Cracd,6,3)+Crana As Acccd6Accna From Gjour2 Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Salgh 	Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Purgh 	Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Salgrh	Where BETWEEN(Date, ldFdt, ldTdt) ;
Union ;
Select Distinct Acccd,Accna,SPACE(1) As Prnt,SUBSTR(Acccd,6,3)+Accna As Acccd6Accna From Purgrh	Where BETWEEN(Date, ldFdt, ldTdt)) t1) ;
INTO CURSOR Tc_AL_1 ORDER BY 4


Regards,
akram

ENTIRE THREAD

Sql Query for Getting Accounts list Posted by Khurram Tahir @ 12/21/2009 2:15:12 PM
RE: Sql Query for Getting Accounts list Posted by M. Akram Bhatti @ 12/21/2009 2:23:04 PM
RE: Sql Query for Getting Accounts list Posted by Khurram Tahir @ 12/22/2009 1:30:28 PM
RE: Sql Query for Getting Accounts list Posted by M. Akram Bhatti @ 12/22/2009 1:47:22 PM
RE: Sql Query for Getting Accounts list Posted by Khurram Tahir @ 12/22/2009 2:49:31 PM
RE: Sql Query for Getting Accounts list Posted by M. Akram Bhatti @ 12/22/2009 2:53:36 PM