Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Set Order that i want...

Posted on 2011-03-02
3
Medium Priority
?
192 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 41

Accepted Solution

by:
Sharath earned 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

963 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