?
Solved

Question regarding combining three individual queries from Elite Enterprise using SQL

Posted on 2010-08-12
25
Medium Priority
?
533 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:sapgonzalez
  • 12
  • 11
  • 2
25 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33422256
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
 

Author Comment

by:sapgonzalez
ID: 33422657
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
 
LVL 11

Expert Comment

by:Lara F
ID: 33425000
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33428877
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
 

Author Comment

by:sapgonzalez
ID: 33429341
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
 

Author Comment

by:sapgonzalez
ID: 33429660
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
 
LVL 11

Expert Comment

by:Lara F
ID: 33429678
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33429685
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33429702
There needs to be a job # or some common thread.  Can you post your schema and your query as is?
0
 

Author Comment

by:sapgonzalez
ID: 33433020
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33433196
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
 

Author Comment

by:sapgonzalez
ID: 33453512
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33454924
remove order by from the inner queries (in the parentheses) and move to the outer query.
0
 

Author Comment

by:sapgonzalez
ID: 33458259
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33458523
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
 

Author Comment

by:sapgonzalez
ID: 33458921
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33464152
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
 

Author Comment

by:sapgonzalez
ID: 33464179
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
 

Author Comment

by:sapgonzalez
ID: 33464227
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
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 33466144
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
 

Author Comment

by:sapgonzalez
ID: 33469251
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33476552
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33500740
Do you need further assistance with this?
0
 

Author Comment

by:sapgonzalez
ID: 33501427
Not at this time. Chasing another issue.
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 33510451
Would recommend closing the question then and posting a new one if you have further issues.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question