New to sql: A better way to do this.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

New to sql: A better way to do this.

Author

Topic

boy_1der
Starting Member

6 Posts

Posted - 2009-03-27 : 10:48:36

I am new to writing sql queries, and I have been struggling to write this one. First here is what I am doing:

Writing a query to return a list of clients and the accounting periods that are missing in any of the tables.
Additional Information:
1. Useing tbllPractice from DB1 to use as my client listing.pk>idcontract
2. Use tbllAccPer to gather all months for the clients identified in step 1.pk>idcontract,actper
3. Use this to compare to all the tables (listed below).(ONLY SOME OF THE TABLES)

PK>idcontract,actper

tableClient
tableARAged
tableDOE

I currently have 9 joines. I know there has to be a more efficient way to do this. I just don't know what it is. Any help or feed back is greatly appreciated!!

sample code:

select 'client', tbllpractice.idcontract as 'practiceid', tbllAccPer.actper as 'batchactper',
tableclient.actper as 'clientactper', tableclient.idcontract as 'clientID'
from tbllpractice
inner join tbllAccPer on
tbllpractice.idcontract = tbllAccPer.idcontract
inner join tableclient on
tbllAccPer.idcontract = tableclient.idcontract
where tableclient.actper not in ( select distinct actper from tbllAccPer)

Union

select 'aged', tbllpractice.idcontract as 'practiceid', tbllAccPer.actper as 'batchactper',
tableARAged.actper as 'ageddosactper', tableARAged.idcontract as 'agedID'
from tbllpractice
inner join tbllAccPer on
tbllpractice.idcontract =tbllAccPer.idcontract
inner join tableARAged on
tbllAccPer.idcontract = tableARAged.idcontract
where tableARAged.actper not in ( select distinct actper from tbllAccPer)

Union

select 'Doe', tbllpractice.idcontract as 'practiceid', tbllAccPer.actper as 'batchactper',
tableDOE.actper as 'doeactper', tableDOE.idcontract as 'ageddoeID'
from tbllpractice
inner join tbllAccPer on
tbllpractice.idcontract =tbllAccPer.idcontract
inner join tableDOE on
tbllAccPer.idcontract = tableDOE.idcontract
where tableDOE.actper not in ( select distinct actper from tbllAccPer)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 11:02:49

Try use UNION ALL in favor of UNION.
It will speed things up becuase no duplicates will be detected.

E 12°55'05.63"
N 56°04'39.26"

dsindo
Starting Member

45 Posts

Posted - 2009-04-02 : 20:53:53

select type,
practiceid,
batchactper,
clientactper,
clientID

from
(
select 'client' as type,
tbllpractice.idcontract as 'practiceid',
tbllAccPer.actper as 'batchactper',
tableclient.actper as 'clientactper',
tableclient.idcontract as 'clientID'
from tbllpractice
inner join tbllAccPer on tbllpractice.idcontract = tbllAccPer.idcontract
inner join tableclient on tbllAccPer.idcontract = tableclient.idcontract

union all

select 'aged' as type ,
tbllpractice.idcontract as 'practiceid',
tbllAccPer.actper as 'batchactper',
tableARAged.actper as 'ageddosactper',
tableARAged.idcontract as 'agedID'
from tbllpractice
inner join tbllAccPer on tbllpractice.idcontract =tbllAccPer.idcontract
inner join tableARAged on tbllAccPer.idcontract = tableARAged.idcontract

union all

select 'Doe' as type ,
tbllpractice.idcontract as 'practiceid',
tbllAccPer.actper as 'batchactper',
tableDOE.actper as 'doeactper',
tableDOE.idcontract as 'ageddoeID'
from tbllpractice
inner join tbllAccPer on tbllpractice.idcontract =tbllAccPer.idcontract
inner join tableDOE on tbllAccPer.idcontract = tableDOE.idcontract

) a

where batchactper not in ( select distinct actper from tbllAccPer)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources