In the first query, cte is not a good name because it is not a Common Table Expression. I would use a real CTE to match your queries:
with cte as (
SELECT sum(DISTINCT( F.TargetValue+F.Target_ForMen)) AS TARGET ,
sum(S.Achieved) AS ACHIEVED ,
F.Question_ID,
F.Year,
F.Quarter,
F.DistrictId,
F.QT_ForWomen ,
F.QT_FormMen,
F.QT_ForGirl ,
F.QT_ForBoy
FROM QuestionTarget F
LEFT OUTER JOIN AnswersNew AS S ON S.Question_ID=F.Question_ID
GROUP BY
F.Question_ID,
F.Year,
F.Quarter,
F.DistrictId,
F.QT_ForWomen ,
F.QT_FormMen,
F.QT_ForGirl ,
F.QT_ForBoy
)
SELECT
QT.Target As Target,
QT.Question_ID,
Q.QuestionText,AN.QTotal As Achieved,
QT.QT_ForWomen AS TW,
AN.id,
QT.QT_FormMen AS TM,
AN.QT_ForMen AS AM,
AN.QT_ForWomen AS AW,
QT.QT_ForGirl AS TG,
AN.QT_ForGirl AS AG,
QT.QT_ForBoy AS TB,
AN.QT_ForBoy AS AB,
V.VillageName,
AN.CdfPErsonName,
QT.District,AN.Year,
AN.Quarter,D.DistrictName,
QU.Quarter_Name,
CC.CommunityCodeName,
G.Group_Name,
Y.Year_Name,
AN.Date_Created,
AN.UserTypes,
AN.AnswerID,
U.IsActive,
AN.Status
from AnswersNew AN
INNER JOIN CTE QT
ON QT.Question_ID= AN.Question_ID
AND QT.Year=AN.Year
AND QT.Quarter=AN.Quarter
AND AN.DistrictId=QT.District
INNER JOIN Villages V ON V.VlgID=AN.VillagesId
INNER JOIN Quarter Qu ON Qu.Quarter_ID=AN.Quarter
INNER JOIN Users U ON U.UserId=AN.CDF_ID
INNER JOIN Questions Q ON Q.QuestionIds=QT.Question_ID
INNER JOIN CommunityCodes CC ON CC.CommunityCodeID =AN.CommunityCodes
INNER JOIN Groups G ON G.Group_ID=AN.UserTypes
INNER JOIN Year Y ON Y.YEAR_ID=AN.Year
INNER JOIN Districts D on D.DistrictId=U.DistrictId
WHERE U.IsVisible = 1
UPDATE: from comments:
This is how you write a cte:
with cte1 as (
SELECT
Question_ID ,
Sum(QuestionTarget) AS "Target"
FROM QuestionTarget
GROUP BY Question_ID
),
cte2 as (
SELECT
Sum(QTotal) AS Achieved ,
SUM(DISTINCT(Target)) AS Target
FROM AnswersNew AS S
RIGHT OUTER Join cte1 ON cte1.Question_ID=S.Question_ID
GROUP BY CTE.Question_ID),
cte3 as(
SELECT
SUM(Target) Target ,
Sum(Achieved) Achieved
FROM cte2)
SELECT
QT.Question_ID,
Q.QuestionText,AN.QTotal As Achieved,
QT.QT_ForWomen AS TW,
AN.id,
QT.QT_FormMen AS TM,
AN.QT_ForMen AS AM,
AN.QT_ForWomen AS AW,
QT.QT_ForGirl AS TG,
AN.QT_ForGirl AS AG,
QT.QT_ForBoy AS TB,
AN.QT_ForBoy AS AB,
V.VillageName,
AN.CdfPErsonName,
QT.District,AN.Year,
AN.Quarter,D.DistrictName,
QU.Quarter_Name,
CC.CommunityCodeName,
G.Group_Name,
Y.Year_Name,
AN.Date_Created,
AN.UserTypes,
AN.AnswerID,
U.IsActive,
AN.Status
from AnswersNew AN
INNER JOIN QuestionTarget QT ON QT.Question_ID= AN.Question_ID
AND QT.Year=AN.Year
AND QT.Quarter=AN.Quarter
AND AN.DistrictId=QT.District
INNER JOIN Villages V ON V.VlgID=AN.VillagesId
INNER JOIN Quarter Qu ON Qu.Quarter_ID=AN.Quarter
INNER JOIN Users U ON U.UserId=AN.CDF_ID
INNER JOIN Questions Q ON Q.QuestionIds=QT.Question_ID
INNER JOIN CommunityCodes CC ON CC.CommunityCodeID =AN.CommunityCodes
INNER JOIN Groups G ON G.Group_ID=AN.UserTypes
INNER JOIN Year Y ON Y.YEAR_ID=AN.Year
INNER JOIN Districts D on D.DistrictId=U.DistrictId
INNER JOIN cre3 ON
WHERE U.IsVisible = 1
That's all I can tell you. There is no more info you have given me
UPDATE2: from comments:
Like this?
with cte1 as (
SELECT
Question_ID ,
Sum(QuestionTarget) AS "Target"
FROM QuestionTarget
GROUP BY Question_ID
),
cte2 as (
SELECT
Sum(QTotal) AS Achieved ,
SUM(DISTINCT(Target)) AS Target ,
S.VillagesId
FROM AnswersNew AS S
RIGHT OUTER Join cte1 ON cte1.Question_ID=S.Question_ID
GROUP BY CTE.Question_ID,
S.VillagesId),
cte3 as(
SELECT
SUM(Target) Target ,
Sum(Achieved) Achieved ,
VillagesId
FROM cte2
Group By VillagesId)
select * from cte3
INNER JOIN Villages V ON V.VlgID=cte3.VillagesId