I have 2 queries, A and B, which I currently use independently from each other. They both return an employee ID and some metric values. I now want to use the employee IDs returned from result set A in query B.
A's query is structured like this:
select employee_id from employee where employee_team = 1 and employee_role = 1
B's query is structured like this:
declare @tester int = 123450 --plug in employee ID
select employee_id
,employee_name
,sum(case
when notes.note_author!=employee_id and logs.log_date<@today
then 1 else 0 end) as metric
from notes
inner join note_to_log_bridge as br on notes.note_id=br.note_id
inner join logs on br.log_id=logs.log_id
inner join employee on employee_id=@Tester
If I want to get B's metrics for 5 employees, I have to run query B 5 times, changing the @Tester variable each time. I'd like to instead find some way of automating that, so that I get the metrics for query B for every employee_id in result set A.
I tried stored result set A as a CTE and using a while loop to run through query B:
declare @line=1
with cte (employee_id) as <query_a>
while (@line<=count(cte.employee_id))
begin <query b>...
I never finished this query because I discovered that while cannot follow the creation of a CTE.
I tried using a table variable:
declare @set_a (employee_id int)
insert into @set_a <query a>
but when I try to use @set_a in query B, I get a message saying that I need to declare the scalar variable @set_a.
I tried using a temp table and got a "could not be bound" error.
I am out of ideas. Am I approaching this problem in anything resembling the right direction? Is this even possible?
Thank you!
Use Cursor ?
If I want to get B's metrics for 5 employees, I have to run query B 5 times, changing the @Tester variable each time.
DECLARE @empid int;
DECLARE vend_cursor CURSOR
FOR select employee_id from employee where employee_team = 1 and employee_role = 1
OPEN vend_cursor
FETCH NEXT FROM vend_cursor into @empid;
WHILE @@FETCH_STATUS = 0
BEGIN
// your query with @mpid
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE vend_cursor
DEALLOCATE vend_cursor