How do I query information from Elite tables

I am trying to build a report that reports on data from nine different tables. The tables are mtkper, matter, timekeep,title, mattimhs, periodt, ledgfee, ledger and timecard.  I have two version of the same query using a "where" and "having" clause. Neither query returns the expected results and takes hours to complete. Any help will be greatly appreciated.

SELECT     top 10 SUM(mattimhs.mthrwkdw) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw) AS GMTDBillableDol, SUM(timecard.tbillhrs) AS GMTDBilledHrs, SUM(timecard.tbilldol)
                      AS GMTDBilledDol, SUM(ledgfee.lfhrs) AS GMTDColHrs, SUM(ledgfee.lfamnt) AS GMTDColDol, timekeep.tklast, timekeep.tkfirst, title.tititle
FROM         ledgfee INNER JOIN
                      mtkper INNER JOIN
                      matter ON mtkper.mtkmatter = matter.mmatter INNER JOIN
                      timecard ON matter.mmatter = timecard.tmatter INNER JOIN
                      mattimhs ON matter.mmatter = mattimhs.mtmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe AND timecard.twoper = periodt.pe ON ledgfee.lwoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit INNER JOIN
                      title ON timekeep.tktitle = title.tititle INNER JOIN
                      ledger ON ledgfee.lindex = ledger.lindex
Where      (ledgfee.lfcode = 'PAY') AND (ledgfee.lftradat >= CONVERT(DATETIME, '2010-07-01 00:00:00', 102) AND ledgfee.lftradat <= CONVERT(DATETIME, '2010-07-30 00:00:00',
                      102)) OR (timecard.tbilldt >= CONVERT(DATETIME, '2010-07-01 00:00:00', 102) AND timecard.tbilldt <= CONVERT(DATETIME, '2010-07-30 00:00:00', 102)) OR
                      (periodt.pebedt >= CONVERT(DATETIME, '2010-07-01 00:00:00', 102)) AND (periodt.peendt <= CONVERT(DATETIME, '2010-07-30 00:00:00', 102)) AND
                      (mtkper.mtkdate1 <= periodt.pebedt) AND (mtkper.mtkdate2 >= periodt.peendt)
GROUP BY timekeep.tklast, timekeep.tkfirst, title.tititle
ORDER BY timekeep.tklast, timekeep.tkfirst, title.tititle



SELECT     SUM(mattimhs.mthrwkdw) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw) AS GMTDBillableDol, SUM(timecard.tbillhrs) AS GMTDBilledHrs, SUM(timecard.tbilldol)
                      AS GMTDBilledDol, SUM(ledgfee.lfhrs) AS GMTDColHrs, SUM(ledgfee.lfamnt) AS GMTDColDol, timekeep.tklast, timekeep.tkfirst, title.tititle
FROM         ledgfee INNER JOIN
                      mtkper INNER JOIN
                      matter ON mtkper.mtkmatter = matter.mmatter INNER JOIN
                      timecard ON matter.mmatter  = timecard.tmatter INNER JOIN
                      mattimhs ON matter.mmatter  = mattimhs.mtmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe AND timecard.twoper = periodt.pe ON ledgfee.lwoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit INNER JOIN
                      title ON timekeep.tktitle = title.tititle INNER JOIN
                      ledger ON ledgfee.lindex = ledger.lindex
GROUP BY timekeep.tklast, timekeep.tkfirst, title.tititle, ledgfee.lfcode, ledgfee.lftradat, timecard.tbilldt, periodt.pebedt, periodt.peendt, mtkper.mtkdate1, mtkper.mtkdate2,
                      mtkper.mtktk
HAVING      (ledgfee.lfcode = 'PAY') AND (ledgfee.lftradat >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102) AND ledgfee.lftradat <= CONVERT(DATETIME, '2010-04-30 00:00:00',
                      102)) OR (timecard.tbilldt >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102) AND timecard.tbilldt <= CONVERT(DATETIME, '2010-04-30 00:00:00', 102)) OR
                      (periodt.pebedt >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102)) AND (periodt.peendt <= CONVERT(DATETIME, '2010-04-30 00:00:00', 102)) AND
                      (mtkper.mtkdate1 <= periodt.pebedt) AND (mtkper.mtkdate2 >= periodt.peendt)
ORDER BY timekeep.tklast, timekeep.tkfirst, title.tititle
sapgonzalezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jrm213jrm213Commented:
Hi, your first inner join is not joining on anything. Add the join between parameters between ledgfee and mtkper

0
sapgonzalezAuthor Commented:
There is no relationship between those two tables.
0
jrm213jrm213Commented:
If there is no relationship between those tables then you can't pull data from them without them joining every row to every row, which is probably why it is taking hours to complete and you are not getting results you expect.

For example

if I have a table with 10 rows called tableA and a table with 10 rows called tableB, it will return 100 rows, not 20, because it is joining the first table to the second table for each row of the first

TableA
A
B
C
D
E
F
G
H
I
J

TableB
0
1
2
3
4
5
6
7
8
9


Results
A 0
A 1
A 2
...
B 0
B 1
B 2
...

etc.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sapgonzalezAuthor Commented:
thats correct. I think the following line should be moved up under Ledgfee INNER JOIN

ledger ON ledgfee.lindex = ledger.lindex
0
jrm213jrm213Commented:
is mtkper related to any table in the list?
0
sapgonzalezAuthor Commented:
yes matter and timekeep.
0
jrm213jrm213Commented:
Does this include all your tables?

FROM mtkper
INNER JOIN  matter ON mtkper.mtkmatter = matter.mmatter
INNER JOIN  timecard ON matter.mmatter  = timecard.tmatter
INNER JOIN  mattimhs ON matter.mmatter  = mattimhs.mtmatter
INNER JOIN periodt ON mattimhs.mtper = periodt.pe AND timecard.twoper = periodt.pe
INNER JOIN ledgfee ON ledgfee.lwoper = periodt.pe
INNER JOIN timekeep ON mtkper.mtktk = timekeep.tkinit
INNER JOIN title ON timekeep.tktitle = title.tititle
INNER JOIN ledger ON ledgfee.lindex = ledger.lindex

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sapgonzalezAuthor Commented:
yes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.