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: 248717 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, December 22, 2009 2:53:36 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) ;
> > 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
>
>
> Thanks
>
> By the help of this query it is adding all 4 accounts but if sale found in sale table then it has to add Sale A/c in the list if no sale found during given period than there is no need to add SALE account in list

NOT IN will check in other result if not found only then it will add otherwise not.

If you can post some sample data using cursors it will be easy to test.

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