Hi,
This is my table.When u run the stored procedure we will get first row as AgentsScheduled as per schdule as first row and Forecasted HC as per hiring plan row in 2nd row but i want Forecasted HC as first row. -
CREATE TABLE SAR_HeadCount(
-
HeadCount int,
-
CampaignID int,
-
WeekStartDate datetime,
-
)
-
-
Insert into SAR_HeadCount(HeadCount,CampaignID,WeekStartDate)
-
select '46','1','8/1/2010' union all
-
select '46','1','8/8/2010' union all
-
select '46','1','8/15/2010' union all
-
select '46','1','8/22/2010'
-
Please execute this stored procedure -
exec SAR_Sp_GetForecastedHC '8/1/2010','8/8/2010','8/15/2010','8/2/2010',1
-
-
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) + 'Agents Scheduled as per Schedule' + 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
-
union
-
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 (' + '[' + isnull(CONVERT(VARCHAR,@date1,101),0) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + isnull(CONVERT(VARCHAR,@date4,101),0) + ']' + ')
-
) AS PivotTable'
-
EXEC(@query)
-
END
1 1465
Could you please try this altered SP...
I just have modified this by adding 'order by HeadCount desc' at end. its working for the purpose..
have good day... - 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) + 'Agents Scheduled as per Schedule' + 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
-
union
-
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 (' + '[' + isnull(CONVERT(VARCHAR,@date1,101),0) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + isnull(CONVERT(VARCHAR,@date4,101),0) + ']' + ')
-
) AS PivotTable order by HeadCount desc'
-
EXEC(@query)
-
print @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: 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: NareshN |
last post by:
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...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |