如何计算百分比

Question

表1

Date  id salary 

01/2012 001 1500 
01/2011 001 750 
01/2010 001 500 

从表1，我想打一个百分比为datewise

预计输出

Date id salary percentage 

01/2012 001 1500 100% 
01/2011 001 750 50% 
01/2010 001 500 0% 

条件

01/2010 salary is 500, then 01/2011 salary is 750, so 50 percentage increased from 01/2010 salary 
01/2011 salary is 750, then 01/2012 salary is 1500, so 100 percentage increase from 02/2011 salary 

如何制作一个查询计算百分比。

需要查询帮助

Answer 1

正如@richardtallent所说，有两个部分：（1）找到以前的工资，（2）计算百分比。但是由于您使用的是sqlserver 2k，CTE不受支持。我用做下列方式：

对于（2）我通常有两个功能创建：百分比和增量：

CREATE FUNCTION [dbo].[percentage] (@a float(53), @b float(53)) 
RETURNS float(53) 
BEGIN 
    RETURN (CASE WHEN COALESCE(@b,0)=0 THEN NULL ELSE @a/@b END)*100. 
END 

而且

CREATE FUNCTION [dbo].[increment] (@a float(53), @b float(53)) 
RETURNS float(53) 
BEGIN 
    RETURN dbo.percentage(@a,@b)-100. 
END 

：（1）你可以使用然后你有它。

SELECT S.date, S.id, S.salary, 
     dbo.increment((SELECT TOP 1 salary 
         FROM table1 
         WHERE id=S.id AND date<S.date 
         ORDER BY date DESC), 
        salary) AS percentage 
FROM table1 S 

Answer 2

有两个部分对这个问题：（1）寻找对于任何给定的工资以前的薪水，和（2）计算百分比。

第一部分：

WITH salarieswithprevious AS (
    SELECT date, id, salary, 
     COALESCE(SELECT salary FROM table t2 WHERE t1.id=t2.id AND t1.date > t2.date 
     AND NOT EXISTS(SELECT NULL FROM table t3 WHERE t3.id=t1.id 
      AND t3.date > t2.date AND t3.date < t1.date), salary) AS previoussalary 
    FROM 
     table t1 
    ) 

这与薪水之前最近的每个薪金匹配。如果没有以前的薪水，它会将价值与当前的工资相结合，这意味着该年的“加薪”将为0％。

第二部分：

SELECT date, id, salary, 
    ((salary - previoussalary)/previoussalary * 100) AS PctIncrease 
FROM 
    salarieswithprevious 

这确实比较容易的部分，简单的百分比数学。

Answer 3

在我看来，更好的你去光标，先通过改变表中创建列，并通过光标表

declare @date datetime,@id int,@salary numeric,@percentage decimal(5,2) 
declare SalCursor cursor static 
    FOR SELECT date,id,Salary FROM table1 
    open SalCursor 
    fetch last FROM SalCursor into @date,@id,@salary 
    while @@fetch_status<>-1 
    begin 
    --code to calculate percentage goes here 
    Update table1 set [email protected] where [email protected] 
    end 

希望它可以给你一些粗略的想法读取最后一行... 所有最好的..

Answer 4

你可以通过执行类似的CTE做到这一点：

WITH CTETable (Row, Date, id, salary) AS 
(
    SELECT Row_Number() over (order by id,Date) AS Row , 
    Date, 
     id, 
    salary 
    FROM sourceTable 
) 
SELECT 
    T1.Row, 
    T1.Date, 
     T1.id 
    T1.salary, 
    CASE 
        -- First row in table, return 0 for Pct 
     WHEN T1.Row = 1 THEN 0 
        -- Switching ids, return 0 for Pct 
        WHEN T1.id <> (SELECT T2.id FROM CTETable AS T2 
           WHERE T2.Row = T1.Row - 1) THEN 0 
        -- else subtract the prior row's salary from the current row 
        -- and divide by the prior row's salary 
     ELSE (T1.salary - (SELECT T2.salary FROM CTETable AS T2 
       WHERE T2.Row = T1.Row - 1) * 1.0)/
      ((SELECT T3.salary FROM CTETable AS T3 
       WHERE T3.Row = T1.Row - 1) * 1.0) 
    END * 100 AS Pct 
FROM CTETable AS T1 

Answer 5

试试这个

create table #tr(dates datetime,id int,salary int) 
insert into #tr values('01/01/2012' , 001 , 1500) 
insert into #tr values('01/01/2011' ,001, 750) 
insert into #tr values('01/01/2010' , 001 , 500); 

WITH tablename as 
(

SELECT ROW_NUMBER()over(PARTITION by t1.dates order by t1.dates)rno, T1.*,isnull((cast((T1.salary - isnull(T2.salary,T1.salary))as decimal(16,3))/T2.salary),0)*100 as 'Percentage' FROM #tr T1 
left JOIN #tr T2 on T1.id = T2.ID and T1.dates >T2.dates 

) 

SELECT * FROM tablename where rno = 1  

drop table #tr 

Answer 6

试试这个：它更加动态：

create table #tr(dates datetime,id int,salary int) 
insert into #tr values('01/01/2012' , 001 , 1500) 
insert into #tr values('01/01/2011' ,001, 750) 
insert into #tr values('01/01/2010' , 001 , 500) 
insert into #tr values('01/01/2011' ,002, 750) 
insert into #tr values('01/01/2010' , 002 , 500) 

with T as (
select ID, dates incDate, Salary, ROW_NUMBER() over(partition by id order by id, Dates) AS rn from #tr) 
select T.id,T1.salary ,T.incdate,(T1.Salary-T.salary)*100/T.Salary as PercHike from T join T T1 on T.id=T1.id and T.rn=(T1.rn-1) 
UNION 
select id,min(Salary) as Salary ,min(dates) as Incdate , 0 as PercHike from #tr group by Id 
ORDER BY ID , incDate 

drop table #tr 

在场景中ID不同，早期的建议解决方案进行行的事。