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