lundi 2 mai 2016

Quering Sql server to obtain sum total using CTE and joins

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 a coloumn "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

Aucun commentaire:

Enregistrer un commentaire