Hi,
I am using this stored procedure with pivot.If i dont have data i am getting null with this stored procedure.Can u tell me how to handle null.below query is pivot.
I am using like this but getting erro
max(isnull(Head,0)) -
PIVOT
-
(
-
max(Head)
-
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
-
) AS PivotTable'
-
-
-
ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
-
AS
-
DECLARE @query VARCHAR(MAX)
-
BEGIN
-
SET @query = 'SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
-
'FROM
-
(SELECT HeadCount as Head,
-
StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
-
AS SourceTable
-
PIVOT
-
(
-
max(Head)
-
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
-
) AS PivotTable'
-
EXEC(@query)
-
END
1  2240 
Please try this query..
Enjoy Knowledge FREEDOM -
ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
-
AS
-
DECLARE @query VARCHAR(MAX)
-
BEGIN
-
SET @query = 'SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, isNull([' + CONVERT(VARCHAR,@date1,101) + '],0), isNull([' + CONVERT(VARCHAR,@date2,101)+ '],0), isNull([' + CONVERT(VARCHAR,@date3,101) + '],0), isNull([' + CONVERT(VARCHAR,@date4,101) + '],0)' +
-
'FROM
-
(SELECT HeadCount as Head,
-
StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
-
AS SourceTable
-
PIVOT
-
(
-
max(Head)
-
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
-
) AS PivotTable'
-
EXEC(@query)
-
END
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Gaz |
last post by:
I have a table which has 10 columns which make up the secondary key. 1
or more of these columns can be set but the remaining columns in the
secondary key will be null. For example :
id k1 k2...
|
by: John Smith |
last post by:
Hello all:
Does anyone happen to know the best way to handle null values for
DateTimePickers? We have quite a few columns in our db that will not
necissarily have a date for certain entries. ...
|
by: DKode |
last post by:
I find myself writing repetitive functions for handling null values
from my DB like so:
Private Function SetDateNull(ByVal p_date As Object) As Date
If (TypeOf (p_date) Is System.DBNull) Then...
|
by: eddy de boer |
last post by:
Hello,
in my aspx page I have the followong code:
<asp:Repeater id="Repeater1" runat="server">
<ItemTemplate>
....
<%# Server.HtmlDecode((string)DataBinder.Eval(Container.DataItem,"tekst"))...
|
by: Jason Coyne |
last post by:
Here is another programming blog entry. Sorry for the normal friends :)
I recently ran into a problem with a web service I was trying to call
from c# where the web service returned null dates...
|
by: rn5a |
last post by:
How do I pass a NULL value to a field while inserting records in a SQL
Server 2005 DB table using a stored procedure? I tried the following
but it inserts an empty string & not a NULL value:
...
|
by: psycho |
last post by:
how should i hanle null values returned by the datareader
i am using OleDbDataReader for accessing access database.
|
by: jehugaleahsa |
last post by:
Hello:
Is there a way to handle null values for ints, DateTime, etc. in
generated DataTables?
It seems it is not something the Microsoft crew thought of. That is
hard for me to believe.
...
|
by: tonialbrown |
last post by:
I have some code that updates a record's fields based on a selection from a list box (lstDelFrom). The user selects the record from the list box & it copies it into the fields , , etc.
My...
|
by: apache626 |
last post by:
I really need help with this one. I am using a script task to write data pulled from a MS SQL DB to a fixed width file. The script takes the ItemArray and writes the value of the column to the file...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
