Solved

Set Order that i want...

Posted on 2011-03-02
3
182 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
3 Comments
 
LVL 40

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now