I am getting the records older and later than the given dates
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @SelectColumnName AS NVARCHAR(MAX)
DECLARE @PreviousMonthStart AS CHAR(15)
DECLARE @PreviousMonthEnd AS CHAR(15)
SET @PreviousMonthStart = '10-Mar-2017 10:21:43 PM'
SET @PreviousMonthEnd = '14-Mar-2017 10:21:43 PM'
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(CatNo)
FROM (SELECT DISTINCT CatNo FROM tblCategory) AS BCats
SELECT @SelectColumnName
= ISNULL(@SelectColumnName + ',','')
+ 'ISNULL(' + QUOTENAME(CatNo) + ', 0) AS '
+ QUOTENAME(CatNo)
FROM (SELECT DISTINCT CatNo FROM tblCategory) AS BCats
SET @DynamicPivotQuery =
'SELECT REPLACE(CONVERT(CHAR(15), RcvdDate, 106),'' '',''-'') AS RcvdDate, ' + @SelectColumnName +
' from
(
SELECT RcvdDate, CatNo, Amount, CatName, Sentby
FROM tblTransaction INNER JOIN
tblCategory ON tblCategory.CatNo = tblTransaction.BCatId
WHERE REPLACE(CONVERT(CHAR(15), RcvdDate, 106),'' '',''-'') BETWEEN '''+ @PreviousMonthStart + ''' AND '''+ @PreviousMonthEnd + '''
) x
PIVOT
(
SUM(x.Amount) for x.CatNo in (' + @ColumnName + ')
) P'
EXEC(@DynamicPivotQuery);