See the second screen shot. where 2010 FYA and rest of the columns are dynamic columns. see first record where dynamic column values are NULL. now tell me how could i mention dynamic column value should not be null when fetching data.
Set @AvgSql = @AvgSql+ 'Avg(CONVERT(decimal(20,6),['+@Period+'])) ['+@Period+'],'
In this way i am storing dynamic column wise vaue into #TmpZacksCons temporary table.
SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,
'+@PeriodCols+'
)
Select b.Section, b.LineItem,Max(Ord)+1 Ord,
'+@AvgSql+'
From #TmpAll_Broker_LI b
Group By b.Section, b.LineItem'
EXEC(@sql)
this is my final query which return whole data where i need to filter out null value. null data should not come.
SET @sql = '
Select XX.*,'''' scale,Isnull(AllowComma,''FALSE'') AllowComma,Isnull(AllowedDecimalPlace,''0'') AllowedDecimalPlace,
Isnull(AllowPercentageSign,''FALSE'') AllowPercentageSign,Isnull(CurrencySign,'''') CurrencySign,Isnull(BM_Denominator,'''') BM_Denominator
From
(
---- Broker Detail
Select AA.Section,AA.LineItem,Csm.DisplayInCSM ,AA.BrokerCode Broker,AA.BrokerName,'''' BM_Element,'''' BM_Code,AA.Ord,AA.[Revise Date],AA.LineItemId,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment
From tblCSM_ModelDetails Csm LEFT OUTER JOIN (
Select b.*,L.ID LineItemId
From #TmpAll_Broker_LI b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
) AA ON Csm.LineItemId=AA.LineItemId
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0 AND Type !=''SHEET''
UNION
----- Consensus
Select Section, b.LineItem,DisplayInCSM, '''' Broker,'''' BrokerName,'''' BM_Element,'''' BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment
From #TmpZacksCons b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
INNER JOIN tblCSM_ModelDetails Csm ON Csm.LineItemID=L.ID
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0
---- Blue Metrics
UNION
Select Section, b.LineItem,DisplayInCSM,'''' Broker,'''' BrokerName,BM_Element,Code BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,
Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],
'+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment
From #TmpBM b
INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem
INNER JOIN tblCSM_ModelDetails Csm ON Csm.BMID=b.code AND Csm.LineItemID=L.ID
WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+'
AND Ord IS NOT NULL
) XX
Left Outer Join tblLiConfig ZZ
On XX.Section=ZZ.Section And XX.LineItem=ZZ.LI And ZZ.Ticker='''+@Ticker+'''
Order by ID,Ord,BM_Code,LineItem,BrokerName'
OK... I'm sorry, but this is a bad answer. A really bad answer. It's the type of answer when you need a fix right now, and don't have time to fix the problem properly.
The true fix is to exclude the rows that have no valid values before combining them into the data set used to create your table. However, without knowing your processes/data much more intimately, I cannot do this. Instead, I have a complete hackjob.
Anyway, from what I can tell
What you can do is to delete the rows from #TmpZacksCons that have all NULLS. The problem is, of course, that those columns are dynamically created.
So, after your existing command
SET @sql='Insert Into #TmpZacksCons (Section, LineItem,Ord,
'+@PeriodCols+'
)
Select b.Section, b.LineItem,Max(Ord)+1 Ord,
'+@AvgSql+'
From #TmpAll_Broker_LI b
Group By b.Section, b.LineItem'
EXEC(@sql)
I suggest a DELETE command to remove one where all the columns are NULL.
UPDATE
(This new version based on updated knowledge - I didn't think COALESCE could return NULL)
The hackjob here is based on the fact we don't know the column names, so we cannot just test if column 1 is null, column 2 is null, etc. So what I'm doing is using a COALESCE expression to find if all columns are NULL, as it will return the first non-NULL value in the list.
SET @sql='DELETE FROM #TmpZacksCons
WHERE COALESCE('+@PeriodCols+',NULL) IS NULL'
EXEC (@sql)
If the COALESCE expression returns a NULL, it means all the columns have NULL values.
Note the additional NULL in the COALESCE is there in case the column list only has 1 column.
PREVIOUS VERSION - NO LONGER RELEVANT
So what I'm doing is to make a 'magic number' which represents that all columns are NULL via the COALESCE expression which finds the first non-NULL value in the list.
So, after your command above, I suggest
SET @sql='DELETE FROM #TmpZacksCons
WHERE COALESCE('+@PeriodCols+',-99999.9999) = -99999.9999'
EXEC (@sql)
If all the values are NULL, the COALESCE will return -99999.9999 which then triggers the delete.
The problem with the above, of course, is that if any of the actual values equal that magic number -99999.9999, it may delete that row.