Solved

Question regarding combining three individual queries from Elite Enterprise using SQL

Posted on 2010-08-12
25
457 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 40

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:Larissa T
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
 
LVL 40

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:Larissa T
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 40

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 40

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 40

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 40

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 40

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 40

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 40

Accepted Solution

by:
Kyle Abrahams earned 500 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 40

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 40

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 40

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now