Solved

# Set Order that i want...

Posted on 2011-03-02
Medium Priority
195 Views
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])as 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([CIngM]) 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([VIngM]))as xCubrir,(SUM([CIngM])/SUM([CVolM])) as Aut,(SUM([VIngM])/SUM([VVolM])) as Vta, SUM([CTIngP]) as CuotaTrans2010, ((Sum([VIngM]))/(SUM([CTIngP]))-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

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???
0
Question by:tenriquez199

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 35023337
Try this.
``````SELECT *
FROM (  SELECT [division],
((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])                           AS 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([cingm])                           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([vingm]))          AS xcubrir,
(Sum([cingm]) / Sum([cvolm]))          AS aut,
(Sum([vingm]) / Sum([vvolm]))          AS vta,
Sum([ctingp])                          AS cuotatrans2010,
((Sum([vingm])) / (Sum([ctingp])) - 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) t1
ORDER BY CASE division
WHEN 'AUTOSERVICIO NACIONALES' THEN 1
WHEN 'AUTOSERVICIO GOB DF' THEN 2
WHEN 'CENTROS DE CONSUMO' THEN 3
WHEN 'CENTRO' THEN 4
WHEN 'NORTE' THEN 5
WHEN 'PACIFICO' THEN 6
WHEN 'SURESTE' THEN 7
WHEN 'CLIENTES ESPECIALES' THEN 8
WHEN 'MARCAS PROPIAS' THEN 9
ELSE 10
END,
division
``````
0

LVL 27

Expert Comment

ID: 35023391
It is the " ELSE Division" at the end that is your problem, it errors trying to convert the string value to a numeric value (type of a case statement is determined by the first "THEN" value and all other must match it).
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.
0

LVL 50

Expert Comment

ID: 35023848
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
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.