By using single CTE with multiple statements in it. can I perform mathematical operations on CTE out-CodePudding

Below is my CTE Example It's just a example my actual query is different

;with CTE as 
( select 1 1 as A,2 2 as B,3 3 as C
)
select (A B)-C as Final_Count from CTE

here in above example A,B & C stands for count which I get from 3 different tables But when I ran my query it shows me error n when I ran this example it shows me output

Anyone help me into this? where i'm going wrong?

Here is my actual Query.

;with CTE as
(
--Till prev mth SPOS count
(select count(*) from #final_merchant where cast(Onboarding_Date as Date)<='2022-09-30' and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')
--66149
) A,
(
--Current_Month SPOS count
select count(*) from #npciactive
where  cast(Onboarding_Date as Date)>='2022-10-01'  and cast(Onboarding_Date as Date)<='2022-10-31'
and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')
group by npci
--2201
) B,
(
--Deactivated_SPOS_In_Current_Month
select count(*) from Opsmerchant where cast(Deactivation_DATE as Date)>='2022-10-01' and cast(Deactivation_DATE as Date)<='2022-10-31'
and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')  
--13
) C)
select (A B)-C Final_Count from CTE

CodePudding user response：

The CTE needs to be a SELECT query, so you must add also a SELECT for all subqueires

;with CTE as
(SELECT 
--Till prev mth SPOS count
(select count(*) from #final_merchant where cast(Onboarding_Date as Date)<='2022-09-30' and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')
--66149
) As A, 
(
--Current_Month SPOS count
select count(*)  from #npciactive
where  cast(Onboarding_Date as Date)>='2022-10-01'  and cast(Onboarding_Date as Date)<='2022-10-31'
and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')
group by npci
--2201
) AS B, 
(
--Deactivated_SPOS_In_Current_Month
select count(*) from Opsmerchant where cast(Deactivation_DATE as Date)>='2022-10-01' and cast(Deactivation_DATE as Date)<='2022-10-31'
and terminal_type in ('Soft POS','SPOS','MPOS AND SPOS')  
--13
) C)
select (A B)-C AS Final_Count from CTE;