Question regarding combining three individual queries from Elite Enterprise using SQL

Have a question regarding combining three individual queries from Elite Enterprise using SQL where all three individual queries may not have information for same key field. However, we want to include all results from all three queries.
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.

Kyle AbrahamsSenior .Net DeveloperCommented:
you can use union to do the different queries as long as the columns are the same.  

select <col 1> <col 2>, null from <table 1> union
select <null>, <col 2>, <col 3> from <table 2>
0
sapgonzalezAuthor Commented:
that is the problem, the columns are not the same.

Query 1
name, billable hours, billable dollars

Query 2
name, invoiced hours, invoiced dollars

Query 3
name, collected hours, collected dollars

Sample Results
Query 1
ABC, 55.00, 5500.00
DEF, 0.00, 0.00

Query 2
ABC, 25.00, 2500.00
DEF, 2.00, 200.00

Query 3
ABC, 0.00, 0.00
DEF, 10.00, 1000.00
GHI, 1.00, 100.00

Desired result:
return rows with data for:
ABC
DEF
GHI


0
Lara FEACommented:
as long as columns are the same type  you are OK. Union will use column names from the very first select
select c1, sum(c2), sum(c3)
from (
           select 'ABC' c1, 55.00 c2, 5500.00 c3
union select 'DEF', 0.00, 0.00
union select 'ABC', 25.00, 2500.00
union select 'DEF', 2.00, 200.00
union select 'ABC', 0.00, 0.00
union select 'DEF', 10.00, 1000.00
union select 'GHI', 1.00, 100.00
) t group by  c1
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kyle AbrahamsSenior .Net DeveloperCommented:
I would add a fourth column type:

select name, 'B' as type_of_hours, billable_hours, billable_dollars from billable union
select name, 'I',  invoiced hours, invoiced_dollars from  invoices union
select name 'C', collected_hours, collected_dollars from collected


