November 3, 2010 at 8:55 am
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/22/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
November 3, 2010 at 9:40 am
You'll need to add a sequencing column that forces the sort order you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2010 at 9:42 am
can u give me example on how to do that.
November 3, 2010 at 1:19 pm
As it sits there is no order by anywhere in this so there is no guarantee about what order it will be returned in.
On another point, this looks like it would be about a million times easier to maintain if you just ran the select statement instead of going through all the hoops to declare a variable immediately fill it with nothing more than a select statement and then execute the string. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 3, 2010 at 1:47 pm
SeanLange (11/3/2010)
As it sits there is no order by anywhere in this so there is no guarantee about what order it will be returned in.On another point, this looks like it would be about a million times easier to maintain if you just ran the select statement instead of going through all the hoops to declare a variable immediately fill it with nothing more than a select statement and then execute the string. Just my 2¢.
It's almost certainly a dynamic pivot, which currently has to be dynamic SQL because the T-SQL pivot operator is pretty much junk.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2010 at 1:49 pm
naresh0407 93367 (11/3/2010)
can u give me example on how to do that.
select case when MyColumn = 'Header' then 1 when MyColumn = 'Other' then 2 else 3 end as Seq, ...
from (derived table) as DT
order by Seq;
It'll look something like that. You build the main query in a CTE or other derived table, and then you use a Case statement to assign a value to the rows that you can Order By.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply