Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Set Order that i want...

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

671 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