I have three queries, I already created another query in which I combined the three queries.
The queries are:
with tbl1 as(
AS total from coaching z
JOIN tbl_calendar v
ON z.starttime BETWEEN v.datestart and v.dateend
JOIN roster a on z.coachEID=a.EID
AND v.month='1' and v.year='2017'
GROUP BY coacheid, coacheeeid, status, role),
SELECT y.role, y.eid, x.eid as coachee, IIF(y.eid=x.sme,1,2) AS target
FROM roster y
JOIN roster x ON (x.supervisor = y.eid OR x.sme = y.eid OR x.teamlead = y.eid) AND x.eid <> y.eid
AND x.employstatus = 'Active'
WHERE y.employstatus = 'active'
AND y.Role IN ('SME', 'Junior Team Lead','Team Lead', 'Shift Lead', 'Operations Lead', 'Quality Lead', 'Policy Lead')
AND y.location = 'manila'),
SELECT x.coachEID,x.CoacheeEID, sum (x.NoOfApproved) as exempt, x.month, x.year from exemptitems x
LEFT JOIN roster y on y.eid=x.Coacheid
WHERE x.month='1' and x.year='2017'
AND y.eid=x.coacheid and x.NoOfApproved is not NULL
GROUP BY x.CoachEID, x.CoacheeEID, x.Month, x.year)
select IIF(tbl2.role is NULL, tbl1.role,tbl2.role) as role,
IIF(tbl1.coachEID is NULL,tbl2.eid,tbl1.coachEID) as coacheid,
IIF(tbl1.coacheeEID is NULL, tbl2.coachee,tbl1.coacheeEID) as coacheeeid,
IIF(total is NULL,0,total) as total,
IIF(tbl2.target is NUll,total,tbl2.target) as ttarget,
IIF(tbl3.exempt is NULL,0,tbl3.exempt) as texemption,
IIF(tbl2.target is NULL,total,tbl2.target)-IIF(tbl3.exempt is NULL,0,tbl3.exempt) as ttotal
FULL join tbl2
on tbl1.coachEID=tbl2.eid and tbl1.coacheeEID=tbl2.coachee
left JOIN tbl3
'Ambiguous column name' means, you forgot to specify table name in front of the column. Choose one and put it there (