MSSQL Dynamic SQL query ISNULL

Question

I have a problem in my dynamic query I want that the values that are null to convert in zero, I found the function that convert but in my dynamic query does not work

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

I want that values from sum(b.value) that are NULL to convert in zero. How to do this???

UPDATED

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

Instead of concatenating string use parameters sth like: EXEC dbo.sp_executesql @DynamicPivotQuery, N'@start DATE, @actual DATE, @bus VARCHAR(50), @start, @actual, @bus — Lukasz Szozda
– Lukasz Szozda, Commented Dec 23, 2015 at 10:18
@lad2025 is not working, I don't know what's the problem.... — Dan Paschevici
– Dan Paschevici, Commented Dec 23, 2015 at 11:14

Devart · Accepted Answer · 2015-12-23 12:57:40Z

2

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

update -

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

edited Dec 23, 2015 at 12:57

answered Dec 23, 2015 at 10:24

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Paschevici Over a year ago

Dear Devart, thank you for your answer, but something is not working, I did exactly how you said but the output is still NULL. Thanks one more time

Devart Over a year ago

please provide values of your parameters + datatype for b.date and bu.name

Dan Paschevici Over a year ago

Dear Devart, I tried, but something is not working, I'm appreciate your work, and thank you one more time for your help. I will vote up your answer. Thank You!!!

shadow · Accepted Answer · 2015-12-23 10:20:00Z

1

I guess you need something like this:

IsNull( sum(b.value), 0 )

answered Dec 23, 2015 at 10:20

shadow

1,9031 gold badge17 silver badges25 bronze badges

Collectives™ on Stack Overflow

MSSQL Dynamic SQL query ISNULL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related