MSSQL Динамический SQL-запрос ISNULL

Question

У меня есть проблема в моем динамическом запросе, я хочу, чтобы значения, которые null чтобы преобразовать в ноль, я нашел функцию, которая convert но по моему динамический запрос не работает

SET @DynamicPivotQuery = 
'select * from (
     select ct.category as [name], 
     CONVERT(nvarchar(50),   DATENAME(m, b.date) 
        + '', '' 
        + DATENAME(yyyy,b.date))as date 
        , sum(b.value) as value from bus bu
            join bus_category buc on bu.id = buc.business_unit_id
            join category ct on ct.id = buc.type_id
            join bus_actual b on buc.id = b.bus_category_id
        where b.date between '''+  cast     (@start as VARCHAR(50))+''' and '''+  cast     (@actual as VARCHAR(50))+'''
         and bu.name = '''+  cast     (@bus as VARCHAR(50))+'''
            group by buca.date, ct.category_name

Я хочу, чтобы значения от sum(b.value) которые NULL конвертировать в ноль. Как это сделать???

ОБНОВЛЕНО

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @start AS DateTime
DECLARE @end AS DateTime
DECLARE @actual AS DateTime
DECLARE @first AS DateTime
DECLARE @bus AS VARCHAR(50)

SET @start = '2015-07-01';
SET @end   = '2016-06-01';
SET @actual = '2015-09-01';
SET @first = '2015-10-01';
SET @bus = 'EUR';


--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(date1)
FROM (  

    SELECT m.date1, m.date2 FROM(
SELECT DISTINCT CONVERT(nvarchar(50),   DATENAME(m, date) 
                               + ', ' 
                               + DATENAME(yyyy,date)) as date1, date as date2  

        FROM bus_actual where date between @start and  @actual 

        union all

                       SELECT DISTINCT
                        CONVERT(nvarchar(50),   DATENAME(m, date) 
                               + ', ' 
                               + DATENAME(yyyy,date)) as date1, date as date3 
                        FROM bus_forecast where date between @first and @end

        )m 
    )tab order by tab.date2

 SET @DynamicPivotQuery = 
'select * from (

        select ct.category as [name], 
        CONVERT(nvarchar(50),   DATENAME(m, bu.date) 
                               + '', '' 
                               + DATENAME(yyyy,bu.date))as date 

        ,ISNULL(sum(bu.value),0) as value from bus bu
            join bus_category buc on bu.id = buc.bus_id
            join category ct on ct.id = buc.id
            join bus_actual b on buc.id = b.bus_id
        where b.date between '''+  cast     (@start as VARCHAR(50))+''' and '''+  cast     (@actual as VARCHAR(50))+'''
         and bu.name = '''+  cast     (@bus as VARCHAR(50))+'''
            group by bu.date, ct.category

            ) as t

            PIVOT(  SUM(t.value) 
          FOR date IN (' + @ColumnName + ')) AS PVTTable'

            EXEC sp_executesql @DynamicPivotQuery, 
             N'@start datetime, @actual_date datetime, @bus VARCHAR(50)',
    @startFY = @start, @actual = @actual, @bus = @business_unit

Answer 1

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
SELECT
      ct.category AS [name]
    , DATENAME(m, b.[date]) + '' '' + DATENAME(yyyy, b.[date])) AS [date]  
    , ISNULL(SUM(b.value), 0) AS value
FROM dbo.bus bu
JOIN dbo.bus_category buc ON bu.id = buc.business_unit_id
JOIN dbo.category ct ON ct.id = buc.[type_id]
JOIN dbo.bus_actual b ON buc.id = b.bus_category_id
WHERE b.date between @start AND @actual
    AND bu.name = @bus
GROUP BY DATENAME(m, b.[date]) + '' '' + DATENAME(yyyy, b.[date])), ct.category'

--PRINT @SQL
EXEC sys.sp_executesql
    @SQL,
    N'@start VARCHAR(50), @actual VARCHAR(50), @bus VARCHAR(50)',
    @start = @start, @actual = @actual, @bus = @bus

Обновить -

DECLARE
      @DynamicPivotQuery NVARCHAR(MAX)
    , @ColumnName NVARCHAR(MAX)
    , @start DATETIME
    , @end DATETIME
    , @actual DATETIME
    , @first DATETIME
    , @bus VARCHAR(50)

SELECT
      @start = '2015-07-01'
    , @end = '2016-06-01'
    , @actual = '2015-09-01'
    , @first = '2015-10-01'
    , @bus = 'EUR'

SELECT @ColumnName = STUFF((
    SELECT ', ' + QUOTENAME(dt) dt
    FROM (
        SELECT [date], dt = DATENAME(m, [date])  + ' ' + DATENAME(yyyy, [date])
        FROM dbo.bus_actual
        WHERE [date] BETWEEN @start AND @actual 

        UNION

        SELECT [date], DATENAME(m, [date])  + ' ' + DATENAME(yyyy, [date]) 
        FROM dbo.bus_forecast
        WHERE [date] BETWEEN @first AND @end
    ) t
    ORDER BY [date]
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

SET @DynamicPivotQuery = 
'
SELECT *
FROM (
    SELECT
          ct.category
        , [date] = DATENAME(m, bu.[date]) + '' '' + DATENAME(yyyy, bu.[date])) 
        , bu.value
    FROM dbo.bus bu
    JOIN dbo.bus_category buc on bu.id = buc.bus_id
    JOIN dbo.category ct on ct.id = buc.id
    JOIN dbo.bus_actual b on buc.id = b.bus_id
    WHERE b.[date] between @startFY and @actual_date
        and bu.name = @bus
    GROUP BY bu.[date], ct.category
) t
PIVOT(
    SUM(value) 
    FOR [date] IN (' + @ColumnName + ')
) p'

EXEC sys.sp_executesql
    @DynamicPivotQuery, 
    N'@startFY DATETIME, @actual_date DATETIME, @bus VARCHAR(50)',
    @startFY = @start, @actual_date = @actual, @bus = @bus

Answer 2

Я думаю, вам нужно что-то вроде этого:

IsNull( sum(b.value), 0 )