Select records between two dates in dynamic pivot query in SQL Server

Last Reply one year ago By Andrea

Posted one year ago

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);
Posted one year ago
Hi @satabeach,
Please try the following

Select Records between two dates in SQL Server

It might help you.

Cheers Andrea.