you may want to add dates and order by transaction date or some other related piece of information (Job #? or something)  There's got to be a reason all this data relates.
0
sapgonzalezAuthor Commented:
We don’t want the result where the Union uses the column name of the first select only (that’s what we have now). We would like the Union to use the column name of each additional select also.  Also, my previous query example was misleading, the queries will not return results for an originator if the values for hours or dollars = 0.00

Query 1
originator name, billable hours, billable dollars

Query 2
originator name, invoiced hours, invoiced dollars

Query 3
originator name, collected hours, collected dollars

Sample Results
Query 1 - billable hours/dollars
Jones, 55.00, 5500.00


Query 2 - invoiced hours/dollars
Jones, 25.00, 2500.00
Smith, 2.00, 200.00

Query 3 - collected hours/dollars
Smith, 10.00, 1000.00
Brown, 1.00, 100.00


Jones - this originator has billable hrs and dollars, invoiced hrs and invoiced dollars, but does not have collected hrs and collected dollars.

Smith - this originator does not have billable hrs, billable dollars, but has invoiced hrs, invoiced dollars, collected hrs and collected dollars.

Brown - this originator does not have billable hrs, billable dollars, invoiced hrs, invoiced dollars, but has collected hrs and collected dollars.

Desired result:
return rows with data for:

Jones, 55.00,5500.00,25.00,2500.00,0.00,0.00
Smith, 0.00,0.00,2.00,200.00,10.00,1000.00
Brown,0.00,0.00,0.00,0.00,1.00,100.00
0
sapgonzalezAuthor Commented:
report results:

Orig Name  Billable Hrs  Billable $    Inv Hrs      Inv $    Collected Hrs.  Collected $
Jones            55.00         5500.00      25.00    2500.00       0.00               0.00
Smith              0.00               0.00        2.00      200.00     10.00          1000.00
Brown             0.00               0.00        0.00          0.00       1.00            100.00  
0
Lara FEACommented:
Write 1-st query in union with list of all columns  you need in output,
If column is not present in the table you query first, just write 0.0 as <columnName>


select name , 0.0 as colHours, bilHours, bilDolar, 0 colDolar
   from table1
union select name, colHours, 0 as bilH, 0 bilDol, colDol from table2
0
Kyle AbrahamsSenior .Net DeveloperCommented:
that would be joins then.

select b.name, b.hours, b.dollars, i.hours, i.dollars, c.hours, c.dollars
from billable b
left outer join collected c on b.name = c.name
left outer join invoiced i on i.name = c.name

you will get nulls for anything that isn't collected.  But you can change that by  replacing c.hours with
isnull(c.hours,0) as collected_hours and so on.

0
Kyle AbrahamsSenior .Net DeveloperCommented:
There needs to be a job # or some common thread.  Can you post your schema and your query as is?
0
sapgonzalezAuthor Commented:
Here are the three queries.

---Q1
SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol,
            timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '04/01/2010' AND periodt.peendt <= '04/30/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
Order By       timekeep.tklast


---Q2
SELECT     SUM(timecard.tbillhrs * (mtkper.mtkpercnt/100)) AS GMTDBilledHrs, SUM(timecard.tbilldol * (mtkper.mtkpercnt/100))AS GMTDBilledDol,
            timekeep.tklast,timekeep.tkfirst
FROM         timecard INNER JOIN
                      mtkper ON timecard.tmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON timecard.twoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (timecard.tbilldt >= '04/01/2010' AND timecard.tbilldt <= '04/30/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
Order By       timekeep.tklast

---Q3
SELECT       SUM(ledgfee.lfhrs * (mtkper.mtkpercnt/100)) AS GMTDColHrs, SUM(ledgfee.lfamnt * (mtkper.mtkpercnt/100)) AS GMTDColDol,
            timekeep.tklast,timekeep.tkfirst
FROM         ledger INNER JOIN
                      mtkper ON ledger.lmatter = mtkper.mtkmatter INNER JOIN
                      ledgfee ON ledger.lindex = ledgfee.lindex INNER JOIN
                      periodt ON ledgfee.lwoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE     (ledgfee.lfcode = 'PAY') AND (ledgfee.lftradat >= '04/01/2010' AND ledgfee.lftradat <= '04/30/2010')
        AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
Order By       timekeep.tklast
0
Kyle AbrahamsSenior .Net DeveloperCommented:
try the following:

select A.tklast + ',' + a.tkfirst as origname, A.GMTDBillableHrs, A.GMTDBillableDol, B.GMTDBilledHrs, B.GMTDBilledDol, C.GMTDColHrs, C.GMTDColDol from
(SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol,
            timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '04/01/2010' AND periodt.peendt <= '04/30/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
Order By       timekeep.tklast
) A,
FULL OUTER JOIN
(SELECT     SUM(timecard.tbillhrs * (mtkper.mtkpercnt/100)) AS GMTDBilledHrs, SUM(timecard.tbilldol * (mtkper.mtkpercnt/100))AS GMTDBilledDol,
            timekeep.tklast,timekeep.tkfirst
FROM         timecard INNER JOIN
                      mtkper ON timecard.tmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON timecard.twoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (timecard.tbilldt >= '04/01/2010' AND timecard.tbilldt <= '04/30/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
Order By       timekeep.tklast) B ON
 A.tklast = B.tklast and A.TkFirst = B.TKfirst
FULL OUTER JOIN
(SELECT       SUM(ledgfee.lfhrs * (mtkper.mtkpercnt/100)) AS GMTDColHrs, SUM(ledgfee.lfamnt * (mtkper.mtkpercnt/100)) AS GMTDColDol,
            timekeep.tklast,timekeep.tkfirst
FROM         ledger INNER JOIN
                      mtkper ON ledger.lmatter = mtkper.mtkmatter INNER JOIN
                      ledgfee ON ledger.lindex = ledgfee.lindex INNER JOIN
                      periodt ON ledgfee.lwoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE     (ledgfee.lfcode = 'PAY') AND (ledgfee.lftradat >= '04/01/2010' AND ledgfee.lftradat <= '04/30/2010')
        AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
Order By       timekeep.tklast) C ON
B.tklast = C.tklast and B.TkFirst = C.tkfirst and A.TKLAST = c.TKLAST and a.tkfirst = c.tkfirst
0
sapgonzalezAuthor Commented:
Thanks for the response. I got the following errors

Server: Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'Order'.
Server: Msg 156, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'Order'.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
remove order by from the inner queries (in the parentheses) and move to the outer query.
0
sapgonzalezAuthor Commented:
Thanks ged325...made the changes you suggest and got the following results

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'a' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'timekeep' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'c' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'a' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'c' does not match with a table name or alias name used in the query.


The query became

select A.tklast + ',' + a.tkfirst as origname, A.GMTDBillableHrs, A.GMTDBillableDol, B.GMTDBilledHrs, B.GMTDBilledDol, C.GMTDColHrs, C.GMTDColDol from
(SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol,
            timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '04/01/2010' AND periodt.peendt <= '04/30/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) A
FULL OUTER JOIN
(SELECT     SUM(timecard.tbillhrs * (mtkper.mtkpercnt/100)) AS GMTDBilledHrs, SUM(timecard.tbilldol * (mtkper.mtkpercnt/100))AS GMTDBilledDol,
            timekeep.tklast,timekeep.tkfirst
FROM         timecard INNER JOIN
                      mtkper ON timecard.tmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON timecard.twoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (timecard.tbilldt >= '04/01/2010' AND timecard.tbilldt <= '04/30/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) B ON
 A.tklast = B.tklast and A.TkFirst = B.TKfirst
FULL OUTER JOIN
(SELECT       SUM(ledgfee.lfhrs * (mtkper.mtkpercnt/100)) AS GMTDColHrs, SUM(ledgfee.lfamnt * (mtkper.mtkpercnt/100)) AS GMTDColDol,
            timekeep.tklast,timekeep.tkfirst
FROM         ledger INNER JOIN
                      mtkper ON ledger.lmatter = mtkper.mtkmatter INNER JOIN
                      ledgfee ON ledger.lindex = ledgfee.lindex INNER JOIN
                      periodt ON ledgfee.lwoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE     (ledgfee.lfcode = 'PAY') AND (ledgfee.lftradat >= '04/01/2010' AND ledgfee.lftradat <= '04/30/2010')
        AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) C ON
B.tklast = C.tklast and B.TkFirst = C.tkfirst and A.TKLAST = c.TKLAST and a.tkfirst = c.tkfirst
Order By       timekeep.tklast
0
Kyle AbrahamsSenior .Net DeveloperCommented:
let's work with one table and get that query down:

Please let me know if the following runs?    Apologies but I don't have your schema so I can't test first here before posting.

select A.tklast + ',' + A.tkfirst as origname, A.GMTDBillableHrs, A.GMTDBillableDol, from
(SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol,
            timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '04/01/2010' AND periodt.peendt <= '04/30/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) A
0
sapgonzalezAuthor Commented:
I remove the coma before the "from" but yes this works and returns the desired result

select A.tklast + ',' + A.tkfirst as origname, A.GMTDBillableHrs, A.GMTDBillableDol from
(SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol,
                  timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '07/01/2010' AND periodt.peendt <= '07/31/2010')
                  AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) A
0
Kyle AbrahamsSenior .Net DeveloperCommented:
great, so let's move on to 2 tables.


select A.tklast + ',' + a.tkfirst as origname, A.GMTDBillableHrs, A.GMTDBillableDol, B.GMTDBilledHrs, B.GMTDBilledDol
(SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol, 
            timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '04/01/2010' AND periodt.peendt <= '04/30/2010') 
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) A
FULL OUTER JOIN 
(SELECT     SUM(timecard.tbillhrs * (mtkper.mtkpercnt/100)) AS GMTDBilledHrs, SUM(timecard.tbilldol * (mtkper.mtkpercnt/100))AS GMTDBilledDol,
            timekeep.tklast,timekeep.tkfirst
FROM         timecard INNER JOIN
                      mtkper ON timecard.tmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON timecard.twoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (timecard.tbilldt >= '04/01/2010' AND timecard.tbilldt <= '04/30/2010') 
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) B ON
 A.tklast = B.tklast and A.TkFirst = B.TKfirst

Open in new window

0
sapgonzalezAuthor Commented:
Thanks agina. I get the following errors

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near 'B'.
0
sapgonzalezAuthor Commented:
Correct the issues...the following SQL works fine

select A.tklast + ',' + A.tkfirst as origname, A.GMTDBillableHrs, A.GMTDBillableDol, B.GMTDBilledHrs, B.GMTDBilledDol from
(SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol,
            timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '07/01/2010' AND periodt.peendt <= '07/31/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) A
FULL OUTER JOIN
(SELECT     SUM(timecard.tbillhrs * (mtkper.mtkpercnt/100)) AS GMTDBilledHrs, SUM(timecard.tbilldol * (mtkper.mtkpercnt/100))AS GMTDBilledDol,
            timekeep.tklast,timekeep.tkfirst
FROM         timecard INNER JOIN
                      mtkper ON timecard.tmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON timecard.twoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (timecard.tbilldt >= '07/01/2010' AND timecard.tbilldt <= '07/31/2010')
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) B ON
 A.tklast = B.tklast and A.tkfirst = B.tkfirst
0
Kyle AbrahamsSenior .Net DeveloperCommented:
now applying the last . . . thanks for bearing with and correcting the syntax errors.  Like I said impossible to run on my side.
select A.tklast + ',' + A.tkfirst as origname, A.GMTDBillableHrs, A.GMTDBillableDol, B.GMTDBilledHrs, B.GMTDBilledDol, C.GMTDColHrs, C.GMTDColDol from
(SELECT            SUM(mattimhs.mthrwkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableHrs, SUM(mattimhs.mtdowkdw *  (mtkper.mtkpercnt/100)) AS GMTDBillableDol, 
            timekeep.tklast,timekeep.tkfirst
FROM              mattimhs INNER JOIN
                      mtkper ON mattimhs.mtmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON mattimhs.mtper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (periodt.pebedt >= '07/01/2010' AND periodt.peendt <= '07/31/2010') 
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) A
FULL OUTER JOIN 
(SELECT     SUM(timecard.tbillhrs * (mtkper.mtkpercnt/100)) AS GMTDBilledHrs, SUM(timecard.tbilldol * (mtkper.mtkpercnt/100))AS GMTDBilledDol,
            timekeep.tklast,timekeep.tkfirst
FROM         timecard INNER JOIN
                      mtkper ON timecard.tmatter = mtkper.mtkmatter INNER JOIN
                      periodt ON timecard.twoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit
WHERE           (timecard.tbilldt >= '07/01/2010' AND timecard.tbilldt <= '07/31/2010') 
            AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
) B ON
 A.tklast = B.tklast and A.tkfirst = B.tkfirst
FULL OUTER JOIN 
(SELECT       SUM(ledgfee.lfhrs * (mtkper.mtkpercnt/100)) AS GMTDColHrs, SUM(ledgfee.lfamnt * (mtkper.mtkpercnt/100)) AS GMTDColDol,
            timekeep.tklast,timekeep.tkfirst
FROM         ledger INNER JOIN
                      mtkper ON ledger.lmatter = mtkper.mtkmatter INNER JOIN
                      ledgfee ON ledger.lindex = ledgfee.lindex INNER JOIN
                      periodt ON ledgfee.lwoper = periodt.pe INNER JOIN
                      timekeep ON mtkper.mtktk = timekeep.tkinit 
WHERE     (ledgfee.lfcode = 'PAY') AND (ledgfee.lftradat >= '04/01/2010' AND ledgfee.lftradat <= '04/30/2010')
        AND (mtkper.mtkdate1 <= periodt.pebedt AND mtkper.mtkdate2 >= periodt.pebedt)
GROUP BY        timekeep.tklast,timekeep.tkfirst
Order By       timekeep.tklast)  C ON
B.tklast = C.tklast and B.tkfirst = C.tkfirst  AND
A.tklast = C.tklast and A.tkfirst = C.tkfirst

Open in new window

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:
The query executed successfully but I am getting NULLs for some of the origname. I am looking into that now. I will let you know what I find. Thanks.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
note in the main query you can use isnull(<column>, <other value>) as <value> to get rid of nulls.  

So if nothing was coming from c:
isnull(C.GMTDColDol, 0) as GMTDColDol

Glad to hear the query is working.  Please let me know your findings and if you can provide a sample of the results I can also take a look.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Do you need further assistance with this?
0
sapgonzalezAuthor Commented:
Not at this time. Chasing another issue.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Would recommend closing the question then and posting a new one if you have further issues.
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
Microsoft SQL Server

From novice to tech pro — start learning today.