I have a column that needs to be created by subtracting two other columns. This final column would need to also appear in the select statement again in a CAST function. I can only do this by having the subtraction happen in a subquery, but the output isn’t correct. screenshot of output. Here is the subquery, what can I do to perfect this and get the correct output?
(SELECT se.sendid,(COUNT(se.eventdate)-COUNT(bo.eventdate)) AS Delivered FROM _Sent se Left Join _bounce bo ON bo.sendid = se.sendid WHERE se.listid = 518 AND CONVERT(date, se.eventdate) BETWEEN '2019-12-31' AND '2021-01-01' Group by se.sendid)