unable to get rows in required order

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unable to get rows in required order

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-11-03 : 10:52:19

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 11:03:51

You should show your output to make more clear what the problem is.
If you don't need A in the first row then what should be in the first row?

No, you're never too old to Yak'n'Roll if you're too young to die.

naresh0407
Starting Member

30 Posts

Posted - 2010-11-03 : 11:22:27

In the stored procedure u can see query with Select statement Forecatsed HC as per Hiring plan .I want this result as first row.

Agents scheduled as per schedule in 2nd row.

When i execute this stored procedure i am getting result like this

HeadCount 9/26/2010 10/3/2010 10/10/2010 10/17/2010

Agents Scheduled 46 46 46 0

Forecasted HC 46 46 46 0

But i want Forecasted HC in first row and Agents Schedule row in 2nd row.

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) + ']' +

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 11:27:59

Have you tried to change the order of queries in your union?

No, you're never too old to Yak'n'Roll if you're too young to die.

naresh0407
Starting Member

30 Posts

Posted - 2010-11-03 : 11:36:20

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 11:48:23

Just add after the last ) AS PivotTable'
an ORDER BY HeadCount DESC

So that the last line looks like this:
) AS PivotTable ORDER BY HeadCount DESC'

No, you're never too old to Yak'n'Roll if you're too young to die.

naresh0407
Starting Member

30 Posts

Posted - 2010-11-03 : 12:05:05

Hi,

You didn'get my point get exactly.

In the STored procedure i have 2 select statements with union in between.

I want second select statement as 1st row and 1st select statement as 2nd row.

Because of char(39) in select statement in query i am getting result in alphabetic order.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 13:23:18

But I have tested it and it worked.
Now another Test is also working, see this:

CREATE TABLE SAR_HeadCount( 
HeadCount int, 
CampaignID int, 
WeekStartDate datetime, 
)

Insert into SAR_HeadCount(HeadCount,CampaignID,WeekStartDate)
select '46','1','1/8/2010' union all
select '46','1','8/8/2010' union all
select '46','1','15/8/2010' union all
select '46','1','22/8/2010' 

DECLARE @query VARCHAR(MAX)

declare @date1 DATETIME = '1/8/2010', 
@date2 DATETIME ='8/8/2010' , 
@date3 DATETIME ='15/8/2010', 
@date4 DATETIME ='22/8/2010',
@CampaignId int = 1

SET @query = 'SELECT * FROM (SELECT '+ CHAR(39) + 'Agents Scheduled as per Schedule' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,104) + '], [' + CONVERT(VARCHAR,@date2,104)+ '], [' + CONVERT(VARCHAR,@date3,104) + '], [' + CONVERT(VARCHAR,@date4,104) + ']' +
'FROM
(SELECT HeadCount as Head,
WeekStartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
AS SourceTable
PIVOT
(
max(Head)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,104) + '], [' + CONVERT(VARCHAR,@date2,104)+ '], [' + CONVERT(VARCHAR,@date3,104) + '], [' + CONVERT(VARCHAR,@date4,104) + ']' + ')
) AS PivotTable
union
SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,104) + '], [' + CONVERT(VARCHAR,@date2,104)+ '], [' + CONVERT(VARCHAR,@date3,104) + '], [' + CONVERT(VARCHAR,@date4,104) + ']' +
'FROM
(SELECT HeadCount as Head,
WeekStartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
AS SourceTable
PIVOT
(
max(Head)
FOR WeekStartDate IN (' + '[' + isnull(CONVERT(VARCHAR,@date1,104),0) + '], [' + CONVERT(VARCHAR,@date2,104)+ '], [' + CONVERT(VARCHAR,@date3,104) + '], [' + isnull(CONVERT(VARCHAR,@date4,104),0) + ']' + ')
) AS PivotTable)dt order by HeadCount DESC' 
EXEC(@query)
drop table SAR_HeadCount

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources