Процент SQL Server 2005

Question

Я использую SQL Server 2005. У меня есть эти таблицы:

CREATE TABLE [dbo].[Proyectos2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Proyecto] [varchar](255) NULL
)
CREATE TABLE [dbo].[Clientes2](

[Vendedora] [varchar](255) NULL,
[Proyecto] [varchar](255) NULL  )



 insert into proyectos2
values
(
'Proyecto1'
)
insert into proyectos2
values
(
'Proyecto2'
)
insert into clientes2
values
(
'Jessica','Proyecto1'
)
insert into clientes2
values
(
'Jessica','Proyecto1'
)
insert into clientes2
values
(
'Mariel','Proyecto2'
)

тогда этот запрос:

    Declare @Names As Varchar(Max), @strSQL Varchar(Max);
DECLARE @sum AS varchar(MAX);
Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';
Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
    Vendedora
    with rollup';
SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) ' +
              'SELECT Vendedora,cast(floor(Proyecto1) as varchar) Proyecto1,cast(floor(Proyecto2) as varchar)Proyecto2,Total FROM CTE UNION ALL ' +
              'SELECT ''Porcentaje'',cast( ' + REPLACE(@Names, '],[', '] / Total as varchar) Proyecto1, cast([') + ' / Total as varchar), ''100''' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';
print @strSQL;
Execute (@strSQL);
GO

Если вы выполняете этот код, это отображается:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------------2-------------------0--------------2
Mariel--------------0-------------------1--------------1
Grand Total---------2-------------------1--------------3
Percentage----------0.666666666666------0.333333333333---100

Я хотел бы что-то вроде этого:

Vendedora------Proyecto1--------------Proyecto2-----Total
Jessica-------------2-------------------0--------------0.66
Mariel--------------0-------------------1--------------0.33
Grand Total---------2-------------------1--------------1
Percentage----------0.66--------------0.33------------------100

То есть две цифры после запятой для последней строки, а также для суммы каждой строки.

Есть ли способ сделать это?

Благодарю.

Answer 1

Я сосредотачиваюсь на вашем высказывании, что вам нужно, чтобы сумма каждой строки была в процентах от итогового итогового итога.

Для того, чтобы сделать это, вам нужно знать, что такое общий итог, прежде чем производить каждую строку. На самом деле лучше всего делать это в инструментах отчетности, таких как SSRS, ActiveReports или Crystal Reports.

Но так как вы спросили: это подход, который я бы выбрал:

ETA: переписано, чтобы быть более конкретным. Обратите внимание, что это все еще пример и его необходимо адаптировать к вашей ситуации.

WITH CTE AS ( <@strSQL> ),
     CTE2 AS (
       SELECT Vendedora, 
              cast(floor(Proyecto1) as varchar) Proyecto1,
              cast(floor(Proyecto2) as varchar) Proyecto2,
              Total 
       FROM   CTE),
       SELECT   Vendedora,
              Proyecto1,
              Proyecto2,
              Total / ( SELECT SUM(Total) FROM CTE2 ) Percentage
       FROM     CTE2    
       UNION  ALL
       SELECT 'Porcentaje',
               <your two columns>
               '100'
       FROM   CTE

Но это действительно очень грязно: лучше всего по возможности возвращать данные и выполнять суммирование и процентные вычисления в инструменте отчетности.

Answer 2

Умножьте нужные значения в процентах (например, 0,33) на 100,0, а затем приведите их к int, чтобы усечь значение в процентах без десятичных разрядов.

cast(percentage * 100.0 as int) as percentage

Answer 3

Я сделал это:

Declare @Names As Varchar(Max), @strSQL Varchar(Max), @PercentNames varchar(max) ;
DECLARE @sum AS varchar(MAX);

Select @Names = Stuff((Select ',' + QuoteName(Proyecto)
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');

Select @PercentNames = Stuff((Select ',' + 'CAST(' + QuoteName(Proyecto)  + '* 100.0/Total AS DECIMAL(10,2))'
From Proyectos2 Group By Proyecto Order by Proyecto For XML Path('')), 1, 1, '');
Select @sum = Stuff(
(
Select ', 1.0 * SUM(' + QuoteName(Proyecto) + ') as ' + QuoteName(Proyecto)
From Proyectos2 
Group By Proyecto 
Order by Proyecto For XML Path('')
), 1, 1, '') + ', SUM(Total) as Total';

Set @strSQL = 'select case when grouping(Vendedora) = 1 then ''Grand Total'' else Vendedora end as Vendedora, ' + @sum + N'
from (
Select Vendedora, ' + @Names +  ', (' + REPLACE(@Names, '],[', '] + [') + ') as Total' + 
' From (Select Vendedora, Proyecto From Clientes2) as P 
 Pivot (Count(Proyecto) For Proyecto in (' + @Names + ')) As Pvt
) as S
group by
    Vendedora
    with rollup';

SET @strSQL = 'WITH CTE AS ( ' + @strSQL + ' ) 
              SELECT *, CAST(Total * 100.0 / (select Total from cte where Vendedora = ''Grand Total'') as Decimal(10,2)) as [Percent]

              FROM CTE UNION ALL 
              SELECT ''Porcentaje'', ' + @PercentNames + ', ''100'', 100' +
              '  FROM CTE ' +
              ' WHERE Vendedora = ''Grand Total'';';

print @strSQL;
Execute (@strSQL);
GO

Работает нормально.

Но у меня все еще есть проблема: я хочу удалить цифры после десятичной точки. Что я могу сделать в этом случае?