What to use in place of union in above query i wrote or more optimize query then my given query without union and union all

Question

I am counting the birthdays , sales , order in all 12 months from customers table in SQL server like these

In Customers table birth_date ,sale_date, order_date are columns of the table

           select 1 as ranking,'Birthdays' as Type,[MONTH],TOTAL
              from ( select DATENAME(month, birth_date)   AS [MONTH],count(*) TOTAL
                     from customers 
                     group by DATENAME(month, birth_date)                                            
                   )x

    union

            select 2 as ranking,'sales' as Type,[MONTH],TOTAL
              from ( select DATENAME(month, sale_date)   AS [MONTH],count(*) TOTAL
                     from customers 
                     group by DATENAME(month, sale_date)                    
                   )x
    union

            select 3 as ranking,'Orders' as Type,[MONTH],TOTAL
             from ( select DATENAME(month, order_date)   AS [MONTH],count(*) TOTAL
                    from customers 
                    group by DATENAME(month, order_date)
                  )x

And the output is like these(just dummy data)

ranking	Type	MONTH	TOTAL
1	Birthdays	January	12
1	Birthdays	April	6
1	Birthdays	May	10
2	Sales	Febrary	8
2	Sales	April	14
2	Sales	May	10
3	Orders	June	4
3	Orders	July	3
3	Orders	October	6
3	Orders	December	17

I want to find count of these all these three types without using UNION and UNION ALL, means I want these data by single query statement (or more optimize version of these query)

Answer 1

Another approach is to create a CTE with all available ranking values ​​and use CROSS APPLY for it, as shown below.

WITH ranks(ranking) AS (
    SELECT * FROM (VALUES (1), (2), (3)) v(r)
)
SELECT 
    r.ranking,
    CASE WHEN r.ranking = 1 THEN 'Birthdays'
         WHEN r.ranking = 2 THEN 'Sales'
         WHEN r.ranking = 3 THEN 'Orders'
    END AS Type,
    DATENAME(month, CASE WHEN r.ranking = 1 THEN c.birth_date
                         WHEN r.ranking = 2 THEN c.sale_date
                         WHEN r.ranking = 3 THEN c.order_date
                    END) AS MONTH,
    COUNT(*) AS TOTAL
FROM customers  c
CROSS APPLY ranks r
GROUP BY r.ranking, 
         DATENAME(month, CASE WHEN r.ranking = 1 THEN c.birth_date
                              WHEN r.ranking = 2 THEN c.sale_date
                              WHEN r.ranking = 3 THEN c.order_date
                         END)
ORDER BY r.ranking, MONTH