Solved

Set Order that i want...

Posted on 2011-03-02
3
186 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 26

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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