I am trying to make temporal decisions over a data set. Let me explain the problem with a simple table:
Time Var1 Var2 Var3 Var4 Var5 Var6 Total
0:00 1.69 3.27 4.80 2.14 0.70 2.14
0:05 2.73 2.73 1.60 1.20 0.46 2.14
0:10 5.45 2.69 4.62 1.15 1.03 4.29 16.28
0:15 2.07 4.74 2.14 1.50 0.43 2.37
0:20 1.71 4.62 1.79 1.29 0.73 2.37
0:25 1.88 3.60 4.00 2.09 0.56 2.25
0:30 5.22 8.57 1.54 2.20 0.48 1.13 14.13
0:35 5.00 5.63 2.93 1.32 1.03 2.05
0:40 4.29 5.29 2.55 1.14 0.38 1.48
What I am trying to do is to sum across all variables in the same time period until I reach 5 or greater than 5. Once I reach greater than 5, I substract 5 to get the remainder and I continue adding the remainder to the next variable in the next time period and so on. For the the result shown in the first total, the path will be the next one:
Time Var1 Var2 Var3 Var4 Var5 Var6 Total
0:00 1.69 3.27 4.80
0:05 1.20
0:10 1.03 4.29 16.28
I am trying to find the path and the total for every time period.
My table is made vertically and not horizontally as shown in the example. So far I have gotten the cumulative_addition for all the variables using:
sum(value) over(partition by variable, time order by variable) as cumulative_addition
But I don't know if this should be the first step I should take. I have also thought about combining lead and lag functions in a query but then I have problems when I try to increment the time period:
Time Variable Value Cumulative_addition
0:00 Var1 1.69 1.69
0:00 Var2 3.27 4.96
0:00 Var3 4.80 9.76
0:00 Var4 2.14 11.90
0:00 Var5 0.70 12.60
0:00 Var6 2.14 14.74
0:05 Var1 2.73 17.47
0:05 Var2 2.73 20.20
0:05 Var3 1.60 21.80
0:05 Var4 1.20 23.00
So in 9.76 I should do 9.76-5=4.76 and jump to 0:05 and in var4 to keep adding until I reach 5 again.
Do you think I can solve this problem by using window functions?
Thank you in advance for your help
This answer provides a loop for the variables and loads them into a table:
create table #t
(
[Time] time
,[Variable] varchar(10)
, [Value] numeric(5,2)
--, [Cumulative_addition] as numeric(5,2)
)
insert into #t
values
('0:00' ,'Var1' ,1.69) --1.69)
,('0:00' ,'Var2' ,3.27) -- 4.96
,('0:00' ,'Var3' ,4.80 ) --9.76
,('0:00' ,'Var4' ,2.14 ) --11.90
,('0:00' ,'Var5' ,0.70 ) --12.60
,('0:00' ,'Var6' ,2.14 )--14.74
,('0:05' ,'Var1' ,2.73) --17.47
,('0:05' ,'Var2' ,2.73 ) --20.20
,('0:05' ,'Var3' ,1.60 ) --21.80
,('0:05' ,'Var4' ,1.20 ) --23.00
declare @v as numeric(7,4)
declare @total numeric(7,4) = 0
declare @calc numeric(7,4) = 0
declare @time time ='0:00'
declare @i int = 1
create table #answers (variable int, [Time] time, Value numeric(7,4))
while(@i<=6)
begin
select @v=[Value]
from #t where time = @time and [Variable] = 'Var' + cast(@i as varchar(1))
set @calc=@calc+@v
set @total = @total+@v
insert into #answers
values(@i,@time,@v)
if @calc>=5
Begin
set @time = dateadd(mi,5,@time)
set @calc = @calc-5
End
set @i=@i+1
set @v=null
end
select *
from #answers
drop table #t,#answers
Results:
variable Time Value
1 00:00:00.0000000 1.6900
2 00:00:00.0000000 3.2700
3 00:00:00.0000000 4.8000
4 00:05:00.0000000 1.2000
5 00:10:00.0000000 NULL
6 00:10:00.0000000 NULL