tenriquez199
asked on
Set Order that i want...
Hello
Well. I have the next query
SELECT [Division],SUM([VIngD])as SumaDia, (SUM([CIngM])/27) as CuotaDia,((case SUM([CIngM]) when 0 then 0 else SUM([VIngD]) end)/((case SUM([CIngM]) when 0 then 1 else SUM([CIngM]) end)/27) ) as PorcientoDia,SUM([VIngM])a s SumaMes,((SUM([CIngM])/27) *1 ) as CuotaTransMes , ((case SUM([CIngM]) when 0 then 0 else SUM([VIngD]) end)/(((case SUM([CIngM]) when 0 then 1 else SUM([CIngM]) end)/27)*1 ) ) as PorcientoTransMes,SUM([CIn gM]) as CuotaMes,(SUM(case [CIngM] when 0 then 0 else [VIngM] end)/SUM(case [CIngM]when 0 then 1 else [CIngM] end)) as PorcientoMes,(SUM([CIngM]) -SUM([VIng M]))as xCubrir,(SUM([CIngM])/SUM( [CVolM])) as Aut,(SUM([VIngM])/SUM([VVo lM])) as Vta, SUM([CTIngP]) as CuotaTrans2010, ((Sum([VIngM]))/(SUM([CTIn gP]))-1)as Creci,SUM([CIngP]) as CuotaMes2010, (SUM(case [CVolP] when 0 then 0 else [CIngP] end)/SUM (case [CVolP] when 0 then 1 else [CVolP] end)) as PrecioMin, SUM([VIngA]) as SumaAnual, SUM([CIngA]) as CuotaAnual, (SUM(case [CIngA] when 0 then 0 else [VIngA] end)/SUM(case [CIngA]when 0 then 1 else [CIngA] end)) as PorcientoAnual FROM [Sicom].[dbo].[xDivision] where mes='3'
group by Division
with this I have
Division SumaDia CuotaDia PorcientoDia
AUTOSERVICIO GOB DF 0,00 8689,7148 0,00
AUTOSERVICIO NACIONALES -119698,43 244644,6388 -0,4892
CENTRO 11371,25 12115,0807 0,9386
CENTROS DE CONSUMO 233609,97 82190,6166 2,8422
CLIENTES ESPECIALES 39733,22 67585,4051 0,5878
MARCAS PROPIAS 99548,91 0,00 0,00
NORTE 122674,34 150979,777 0,8125
PACIFICO 341273,69 232171,3048 1,4699
SURESTE 293923,29 46674,1403 6,2973
but i need that the order about of Division be like this
AUTOSERVICIO CTRO.DIST.
AUTOSERVICIO GOB DF
CENTROS DE CONSUMO
CENTRO
NORTE
PACIFICO
SURESTE
CLIENTES ESPECIALES
MARCAS PROPIAS
I try this i it's work but when i change the type of the date it doesnt work
ORDER BY CASE Division WHEN 'AUTOSERVICIO NACIONALES' THEN 0.1
WHEN 'AUTOSERVICIO GOB DF' THEN 0.2 WHEN 'CENTROS DE CONSUMO' THEN 0.3 WHEN 'CENTRO' THEN 0.4 WHEN 'NORTE' THEN 0.5 WHEN 'PACIFICO' THEN 0.6 WHEN 'SURESTE' THEN 0.7 WHEN 'CLIENTES ESPECIALES' THEN 0.8 WHEN 'MARCAS PROPIAS' THEN 0.9 ELSE Division END
I put the same type that it have before ... but now no works
Some Idea???
Well. I have the next query
SELECT [Division],SUM([VIngD])as SumaDia, (SUM([CIngM])/27) as CuotaDia,((case SUM([CIngM]) when 0 then 0 else SUM([VIngD]) end)/((case SUM([CIngM]) when 0 then 1 else SUM([CIngM]) end)/27) ) as PorcientoDia,SUM([VIngM])a
group by Division
with this I have
Division SumaDia CuotaDia PorcientoDia
AUTOSERVICIO GOB DF 0,00 8689,7148 0,00
AUTOSERVICIO NACIONALES -119698,43 244644,6388 -0,4892
CENTRO 11371,25 12115,0807 0,9386
CENTROS DE CONSUMO 233609,97 82190,6166 2,8422
CLIENTES ESPECIALES 39733,22 67585,4051 0,5878
MARCAS PROPIAS 99548,91 0,00 0,00
NORTE 122674,34 150979,777 0,8125
PACIFICO 341273,69 232171,3048 1,4699
SURESTE 293923,29 46674,1403 6,2973
but i need that the order about of Division be like this
AUTOSERVICIO CTRO.DIST.
AUTOSERVICIO GOB DF
CENTROS DE CONSUMO
CENTRO
NORTE
PACIFICO
SURESTE
CLIENTES ESPECIALES
MARCAS PROPIAS
I try this i it's work but when i change the type of the date it doesnt work
ORDER BY CASE Division WHEN 'AUTOSERVICIO NACIONALES' THEN 0.1
WHEN 'AUTOSERVICIO GOB DF' THEN 0.2 WHEN 'CENTROS DE CONSUMO' THEN 0.3 WHEN 'CENTRO' THEN 0.4 WHEN 'NORTE' THEN 0.5 WHEN 'PACIFICO' THEN 0.6 WHEN 'SURESTE' THEN 0.7 WHEN 'CLIENTES ESPECIALES' THEN 0.8 WHEN 'MARCAS PROPIAS' THEN 0.9 ELSE Division END
I put the same type that it have before ... but now no works
Some Idea???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ORDER BY CASE Division WHEN 'AUTOSERVICIO NACIONALES' THEN 0.1
WHEN 'AUTOSERVICIO GOB DF' THEN 0.2 WHEN 'CENTROS DE CONSUMO' THEN 0.3 WHEN 'CENTRO' THEN 0.4 WHEN 'NORTE' THEN 0.5 WHEN 'PACIFICO' THEN 0.6 WHEN 'SURESTE' THEN 0.7 WHEN 'CLIENTES ESPECIALES' THEN 0.8 WHEN 'MARCAS PROPIAS' THEN 0.9 ELSE 999 END,division
WHEN 'AUTOSERVICIO GOB DF' THEN 0.2 WHEN 'CENTROS DE CONSUMO' THEN 0.3 WHEN 'CENTRO' THEN 0.4 WHEN 'NORTE' THEN 0.5 WHEN 'PACIFICO' THEN 0.6 WHEN 'SURESTE' THEN 0.7 WHEN 'CLIENTES ESPECIALES' THEN 0.8 WHEN 'MARCAS PROPIAS' THEN 0.9 ELSE 999 END,division
Changing that to an "ELSE 1" or some number that is greater than all your others and then adding just the column DIVISION as the second sort criteria should solve your problem.