I have the following generated fees data for the months like this
AdmissionNo
|
Month
|
Fees
|
Head
|
Price
|
R-01
|
05-May
|
1500
|
0
|
0
|
R-02
|
05-May
|
1500
|
0
|
0
|
R-03
|
05-May
|
1500
|
0
|
0
|
R-01
|
06-Jun
|
1500
|
1
|
120
|
R-01
|
06-Jun
|
1500
|
2
|
150
|
R-01
|
06-Jun
|
1500
|
3
|
50
|
R-02
|
06-Jun
|
1500
|
1
|
120
|
R-02
|
06-Jun
|
1500
|
2
|
150
|
R-02
|
06-Jun
|
1500
|
3
|
50
|
R-01
|
07-Jul
|
1500
|
0
|
0
|
R-02
|
07-Jul
|
1500
|
0
|
0
|
R-03
|
07-Jul
|
1500
|
0
|
0
|
Now I am filtering the data based on the month and wrote the following query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(HeadName) FROM (SELECT DISTINCT HeadName FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID) AS Head
DECLARE @qry NVARCHAR(4000)
IF(@cols IS NOT NULL AND @cols <> '[0]')
SET @qry =
N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
FROM (SELECT AdmissionNo,Month,Fees,HeadName,Price
FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID ) p
PIVOT (MAX(Price) FOR HeadName IN (' + @cols + ')) AS Pvt'
ELSE
BEGIN
SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate where Month=''05-May'''
END
EXEC sp_executesql @qry
it is not filtering the data. but when i wrote the query by applying a bit change like this it is showing nothing
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(HeadName) FROM (SELECT DISTINCT HeadName FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID) AS Head
DECLARE @qry NVARCHAR(4000)
IF(@cols IS NOT NULL AND @cols <> '[0]')
SET @qry =
N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
FROM (SELECT AdmissionNo,Month,Fees,HeadName,Price
FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID where Month=''05-May'') p
PIVOT (MAX(Price) FOR HeadName IN (' + @cols + ')) AS Pvt'
ELSE
BEGIN
SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate '
END
EXEC sp_executesql @qry
but in where clause i wrote where Month=''06-Jun'' then it is showing data.
In simply words it is only showing data in the case of '06-Jun' whereas It must show the data based on the month of '05-May' or '07-Jul'.