Can you post the SQL you have, DDL for the tables and insert statement(s) for the data please.
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
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
This did it. Thank you so much Scott!
እዮብ,
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
haha:) thanks Yosias!
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!
Eyob (የመስቀል ወፍ),
one would need a way to define and clearly identify what would constitute as previous row? What column do we key off?
ገባሽ?
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?
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
Awesome. I think that's what I am looking for. Thanks Yosias!
በል ላይክ ገጭ አርግልኝ
just did...
friend, adis thread jemir, mindinew yihe telefa
eshi Yosias. Ameseginalew:)
this is a secret alien language, we have come from the third dimension. from a universe no yet discovered. you will be assimilated
Thanks..ooo Thor of gupius
Please feel free to enlighten me
So is the work of Zeus
Or ethiopia