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


> >
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) ;
> > ORDER BY 4 ;
> > INTO CURSOR Tc_AL_1

> >
> > Above query giving those accounts name whose any transaction passed during given period from all transaction tables.
> >
> > Problem:
> > How can i add the following Accounts also if not exsist in above list & also check that if sales,purchase,sales return,purchase return transactions occured in this period then add in the list
> >
> > 'Sales' = '47200SLS'
> > 'Purchase' = '47000CHS'
> > 'Sales Return' = '47002CHS'
> > 'Purchase Return' = '47202SLS'
>
> 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

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