sort order help urgent!!!!!!

Question

Post reply

sort order help urgent!!!!!!

SSM-140546

Old Hand

Points: 365
More actions
June 30, 2005 at 1:00 pm

#362996

Hi,

I need to do the following sort order is a parameter to procedure..I have a dynamic sql to do that.After that I group by.Then I need to calculate subtotals for which I use RollUp.

When I use Roolup my sort order is lost?why is it so?

thanks,

SSM

thanx...,

SSM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

June 30, 2005 at 1:07 pm

#570083

Can you post the proc code??

Also why use dynamic sql in this case?

SSM-140546 Old Hand Points: 365 More actions · Answer 2

CREATE PROCEDURE dbo.prc_indicator_get_machinehours

@cycleName varchar(50),

@Framework varchar(50),

@toolName varchar(50),

@sortOrder varchar(200)

AS

SET ANSI_NULLS OFF

SET NOCOUNT ON

declare @sql varchar(5000)

declare @sort varchar(300)

set @sort = ""+ @sortOrder +""

set @sql=''

set @sql='SELECT [view_machine_hours].[cycleName], [view_machine_hours].[framework], [view_machine_hours].[toolName], [view_machine_hours].[targetName] ,[view_machine_hours].[taskName], [view_machine_hours].[WW], [view_machine_hours].[ExecDate],[view_machine_hours].[ExecHour],[MachineHours]

FROM [view_machine_hours] where 1=1'

if @cycleName <> '0'

begin

set @sql = @sql + ' and [view_machine_hours].cycleName like ''%'+ @cycleName + '%'''

end

if @Framework <> '0'

begin

set @sql = @sql + ' and [view_machine_hours].framework like ''%'+ @Framework + '%'''

end

if @toolName <> '0'

begin

set @sql = @sql + ' and [view_machine_hours].toolName like ''%'+ @toolName + '%'''

end

set @sql=@sql + @sort

print(@sql)

CREATE TABLE #temp_machine_hours (

[cycleName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[framework] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[toolName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[targetName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[taskName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[WW] [int] NULL ,

[execDate] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[execHour] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[MachineHours] [decimal] (20,10) NULL

)

Insert into #temp_machine_hours Exec(@sql)

SELECT [cycleName], [framework], [toolName], [targetName],[taskName], [WW], [execDate],[execHour], Sum([MachineHours]) as [MachineHours] FROM #temp_machine_hours

Group by [cycleName], [framework], [toolName], [targetName] ,[taskName], [WW], [ExecDate],[ExecHour]

with rollup

GO

thanx...,

SSM

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Ray M

SSC-Insane

Points: 21093

June 30, 2005 at 2:34 pm

#570156

How about a create table statement for [view_machine_hours]

and some example data, and what you want the results to look like.

http://www.aspfaq.com/etiquette.asp?id=5006

SSM-140546 Old Hand Points: 365 More actions · Answer 4

REATE VIEW dbo.view_machine_hours

AS

SELECT TOP 100 PERCENT cycleName, framework, toolName, taskName, DATEPART(ww, execDate) AS WW, execDate, execHour,

SUM(CAST(StressTime AS decimal) / 3600) AS MachineHours, targetName

FROM dbo.tbl_execution_time_hourly

WHERE (cycleName IN

(SELECT [tbl_Cycle].[cycleName]

FROM [tbl_Cycle]

WHERE [tbl_Cycle].[deleted] = 0 AND [tbl_Cycle].[dateClosed] IS NULL))

GROUP BY cycleName, framework, toolName, targetName, taskName, DATEPART(ww, execDate), execDate, execHour

ORDER BY cycleName, framework, toolName, targetName, taskName, DATEPART(ww, execDate), execDate, execHour

thanx...,

SSM

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

June 30, 2005 at 2:55 pm

#570177

We need to see the expected output too. Help us help you.

SSM-140546 Old Hand Points: 365 More actions · Answer 6

cycle	framework	tool	target	task	week	date	hour	machine hours
VFM_Random_PState	diablo	Diablo_V1_8	sv-pt15	summary	24	6/9/2005	16	0.528611
VFM_Random_PState	diablo	Diablo_V1_8	sv-pt15	summary	24	6/9/2005	17	0.977222
VFM_Random_PState	diablo	Diablo_V1_8	sv-pt15	summary	24	6/9/2005	18	0.983055
VFM_Random_PState	diablo	Diablo_V1_8	sv-pt15	summary	24	6/9/2005	19	0.488055
						6/9/2005		2.97
VFM_Random_PState	diablo	Diablo_V1_8	sv-pt15	summary	24	6/10/2005	10	0.781944
VFM_Random_PState	diablo	Diablo_V1_8	sv-pt15	summary	24	6/10/2005	11	1.023888
					24			4.78
				summary				4.78
			sv-pt15					4.78
		Diablo_V1_8						4.78
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb37	summary	27	6/29/2005	11	0.510555
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb37	summary	27	6/29/2005	12	0.9975
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb37	summary	27	6/29/2005	13	1.000555
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb37	summary	27	6/29/2005	14	0.4175
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb33	summary	27	6/29/2005	16	0.509166
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb37	summary	27	6/29/2005	16	0.780833
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb37	summary	27	6/29/2005	17	0.965555
VFM_Random_PState	diablo	Diablo_V1_8a	sv-mb33	summary	27	6/29/2005	17	0.983055
						6/29/2005		6.16

something like this..

The problem is I get the table sorted in the order in which is required in the temp table.When I later group by & rollup the temp table,the order is lost.

thanx...,

SSM