Adding previous or next row values based on condition

eyobzeleke · January 3, 2017, 5:05pm

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.

jotorre_riversidedpss.org · January 3, 2017, 5:50pm

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

eyobzeleke · January 3, 2017, 6:19pm

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

ScottPletcher · January 3, 2017, 6:37pm

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

eyobzeleke · January 3, 2017, 7:01pm

This did it. Thank you so much Scott!

yosiasz · January 3, 2017, 7:13pm

እዮብ,

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

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

eyobzeleke · January 3, 2017, 7:20pm

haha:) thanks Yosias!

eyobzeleke · May 14, 2017, 5:11pm

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

eyobzeleke · August 13, 2018, 12:00pm

Hi Yosias, for the same example sql code here, how would you modify it if I want to look at the following conditions:

previous row (for each row) - check to see the time is between 5-9pm
next row (for each row) - check to see the time is between 7-10am
there is atleast 12 hours gap between 2 consecutive rows
If all these conditions are met, pass a random number for now (99999) else 0.
***You could ignore the 24 hours deal for this challenge:
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
I appreciate any inputs!

yosiasz · August 13, 2018, 6:30pm

Eyob (የመስቀል ወፍ),

one would need a way to define and clearly identify what would constitute as previous row? What column do we key off?

ገባሽ?

eyobzeleke · August 13, 2018, 7:29pm

Yep got it! We key off of the datetime column and assume that the datetime column is sorted in ascending order. So at any point of time "previous row" is the earlier datetime for any given row except the first row which won't have a prev row reference. Make sense?

yosiasz · August 13, 2018, 7:35pm

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

;with cte
as
(
  select *,
         LAG(mdatetime) OVER (ORDER BY mdatetime ASC) PreviousValue,
		 LEAD(mdatetime) OVER (ORDER BY mdatetime ASC) NextValue
  From #eyobzeleke
)
select *,
       LTRIM(RIGHT(CONVERT(VARCHAR(20), PreviousValue, 100), 7))  PreviousTimeValue,
	   LTRIM(RIGHT(CONVERT(VARCHAR(20), NextValue, 100), 7))  NextTimeValue,
	   case
	     when (DATEPART(hour,PreviousValue) between 17 and 20 and 
		       DATEPART(hour,NextValue) between 7 and 10) and 
		     ( DATEDIFF ( hh , PreviousValue , NextValue )  = 12  ) then 9999
			 else 0
	   end as _datediff
  From cte

drop table #eyobzeleke

eyobzeleke · August 13, 2018, 8:23pm

Awesome. I think that's what I am looking for. Thanks Yosias!

yosiasz · August 13, 2018, 8:24pm

በል ላይክ ገጭ አርግልኝ

eyobzeleke · August 13, 2018, 8:25pm

just did...

yosiasz · October 3, 2018, 8:50pm

friend, adis thread jemir, mindinew yihe telefa

eyobzeleke · October 4, 2018, 12:55am

eshi Yosias. Ameseginalew:)

harishgg1 · October 5, 2018, 7:17am

hi yosiasz .. please tell me what language is this .. which country

thanks

yosiasz · October 5, 2018, 3:33pm

this is a secret alien language, we have come from the third dimension. from a universe no yet discovered. you will be assimilated

harishgg1 · October 5, 2018, 4:03pm

Thanks..ooo Thor of gupius

Please feel free to enlighten me

So is the work of Zeus