Querying SQL Server to obtain sum total using CTE and joins

Question

I have a below query that I am trying since yesterday with some 33 records of Employee with employeeId on various conditions:

With CTE 
(
    select EmployeeId, and other colums with joins and conditions.
)

Now I want to join this query to obtain sum of invoices of each employee from below tables, table1 and table2.

table1 has employeeid so as my CTE has employeeid I can join it with table1

With CTE 
(
     select EmployeeId, and other colums with joins and conditions.
) 
select *, table1.invoiceId 
from CTE 
left join table1 on table1.employeeid = CTE.employeeId
left join table2 on table2.invoiceid = table1.invoiceid 
groupby

But my table1 only have invoices and for each such invoice there are amount spend in other table i.e table2. table2 has column "amount" that I need to sum up depending upon invoiceid.

For more clarity I am writing the table structure or output as below. I am trying like above but they are not showing correct results

Assume CTE has

Emplyeeid empName Empaddress empcode
1          john    America    121
2          sandy    America   122

Now table1 has

InvoiceId EmployeeId  RecordId PAyeeid
1           1            223     202
2           1            222     212
3           1            121     378
4           2            229     987
5           2            345     333

table2 has the coulmm amount that we need for each invoice of epmloyee

now table2

InvLine     Invoiceid    Amount
1             1            30
2             1            30
3             1            20
4             2            10
5             2            10
6             2            10

The output should be as per employe john has two invoices in table1 ie with Id 1 and 2, and for 1 and 2 invoiceds there are amounts that need to be add up

Emplyeeid  empName Empaddress empcode  Amount
1           john    America    121      80

score 3 · Answer 1 · edited May 03 '16 at 07:54

    With CTE 
    (
     select EmployeeId, and other colums with joins and conditions.
    ) 

    With CTE1 
    (
     select EmployeeId,table1.invoiceid  
     from cte 
     left join table1 on table1.employeeid=CTE.employeeId
    ) 

    select sum(amount), cte1.employeeId from CTE1 
    left join table2 on table2.invoiceid = cte1.invoiceid 
    group by cte1.employeeId

but you can join the table1 in the first cte itself. There is no need to go for second cte if the first cte is simple one.

Querying SQL Server to obtain sum total using CTE and joins

1 Answers1