how to get distinct records sql server

USE [planrmbeDb2]
GO
/****** Object: StoredProcedure [dbo].[usp_Target_Test] Script Date: 09/15/2015 21:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Target_Test](@DistrictStr Nvarchar(max)=NULL,@YearStr [nvarchar](max)= NULL, @QuarterStr [nvarchar](max)=NULL,@QuestionID [int]=NULL)
AS
BEGIN
DECLARE @Tab TABLE
(
[Year] Nvarchar(max),
[Quarter] Nvarchar(max),
[District] Nvarchar(max),
[QuestionText] NVarchar(max)
)

DECLARE @TabALL TABLE
(

[District] int ,
[Year] int ,
[Quarter] int
)

DECLARE @Year_Tab TABLE
(
Year_Id int
)
DECLARE @Quarter_Tab TABLE
(
Quarter_Id int
)
DECLARE @District_Tab TABLE
(
District_Id int
)


DECLARE @Tab2 TABLE
(
[Target] [int],
[Achieved] [int],
[Year] Nvarchar(max),
[Quarter] Nvarchar(max),
[District] Nvarchar(max),
[QuestionText] NVarchar(max)
)


DECLARE @Year_ Nvarchar(max)
DECLARE @Quarter_ Nvarchar(max)
DECLARE @District_ Nvarchar(max)
DECLARE @QuestionText_ NVarchar(max)
DECLARE @DistrictID [int];
DECLARE @QuarterID [int];
DECLARE @YearID [int];
DECLARE @StartIndex [int];
DECLARE @EndIndex1 INT;
SET @StartIndex=1;
DECLARE @Str [nvarchar](max);

IF @DistrictStr IS NOT NULL
BEGIN
SET @DistrictStr=@DistrictStr+',';
WHILE CHARINDEX(',', @DistrictStr) > 0
BEGIN
SET @EndIndex1 = CHARINDEX(',', @DistrictStr);
SET @Str=SUBSTRING(@DistrictStr,1,@EndIndex1-1)
INSERT INTO @District_Tab VALUES (@Str)

SET @DistrictStr=SUBSTRING(@DistrictStr, @EndIndex1 + 1, LEN(@DistrictStr))
END
END
ELSE
BEGIN
INSERT INTO @District_Tab VALUES (NULL)
END

IF @QuarterStr IS NOT NULL
BEGIN
SET @QuarterStr=@QuarterStr+',';
WHILE CHARINDEX(',', @QuarterStr) > 0
BEGIN
SET @EndIndex1 = CHARINDEX(',', @QuarterStr);
SET @Str=SUBSTRING(@QuarterStr,1,@EndIndex1-1)
INSERT INTO @Quarter_Tab VALUES (@Str)

SET @QuarterStr=SUBSTRING(@QuarterStr, @EndIndex1 + 1, LEN(@QuarterStr))
END
END
ELSE
BEGIN
INSERT INTO @Quarter_Tab VALUES (NULL)
END

IF @YearStr IS NOT NULL
BEGIN
SET @YearStr=@YearStr+',';
WHILE CHARINDEX(',', @YearStr) > 0
BEGIN
SET @EndIndex1 = CHARINDEX(',', @YearStr);
SET @Str=SUBSTRING(@YearStr,1,@EndIndex1-1)
INSERT INTO @Year_Tab VALUES (@Str)

SET @YearStr=SUBSTRING(@YearStr, @EndIndex1 + 1, LEN(@YearStr))
END
END
ELSE
BEGIN
INSERT INTO @Year_Tab VALUES(NULL)
END


INSERT INTO @TabALL
SELECT * FROM (SELECT dt.District_Id FROM @District_Tab dt)T1,(SELECT yt.Year_Id FROM @Year_Tab yt)T2,(SELECT qt.Quarter_Id FROM @Quarter_Tab qt )T3



DECLARE RESULT_CURSOR CURSOR
FOR
SELECT * FROM @TabALL ta
OPEN RESULT_CURSOR
FETCH NEXT FROM RESULT_CURSOR INTO @DistrictID ,@YearID,@QuarterID
WHILE @@FETCH_STATUS = 0
BEGIN


if(@DistrictID=Null AND @QuarterID=NULL AND @YearID=NULL AND @QuestionID=NULL)
BEGIN
INSERT INTO @Tab VALUES('ALL','ALL','ALL','ALL')

END
ELSE
BEGIN

SET @Quarter_='ALL'
SET @District_='ALL'
SET @Year_='ALL'
SET @QuestionText_='ALL'

IF EXISTS(SELECT y.Year_Name FROM dbo.[Year] y WHERE y.Year_ID=@YearID)
BEGIN
SET @Year_=(SELECT y.Year_Name FROM dbo.[Year] y WHERE y.Year_ID=@YearID)
END

IF EXISTS(SELECT d.DistrictName from dbo.Districts d WHERE d.DistrictID=@DistrictID)
BEGIN
SET @District_=(SELECT d.DistrictName from dbo.Districts d WHERE d.DistrictID=@DistrictID)
END

IF EXISTS(SELECT q.Quarter_Name from dbo.Quarter q WHERE q.Quarter_ID=@QuarterID)
BEGIN
SET @Quarter_=(SELECT q.Quarter_Name from dbo.Quarter q WHERE q.Quarter_ID=@QuarterID)
END

IF EXISTS(SELECT q.QuestionText from dbo.Questions q WHERE q.QuestionIds=@QuestionID)
BEGIN
SET @QuestionText_=(SELECT q.QuestionText from dbo.Questions q WHERE q.QuestionIds=@QuestionID)

END

INSERT INTO @Tab
(
[Year],
Quarter,
District,
QuestionText
)
VALUES
(
@Year_, @Quarter_,@District_,@QuestionText_

)


END
IF @DistrictID=0
BEGIN
SET @DistrictID=NULL;
END

IF @YearID=0
BEGIN
SET @YearID=NULL;
END

IF @QuarterID=0
BEGIN
SET @DistrictID=NULL;
END

INSERT INTO @Tab2
SELECT Cte2.Target,Cte2.Achieved ,t. * FROM
(
SELECT QuestionTarget.Question_ID , Sum(ISNULL(QuestionTarget,0)) + Sum(ISNULL(QT_FormMen,0))+ Sum(ISNULL(QT_ForWomen,0)) +Sum(ISNULL(QT_ForGirl,0)) + Sum(ISNULL(QT_ForBoy,0)) AS Target,CTE.Achieved AS Achieved FROM QuestionTarget
FULL OUTER JOIN
(SELECT Question_ID,Sum(ISNULL(S.QTotal,0))+ Sum(ISNULL(QT_ForMen,0))+ Sum(ISNULL(QT_ForWomen,0)) +Sum(ISNULL(QT_ForBoy,0)) + Sum(ISNULL(QT_ForGirl,0)) AS Achieved FROM AnswersNew AS S
WHERE
(@YearID IS NULL OR ( S.Year=@YearID)) AND
(@QuarterID IS NULL OR( S.Quarter=@QuarterID)) AND
(@DistrictID IS NULL OR(S.DistrictId =@DistrictID))
AND (@QuestionID IS NULL OR ( S.Question_ID=@QuestionID))
GROUP BY Question_ID)CTE
ON
CTE.Question_ID=QuestionTarget.Question_ID

WHERE
(@YearID IS NULL OR ( dbo.QuestionTarget.Year=@YearID)) AND
(@QuarterID IS NULL OR( dbo.QuestionTarget.Quarter=@QuarterID)) AND
(@DistrictID IS NULL OR( dbo.QuestionTarget.District=@DistrictID))
AND (@QuestionID IS NULL OR ( dbo.QuestionTarget.Question_ID=@QuestionID))
GROUP BY QuestionTarget.Question_ID,CTE.Achieved
)Cte2 , @Tab t
DELETE FROM @Tab;
FETCH NEXT FROM RESULT_CURSOR INTO @DistrictID ,@YearID,@QuarterID
END

CLOSE RESULT_CURSOR
DEALLOCATE RESULT_CURSOR

set @District_=Null;
set @Year_=NULL
set @Quarter_=null;

SELECT @District_=COALESCE(@District_+',',' ')+ t.District FROM @Tab2 t WHERE t.District!='ALL'
SELECT @Year_=COALESCE(@Year_+',',' ')+t.[Year] FROM @Tab2 t WHERE t.year !='ALL'
SELECT @Quarter_=COALESCE(@Quarter_+',',' ')+t.Quarter FROM @Tab2 t WHERE t.Quarter!='ALL'
DELETE FROM @Tab
INSERT INTO @Tab
(
[Year],
Quarter,
District,
QuestionText
)
VALUES
(
@Year_, -- Year - Nvarchar
@Quarter_, -- Quarter - Nvarchar
@District_, -- District - Nvarchar
@QuestionText_ -- QuestionText - NVarchar
)

IF @Year_ IS NULL
BEGIN
SET @Year_='ALL'
END

IF @District_ IS NULL
BEGIN
SET @District_='ALL'
END

IF @Quarter_ IS NULL
BEGIN
SET @Quarter_='ALL'
END

SELECT Tab3.Targer,Tab3.Achieved, @District_ AS 'District',@Year_ AS 'Year',@Quarter_ AS 'Quarter', Tab3.QuestionText FROM
(SELECT SUM( t.Target) AS [Targer], SUM(t.Achieved) as [Achieved] , t.QuestionText FROM @Tab2 t GROUP BY t.QuestionText)Tab3;


END

USE [planrmbeDb2]
GO
/****** Object: StoredProcedure [dbo].[usp_Target_Test] Script Date: 09/15/2015 21:37:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Target_Test](@DistrictStr Nvarchar(max)=NULL,@YearStr [nvarchar](max)= NULL, @QuarterStr [nvarchar](max)=NULL,@QuestionID [int]=NULL)
AS
BEGIN
DECLARE @Tab TABLE
(
[Year] Nvarchar(max),
[Quarter] Nvarchar(max),
[District] Nvarchar(max),
[QuestionText] NVarchar(max)
)

DECLARE @TabALL TABLE
(

[District] int ,
[Year] int ,
[Quarter] int
)

DECLARE @Year_Tab TABLE
(
Year_Id int
)
DECLARE @Quarter_Tab TABLE
(
Quarter_Id int
)
DECLARE @District_Tab TABLE
(
District_Id int
)


DECLARE @Tab2 TABLE
(
[Target] [int],
[Achieved] [int],
[Year] Nvarchar(max),
[Quarter] Nvarchar(max),
[District] Nvarchar(max),
[QuestionText] NVarchar(max)
)


DECLARE @Year_ Nvarchar(max)
DECLARE @Quarter_ Nvarchar(max)
DECLARE @District_ Nvarchar(max)
DECLARE @QuestionText_ NVarchar(max)
DECLARE @DistrictID [int];
DECLARE @QuarterID [int];
DECLARE @YearID [int];
DECLARE @StartIndex [int];
DECLARE @EndIndex1 INT;
SET @StartIndex=1;
DECLARE @Str [nvarchar](max);

IF @DistrictStr IS NOT NULL
BEGIN
SET @DistrictStr=@DistrictStr+',';
WHILE CHARINDEX(',', @DistrictStr) > 0
BEGIN
SET @EndIndex1 = CHARINDEX(',', @DistrictStr);
SET @Str=SUBSTRING(@DistrictStr,1,@EndIndex1-1)
INSERT INTO @District_Tab VALUES (@Str)

SET @DistrictStr=SUBSTRING(@DistrictStr, @EndIndex1 + 1, LEN(@DistrictStr))
END
END
ELSE
BEGIN
INSERT INTO @District_Tab VALUES (NULL)
END

IF @QuarterStr IS NOT NULL
BEGIN
SET @QuarterStr=@QuarterStr+',';
WHILE CHARINDEX(',', @QuarterStr) > 0
BEGIN
SET @EndIndex1 = CHARINDEX(',', @QuarterStr);
SET @Str=SUBSTRING(@QuarterStr,1,@EndIndex1-1)
INSERT INTO @Quarter_Tab VALUES (@Str)

SET @QuarterStr=SUBSTRING(@QuarterStr, @EndIndex1 + 1, LEN(@QuarterStr))
END
END
ELSE
BEGIN
INSERT INTO @Quarter_Tab VALUES (NULL)
END

IF @YearStr IS NOT NULL
BEGIN
SET @YearStr=@YearStr+',';
WHILE CHARINDEX(',', @YearStr) > 0
BEGIN
SET @EndIndex1 = CHARINDEX(',', @YearStr);
SET @Str=SUBSTRING(@YearStr,1,@EndIndex1-1)
INSERT INTO @Year_Tab VALUES (@Str)

SET @YearStr=SUBSTRING(@YearStr, @EndIndex1 + 1, LEN(@YearStr))
END
END
ELSE
BEGIN
INSERT INTO @Year_Tab VALUES(NULL)
END


INSERT INTO @TabALL
SELECT * FROM (SELECT dt.District_Id FROM @District_Tab dt)T1,(SELECT yt.Year_Id FROM @Year_Tab yt)T2,(SELECT qt.Quarter_Id FROM @Quarter_Tab qt )T3



DECLARE RESULT_CURSOR CURSOR
FOR
SELECT * FROM @TabALL ta
OPEN RESULT_CURSOR
FETCH NEXT FROM RESULT_CURSOR INTO @DistrictID ,@YearID,@QuarterID
WHILE @@FETCH_STATUS = 0
BEGIN


if(@DistrictID=Null AND @QuarterID=NULL AND @YearID=NULL AND @QuestionID=NULL)
BEGIN
INSERT INTO @Tab VALUES('ALL','ALL','ALL','ALL')

END
ELSE
BEGIN

SET @Quarter_='ALL'
SET @District_='ALL'
SET @Year_='ALL'
SET @QuestionText_='ALL'

IF EXISTS(SELECT y.Year_Name FROM dbo.[Year] y WHERE y.Year_ID=@YearID)
BEGIN
SET @Year_=(SELECT y.Year_Name FROM dbo.[Year] y WHERE y.Year_ID=@YearID)
END

IF EXISTS(SELECT d.DistrictName from dbo.Districts d WHERE d.DistrictID=@DistrictID)
BEGIN
SET @District_=(SELECT d.DistrictName from dbo.Districts d WHERE d.DistrictID=@DistrictID)
END

IF EXISTS(SELECT q.Quarter_Name from dbo.Quarter q WHERE q.Quarter_ID=@QuarterID)
BEGIN
SET @Quarter_=(SELECT q.Quarter_Name from dbo.Quarter q WHERE q.Quarter_ID=@QuarterID)
END

IF EXISTS(SELECT q.QuestionText from dbo.Questions q WHERE q.QuestionIds=@QuestionID)
BEGIN
SET @QuestionText_=(SELECT q.QuestionText from dbo.Questions q WHERE q.QuestionIds=@QuestionID)

END

INSERT INTO @Tab
(
[Year],
Quarter,
District,
QuestionText
)
VALUES
(
@Year_, @Quarter_,@District_,@QuestionText_

)


END
IF @DistrictID=0
BEGIN
SET @DistrictID=NULL;
END

IF @YearID=0
BEGIN
SET @YearID=NULL;
END

IF @QuarterID=0
BEGIN
SET @DistrictID=NULL;
END

INSERT INTO @Tab2
SELECT Cte2.Target,Cte2.Achieved ,t. * FROM
(
SELECT QuestionTarget.Question_ID , Sum(ISNULL(QuestionTarget,0)) + Sum(ISNULL(QT_FormMen,0))+ Sum(ISNULL(QT_ForWomen,0)) +Sum(ISNULL(QT_ForGirl,0)) + Sum(ISNULL(QT_ForBoy,0)) AS Target,CTE.Achieved AS Achieved FROM QuestionTarget
FULL OUTER JOIN
(SELECT Question_ID,Sum(ISNULL(S.QTotal,0))+ Sum(ISNULL(QT_ForMen,0))+ Sum(ISNULL(QT_ForWomen,0)) +Sum(ISNULL(QT_ForBoy,0)) + Sum(ISNULL(QT_ForGirl,0)) AS Achieved FROM AnswersNew AS S
WHERE
(@YearID IS NULL OR ( S.Year=@YearID)) AND
(@QuarterID IS NULL OR( S.Quarter=@QuarterID)) AND
(@DistrictID IS NULL OR(S.DistrictId =@DistrictID))
AND (@QuestionID IS NULL OR ( S.Question_ID=@QuestionID))
GROUP BY Question_ID)CTE
ON
CTE.Question_ID=QuestionTarget.Question_ID

WHERE
(@YearID IS NULL OR ( dbo.QuestionTarget.Year=@YearID)) AND
(@QuarterID IS NULL OR( dbo.QuestionTarget.Quarter=@QuarterID)) AND
(@DistrictID IS NULL OR( dbo.QuestionTarget.District=@DistrictID))
AND (@QuestionID IS NULL OR ( dbo.QuestionTarget.Question_ID=@QuestionID))
GROUP BY QuestionTarget.Question_ID,CTE.Achieved
)Cte2 , @Tab t
DELETE FROM @Tab;
FETCH NEXT FROM RESULT_CURSOR INTO @DistrictID ,@YearID,@QuarterID
END

CLOSE RESULT_CURSOR
DEALLOCATE RESULT_CURSOR

set @District_=Null;
set @Year_=NULL
set @Quarter_=null;

SELECT @District_=COALESCE(@District_+',',' ')+ t.District FROM @Tab2 t WHERE t.District!='ALL'
SELECT @Year_=COALESCE(@Year_+',',' ')+t.[Year] FROM @Tab2 t WHERE t.year !='ALL'
SELECT @Quarter_=COALESCE(@Quarter_+',',' ')+t.Quarter FROM @Tab2 t WHERE t.Quarter!='ALL'
DELETE FROM @Tab
INSERT INTO @Tab
(
[Year],
Quarter,
District,
QuestionText
)
VALUES
(
@Year_, -- Year - Nvarchar
@Quarter_, -- Quarter - Nvarchar
@District_, -- District - Nvarchar
@QuestionText_ -- QuestionText - NVarchar
)

IF @Year_ IS NULL
BEGIN
SET @Year_='ALL'
END

IF @District_ IS NULL
BEGIN
SET @District_='ALL'
END

IF @Quarter_ IS NULL
BEGIN
SET @Quarter_='ALL'
END

SELECT Tab3.Targer,Tab3.Achieved, @District_ AS 'District',@Year_ AS 'Year',@Quarter_ AS 'Quarter', Tab3.QuestionText FROM
(SELECT SUM( t.Target) AS [Targer], SUM(t.Achieved) as [Achieved] , t.QuestionText FROM @Tab2 t GROUP BY t.QuestionText)Tab3;


END

Target 0+0+10+1=11

Achieved 2+21+6+0=29
which is correct

as you asked that



please tell me when u are giving multiple values like for Quarter1,2 did u fetching the records from answersnew table and quarter table where Quarter=1 or Quarter=2 or u are using where Quarter=1 AND quarter=2

Here i am calculate result first for Quarter1 then Quarter 2 and insert into Table @Tab3 but retrieve as sum of both result.

Please check this
I can also modify this stored procedure
Like District , Year , Quarter contain duplicate values like 2015,2015,2015,2015 and Quater1,quarter2,Quarter1,Quarter2
i can retrieve unique result.
i can modify this stored procedure if you want to provide multiple question like.
QuestionID=1,2,3

Please check this and Reply me if this is not correct or you want apply any modification......
Now Year column doesn't contain "ALL" text.

Last modified stored procedure is

USE [planrmbeDb2]
GO
/****** Object: StoredProcedure [dbo].[usp_Target_Test] Script Date: 09/14/2015 15:40:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Target_Test](@DistrictStr Nvarchar(max)=NULL,@YearStr [nvarchar](max)= NULL, @QuarterStr [nvarchar](max)=NULL,@QuestionID [int]=NULL)
AS
BEGIN
DECLARE @Tab TABLE
(
[Year] Nvarchar(max),
[Quarter] Nvarchar(max),
[District] Nvarchar(max),
[QuestionText] NVarchar(max)
)

DECLARE @Tab2 TABLE
(
[Target] [int],
[Achieved] [int],
[Year] Nvarchar(max),
[Quarter] Nvarchar(max),
[District] Nvarchar(max),
[QuestionText] NVarchar(max)
)


DECLARE @Year_ Nvarchar(max)
DECLARE @Quarter_ Nvarchar(max)
DECLARE @District_ Nvarchar(max)
DECLARE @QuestionText_ NVarchar(max)
DECLARE @DistrictID [int];
DECLARE @QuarterID [int];
DECLARE @YearID [int];
DECLARE @StartIndex [int], @EndIndex1 INT;
DECLARE @EndIndex2 INT;
DECLARE @EndIndex3 INT;
SET @StartIndex=1;
WHILE CHARINDEX(',', @DistrictStr) > 0 OR CHARINDEX(',', @YearStr) > 0 OR CHARINDEX(',', @QuarterStr) > 0
BEGIN
SET @EndIndex1 = CHARINDEX(',', @DistrictStr);
SET @EndIndex2 = CHARINDEX(',', @YearStr);
SET @EndIndex3 = CHARINDEX(',', @QuarterStr);

SET @DistrictID=NULL;
SET @QuarterID=NULL;
SET @YearID=NULL;
IF @EndIndex1>1
BEGIN
SET @DistrictID= (SELECT CONVERT([int],SUBSTRING(@DistrictStr, @StartIndex, @EndIndex1-1)))
END
IF @EndIndex3>1
BEGIN
SET @QuarterID= ( SELECT CONVERT([int],SUBSTRING(@QuarterStr, @StartIndex, @EndIndex3-1)))
END
IF @EndIndex2>1
BEGIN
SET @YearID=( SELECT CONVERT([int],SUBSTRING(@YearStr, @StartIndex, @EndIndex2-1)))
END

if(@DistrictID=Null AND @QuarterID=NULL AND @YearID=NULL AND @QuestionID=NULL)
BEGIN
INSERT INTO @Tab VALUES('ALL','ALL','ALL','ALL')

END
ELSE
BEGIN

SET @Quarter_='ALL'
SET @District_='ALL'
SET @Year_='ALL'
SET @QuestionText_='ALL'

IF EXISTS(SELECT y.Year_Name FROM dbo.[Year] y WHERE y.Year_ID=@YearID)
BEGIN
SET @Year_=(SELECT y.Year_Name FROM dbo.[Year] y WHERE y.Year_ID=@YearID)
END

IF EXISTS(SELECT d.DistrictName from dbo.Districts d WHERE d.DistrictID=@DistrictID)
BEGIN
SET @District_=(SELECT d.DistrictName from dbo.Districts d WHERE d.DistrictID=@DistrictID)
END

IF EXISTS(SELECT q.Quarter_Name from dbo.Quarter q WHERE q.Quarter_ID=@QuarterID)
BEGIN
SET @Quarter_=(SELECT q.Quarter_Name from dbo.Quarter q WHERE q.Quarter_ID=@QuarterID)
END

IF EXISTS(SELECT q.QuestionText from dbo.Questions q WHERE q.QuestionIds=@QuestionID)
BEGIN
SET @QuestionText_=(SELECT q.QuestionText from dbo.Questions q WHERE q.QuestionIds=@QuestionID)

END

INSERT INTO @Tab
(
[Year],
Quarter,
District,
QuestionText
)
VALUES
(
@Year_, @Quarter_,@District_,@QuestionText_

)


END

INSERT INTO @Tab2
SELECT Cte2.Target,Cte2.Achieved ,t. * FROM
(
SELECT QuestionTarget.Question_ID , Sum(ISNULL(QuestionTarget,0)) + Sum(ISNULL(QT_FormMen,0))+ Sum(ISNULL(QT_ForWomen,0)) +Sum(ISNULL(QT_ForGirl,0)) + Sum(ISNULL(QT_ForBoy,0)) AS Target,CTE.Achieved AS Achieved FROM QuestionTarget
FULL OUTER JOIN
(SELECT Question_ID,Sum(ISNULL(S.QTotal,0))+ Sum(ISNULL(QT_ForMen,0))+ Sum(ISNULL(QT_ForWomen,0)) +Sum(ISNULL(QT_ForBoy,0)) + Sum(ISNULL(QT_ForGirl,0)) AS Achieved FROM AnswersNew AS S
WHERE
(@YearID IS NULL OR ( S.Year=@YearID)) AND
(@QuarterID IS NULL OR( S.Quarter=@QuarterID)) AND
(@DistrictID IS NULL OR(S.DistrictId =@DistrictID))
AND (@QuestionID IS NULL OR ( S.Question_ID=@QuestionID))
GROUP BY Question_ID)CTE
ON
CTE.Question_ID=QuestionTarget.Question_ID

WHERE
(@YearID IS NULL OR ( dbo.QuestionTarget.Year=@YearID)) AND
(@QuarterID IS NULL OR( dbo.QuestionTarget.Quarter=@QuarterID)) AND
(@DistrictID IS NULL OR( dbo.QuestionTarget.District=@DistrictID))
AND (@QuestionID IS NULL OR ( dbo.QuestionTarget.Question_ID=@QuestionID))
GROUP BY QuestionTarget.Question_ID,CTE.Achieved
)Cte2 , @Tab t



SET @DistrictStr=SUBSTRING(@DistrictStr, @EndIndex1 + 1, LEN(@DistrictStr))
SET @YearStr=SUBSTRING(@YearStr, @EndIndex2 + 1, LEN(@YearStr))
SET @QuarterStr=SUBSTRING(@QuarterStr, @EndIndex3 + 1, LEN(@QuarterStr))
DELETE FROM @Tab;
END
set @District_=Null;
set @Year_=NULL
set @Quarter_=null;

SELECT @District_=COALESCE(@District_+',',' ')+ t.District FROM @Tab2 t WHERE t.District!='ALL'
SELECT @Year_=COALESCE(@Year_+',',' ')+t.[Year] FROM @Tab2 t WHERE t.year !='ALL'
SELECT @Quarter_=COALESCE(@Quarter_+',',' ')+t.Quarter FROM @Tab2 t WHERE t.Quarter!='ALL'
DELETE FROM @Tab
INSERT INTO @Tab
(
[Year],
Quarter,
District,
QuestionText
)
VALUES
(
@Year_, -- Year - Nvarchar
@Quarter_, -- Quarter - Nvarchar
@District_, -- District - Nvarchar
@QuestionText_ -- QuestionText - NVarchar
)

SELECT Tab3.Targer,Tab3.Achieved, @District_ AS 'District',@Year_ AS 'Year',@Quarter_ AS 'Quarter', Tab3.QuestionText FROM
(SELECT SUM( t.Target) AS [Targer], SUM(t.Achieved) as [Achieved] , t.QuestionText FROM @Tab2 t GROUP BY t.QuestionText)Tab3;



END

Hello Danish i checked my stored procedure for your query i find this result
QuestionID=1
Result for District=1 , Year=5, Quarter=1


Result for District=1 , Year=5, Quarter=2



Result for District=9 , Year=5, Quarter=1



Result for District=9 , Year=5, Quarter=2




You can check that this result is little different form your result
Your result showing 6 instead of NULL but this is correct result according my table


You can see that there is not any result in Answer Table for
So these is correct result it may be possible that your table contain data for above parameter but
There is not any data in my AnswerNew table.

if I compute a combine result then it will be below

Dear Pankaj Kumar Choudhary you query really works with any combination thanks i was putting ZERO instead of NULL when i Put NULL values as parameters for test it gives correct result, Now a bit more question i have dear when i am using crystal report and using its parameter pane for parameters then i allow me to select multiple values like I can select quarter 1,   Quarter 2 ----so on or all quarter and so it provide me a functionality to provide range values , how can i achieve this by using stored procedure which you gave me i hope u understand . Now we can select combination of values but one from each dropdown i want to select many value at a time for one parameter i am attaching a screenshot with this email so see it what does i mean by 

Dear Friend as i already told you in my previous post that My table contains data like that 

Hello @Dansih i create a stored procedure na duse the following tables that you maintained in

if  in my table i am using this 

crystal report format 

Dear 

Thanks Pankaj Kumar Choudhary  I am checking it at my end ,its a whole debate subject which raises many many threads :) thanks 

 please check .rar fie 

Dear Pankaj Kumar Choudhary You send last query it works when i have distinct records in target table , but my sceniro does not restrict the user to make distinct records always there could be a possibility that a question having same target value i.e. two or more question having same target value in your query you are applying distinct so it always produce the sum of distinct values so it does not work for me :)

SELECT SUM(AchievedValue) AS ACHIEVEDRecord,TargetValue AS TARGETRecord

FROM test1

INNER JOIN test2 on test2.QuestionID=test1.QuestionID

group by test1.QuestionID, test1.TargetValue

Dear Upendra Pratap Shahi it is giving 1500 Rows instead of one row showing sum of both :)