샘플데이터는 다음과 같습니다.
stat_date
prj
g_1
g_2
g_3
g_4
2008-07-01
1
100
23
65
656
2
12
3
6
54
33
0
2008-07-02
123
44
89
767
15
7
9
78
34
2008-07-03
200
56
433
8976
45
11
322
66
67
55
77
30
32
-- 집계자료라서 데이타량이 적으므로 그냥 간단하게 서브쿼리를 이용하셔도 될듯 하네요.
if object_id('res') is not null drop table res go
create table res (idx int identity(1,1) , stat_date varchar(10) , prj int , g_1 int , g_2 int , g_3 int , g_4 int
) go
insert into res ( stat_date , prj , g_1 , g_2 , g_3 , g_4 )
select '2008-07-01' , 1 ,100 ,23 ,65 ,656 union select '2008-07-01' ,2 ,12 ,3 ,6 ,54 union select '2008-07-01' ,3 ,23 ,12 ,33 ,0 union select '2008-07-02' ,1 ,123 ,44 ,89 ,767 union select '2008-07-02' ,2 ,15 ,7 ,9 ,78 union select '2008-07-02', 3 ,34 ,33 ,44 ,0 union select '2008-07-03', 1 ,200 ,56 ,433 ,8976 union select '2008-07-03', 2 ,45 ,7 ,11 ,322 union select '2008-07-03', 3 ,66 ,67 ,55 ,0
select a.stat_date, a.prj , a.g_1 - isnull(( select g_1 from res where prj = a.prj and stat_date = (select max(stat_date) from res where stat_date < a.stat_date and prj = a.prj )),0) g_1 , a.g_2 - isnull(( select g_2 from res where prj = a.prj and stat_date = (select max(stat_date) from res where stat_date < a.stat_date and prj = a.prj )),0) g_2 , a.g_3 - isnull(( select g_3 from res where prj = a.prj and stat_date = (select max(stat_date) from res where stat_date < a.stat_date and prj = a.prj )),0) g_3 , a.g_4 - isnull(( select g_4 from res where prj = a.prj and stat_date = (select max(stat_date) from res where stat_date < a.stat_date and prj = a.prj )),0) g_4 from res a
