Mam poniższy stół, próbuję uruchomić zapytanie w T-SQL, aby uzyskać najwcześniejsze i najwcześniejsze koszty dla każdego projektu_id zgodnie z kolumną daty i obliczyć procentowy wzrost kosztów lub zmniejszenia i zwrócić pokaz zestawu danych w Druga tabela (uprościłem tabelę w tym pytaniu).

project_id  date        cost
-------------------------------
123          7/1/17     5000
123          8/1/17     6000
123          9/1/17     7000
123         10/1/17     8000
123         11/1/17     9000
456          7/1/17    10000
456          8/1/17     9000
456          9/1/17     8000
876          1/1/17     8000
876          6/1/17     5000
876          8/1/17    10000
876         11/1/17     8000

Wynik:

(Edytuj: Naprawiono wynik)

project_id        "cost incr/decr pct"
------------------------------------------------
123                80% which is (9000-5000)/5000
456                -20%
876                 0%

Jakieś zapytanie, które biegną, mam duplikaty.

Oto, czego próbowałem:

select distinct 
    p1.Proj_ID, p1.date, p2.[cost], p3.cost,
    (nullif(p2.cost, 0) / nullif(p1.cost, 0)) * 100 as 'OVER UNDER'
from
    [PROJECT] p1
inner join
    (select  
         [Proj_ID], [cost], min([date]) min_date
     from
         [PROJECT]
     group by 
         [Proj_ID], [cost]) p2 on p1.Proj_ID = p2.Proj_ID
inner join
    (select  
         [Proj_ID], [cost], max([date]) max_date
     from
         [PROJECT]
     group by 
         [Proj_ID], [cost]) p3 on p1.Proj_ID = p3.Proj_ID
where 
    p1.date in (p2.min_date, p3.max_date)
1
jax 19 luty 2018, 23:32

3 odpowiedzi

Najlepsza odpowiedź

Niestety, SQL Server nie ma funkcji agregacji first_value(). Ma jednak funkcję analityczną. Możesz więc zrobić:

select distinct project_id,
       first_value(cost) over (partition by project_id order by date asc) as first_cost,
       first_value(cost) over (partition by project_id order by date desc) as last_cost,
       (first_value(cost) over (partition by project_id order by date desc) /
        first_value(cost) over (partition by project_id order by date asc)
       ) - 1 as ratio
from project;

Jeśli cost jest liczbą całkowitą, może być konieczne przekonwertowanie na reprezentację miejsc po przecinku.

2
Gordon Linoff 19 luty 2018, 20:40

Możesz użyć row_number i OUTER APPLY nad top 1 ... przed SQL 2012

select 
min_.projectid,
latest_.cost - min_.cost [Calculation]
from
(select 
 row_number() over (partition by projectid order by date) rn
 ,projectid
 ,cost
 from projectable) min_   -- get the first dates per project
outer apply (
select 
 top 1 
   cost
 from projectable 
where 
projectid = min_.projectid    -- get the latest cost for each project
order by date desc
) latest_
where min_.rn = 1
1
maSTAShuFu 19 luty 2018, 21:00

To może być trochę lepsze

;with costs as (
    select      *,
                ROW_NUMBER() over (PARTITION BY project_id ORDER BY date) mincost,
                ROW_NUMBER() over (PARTITION BY project_id ORDER BY date desc) maxcost
    from        table1
)
select      project_id,
            min(case when mincost = 1 then cost end) as cost1,
            max(case when maxcost = 1 then cost end) as cost2,
            (max(case when maxcost = 1 then cost end) - min(case when mincost = 1 then cost end)) * 100 / min(case when mincost = 1 then cost end)  as [OVER UNDER]
from        costs a 
group by    project_id
0
JamieD77 20 luty 2018, 15:00