SQL - One Table with Two Date Columns. Count and Join

Question

I have a table (vOKPI_Tickets) that has the following columns:

|CreationDate | CompletionDate|

I'd like to get a count on each of those columns, and group them by date. It should look something like this when complete:

| Date | Count-Created | Count-Completed |

I can get each of the counts individually, by doing something like this:

SELECT COUNT(TicketId)
FROM vOKPI_Tickets
GROUP BY CreationDate

and

SELECT COUNT(TicketId)
FROM vOKPI_Tickets
GROUP BY CreationDate

How can I combine the output into one table? I should also note that this will become a View.

Thanks in advance

Answer 1

Simple generic approach:

select
coalesce(crte.creationdate, cmpl.CompletionDate) as theDate,
crte.cnt as created,
cmpl.cnt as completed
from
(select creationdate, count (*) as cnt from vOKPI_Tickets where creationdate  is not null group by creationdate) crte
full join
(select CompletionDate, count (*) as cnt from vOKPI_Tickets where CompletionDate  is not null group by CompletionDate) cmpl
on crte.creationdate  = cmpl.CompletionDate