Solved

Set Order that i want...

Posted on 2011-03-02
3
188 Views
Last Modified: 2012-05-11
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


                           
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???
0
Comment
Question by:tenriquez199
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35023337
Try this.
SELECT * 
    FROM (  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) 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

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
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

by:Lowfatspread
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question