'Subtract result of two CTE

So I have a query like this

Create Procedure sp_GetTotalTargetsCount
@userId int
AS
Begin
Create Table #RequestTargets
(
Name varchar(20),
Value int
)
Declare @rId int
Set @rId=0

Select @rId=r.RequestId From Requests r
Join RequestPipeline rp 
on r.RequestId=rp.RequestId
Join RequestStatus rs
on rp.RequestStatusId=rs.StatusId
Where rs.StatusDescription='In Progress.'

;With TotalCTE AS (Select Count(*) As TotalTargets FROM Targets Where InsertedByUserId=@userId),
FilteredCTE AS (Select Count(*) As FilteredTargets From RequestDetails Where RequestId=@rId)


Insert into #RequestTargets (Name,Value)
Select 'TotalTargets', Count(*) FROM Targets Where InsertedByUserId=@userId

Insert into #RequestTargets (Name,Value) 
Select 'UnderSurveillence', Count(*) From 
RequestDetails Where RequestId=@rId

Insert into #RequestTargets (Name,Value)
Values ('NotInSurveillence', (TotalCTE.TotalTargets-FilteredCTE.FilteredTargets)) --ERROR HERE it says Multipart identifier --  TotalTargets could not be bound
End

I think I can subract one count() query from the other count() without using CTE. Is there there a way to subtract the result of two CTE's as above. Please help.

sql-serverstored-procedurescommon-table-expression


Solution 1:[1]

You can't use CTE that way. Try

;WITH totalcte
     AS (SELECT Count(*) AS TotalTargets
         FROM   targets
         WHERE  insertedbyuserid = @userId),
     filteredcte
     AS (SELECT Count(*) AS FilteredTargets
         FROM   requestdetails
         WHERE  requestid = @rId)
INSERT INTO #requesttargets
            (NAME,
             value)
SELECT 'NotInSurveillence',
       totalcte.totaltargets - filteredcte.filteredtargets
FROM   totalcte
       CROSS JOIN filteredcte  
FROM  TotalCTE CROSS JOIN FilteredCTE

infact. . you can combine all into one big single query

 ;WITH totalcte
     AS (SELECT Count(*) AS TotalTargets
         FROM   targets
         WHERE  insertedbyuserid = @userId),
     filteredcte
     AS (SELECT Count(*) AS FilteredTargets
         FROM   requestdetails
         WHERE  requestid = @rId)
INSERT INTO #requesttargets
            (NAME,
             value)
SELECT 'NotInSurveillence',
       totalcte.totaltargets - filteredcte.filteredtargets
FROM   totalcte
       CROSS JOIN filteredcte
UNION ALL
SELECT 'TotalTargets',
       totalcte.totaltargets
FROM    totalcte
UNION ALL
SELECT 'UnderSurveillence',
       filteredcte.filteredtargets  
FROM   filteredcte

Solution 2:[2]

select * from cte1 union distinct cte2

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 richardec

Related Questions