new to sql_need help with query

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

General SQL Server Forums

New to SQL Server Programming

new to sql_need help with query

Author

Topic

boy_1der
Starting Member

6 Posts

Posted - 2009-03-27 : 00:04:09

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 03:31:40

this seems like an assignment. can we see what you tried yet?

boy_1der
Starting Member

6 Posts

Posted - 2009-03-28 : 11:51:15

Yes it is, but the code above is mine. I was just wondering if there was an easie/ more efficient way to write it. This works, but runs really slow. Maybe i'll google temp table and see if that is a better query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:30:41

select tmp.category, 
tbllpractice.idcontract as 'practiceid', tbllAccPer.actper as 'batchactper', 
tmp.actper as 'catactper', tmp.idcontract as 'catID'
from tbllpractice
inner join tbllAccPer on 
tbllpractice.idcontract = tbllAccPer.idcontract
inner join 
(select actper,idcontract,'client' as category from tableclient 
union 
select actper,idcontract,'aged' from tableARAged 
union 
select actper,idcontract,'Doe' from tableDOE 
)tmp
on
tbllAccPer.idcontract = tmp.idcontract
where not exists( select 1 from tbllAccPer where actper  =tmp.actper)

boy_1der
Starting Member

6 Posts

Posted - 2009-04-02 : 11:13:29

thanks alot visakh16. That's it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources