SQLTeam.com | Weblogs | Forums

Adding previous or next row values based on condition


#1

Please refer to the diagram. This is done in Excel with SumIffs but I want to do this in SQL. The Value Expected should be calculated per DateTime based on a condition. The condition is that for the current row, look to either previous and/or next rows if the Time is with in 24 hours. In the example, for the first row, the DateTime column shows that the 2nd and 3rd DateTime values are within 24 hours of the 1st. So, I take 2+1+3 = 6 in the Value Expected column. For the 3rd row, (12/29/16 9am), I see the previous and next rows if they are with in 24 hours of (12/29/16 9am). In this case it would be the previous 3 row and the next 1 row. So I add 2+1+3+5 to get Value expected. If the 5th and/or 6th row were with in 24 hours, I would have included them for the total. Can Anybody suggest a simple way of doing this in SQL? I have tried Lag() and Lead() functions so far and isn't giving me what i want. I appreciate any help. Thanks.


#2

Can you post the SQL you have, DDL for the tables and insert statement(s) for the data please.


#3

I haven't gone that deep into it. Can we tackle it at a basic level? The select I have looks like this:
select
b.[DateTime]
,cast(b.[EFT]/60.0 as decimal(8,1)) Value
,DATEDIFF(HOUR, Lag(b.[DateTime], 1) OVER(ORDER BY b.[DateTime]), b.[DateTime]) 'Hours'
,case when DATEDIFF(HOUR, Lag(b.[DateTime], 1) OVER(ORDER BY b.[DateTime]), b.[DateTime]) < 24 then EFT/60.0+Lag(cast(b.EFT/60.0 as decimal(8,1)), 1) OVER(ORDER BY b.EFT) else b.EFT/60.0 end [Value Expected]

  from [dbo].[Records] b

#4
SELECT
b.DateTime
,b.value + ISNULL((SELECT SUM(c.value) FROM table_name c 
WHERE c.DateTime >= DATEADD(HOUR, -24, b.DateTime) AND 
    c.DateTime < DATEADD(HOUR, 24, b.DateTime) AND 
    b.DateTime <> c.DateTime), 0)
FROM table_name b

#5

This did it. Thank you so much Scott!


#6

እዮብ,

this is what is meant by providing sample data. ታላቆችህን አዳምጥ! :grinning:

create table #eyobzeleke(mdatetime datetime, mvalue int, mhours int )

insert into #eyobzeleke
select '2016-12-28 10:00', 1, 0 union
select '2016-12-28 13:00', 1, 0 union
select '2016-12-29 9:00', 1, 0 union
select '2016-12-30 8:00', 1, 0 union
select '2016-12-30 10:00', 1, 0 union
select '2016-12-31 12:00', 1, 0 

select * From #eyobzeleke

SELECT
b.mdatetime
,b.mvalue + ISNULL((SELECT SUM(c.mvalue) FROM #eyobzeleke c 
WHERE c.mdatetime >= DATEADD(HOUR, -24, b.mdatetime) AND 
    c.mdatetime < DATEADD(HOUR, 24, b.mdatetime) AND 
    b.mdatetime <> c.mdatetime), 0)
FROM #eyobzeleke b

drop table #eyobzeleke

#7

haha:) thanks Yosias!


#8

any inputs on this? Concurrency issue, Stored Procedure, Table Valued Function