Grouped Aggregation

Transact-SQL
1

Hello. First time posting here.

I've got a SQL query questions. I'm trying to generate a column that will identify PrevValue and CurrValue by date. In the example, SP_Drift column identifies a change in CurrValue +2. The DriftTag column is where I need help. It needs to identify only in the data set where the Zone changes until it is equal to the CurrValue. In this example the data set group inside the box is correct, however, I cannot figure out to NOT include ones outside of the box. The process starts again when it finds another drift based on the SP_Drift column and start it's process again only AFTER the SP_Drift and should not tag the actual drifted row (in yellow). Any suggestions?

Sample
Sample.JPG642×584 94.1 KB

2

please provide sample data in the following format

create table #jvibez(_date datetime, 
PrevValue decimal(6,2), 
CurrValue decimal(6,2), 
Zone decimal(6,2), 
SP_Drift decimal(6,2))

insert into #jvibez
select '2018-07-24 22:15', 72.01, 74.01,71.42,71.42 union
--next set of data
3

Here you go. I need the assistance on 'DriftTag' column. Thank you.

create table #jvibez(_date datetime,
PrevValue decimal(6,2),
CurrValue decimal(6,2),
Zone decimal(6,2))

insert into #jvibez
select '2018-07-24 21:45', 72.01, 72.01, 72.61 union
select '2018-07-24 22:00', 72.01, 72.01, 71.87 union
select '2018-07-24 22:15', 72.01, 74.01, 71.42 union
select '2018-07-24 22:30', 74.01, 74.01, 72.25 union
select '2018-07-24 22:45', 74.01, 74.01, 72.79 union
select '2018-07-24 23:00', 74.01, 74.01, 73.26 union
select '2018-07-24 23:15', 74.01, 74.01, 73.89 union
select '2018-07-24 22:30', 74.01, 74.01, 72.25 union
select '2018-07-24 23:45', 74.01, 74.01, 74.89 union
select '2018-07-24 00:00', 74.01, 74.01, 74.62 union

select *
,CASE WHEN (LAG(SP_Drift) Over(ORDER BY _date)) < CurrVALUE AND [Zone] < CurrVALUE then .13
WHEN (LAG(PrevValue) Over(ORDER BY _date)) = CurrVALUE AND [Zone] < CurrVALUE then .13
WHEN (LAG(SP_Drift) Over(ORDER BY _date)) > CurrVALUE AND [Zone] >= CurrVALUE then .13
else 0 end as DriftTag
from (

select *
,CASE WHEN ABS(PrevValue - CurrValue) >=2 THEN CurrValue ELSE 0 END AS SP_Drift
from #jvibez
) as drift

4

I have absolutely no clue as to the logic that should be implemented for column drifttag, so I have implemented "what I think" you're aiming at:

with cte
  as (select a._date as dt_from
            ,min(b._date) as dt_to
        from #jvibez as a
             left outer join #jvibez as b
                          on b._date>a._date
                         and b.[zone]>a.currvalue
       where a.currvalue-a.prevvalue>=2
       group by a._date
     )
select b._date
      ,b.prevvalue
      ,b.currvalue
      ,b.[zone]
      ,case
          when b.prevvalue=b.currvalue
          then null
          else b.[zone]
       end as sp_drift
      ,sign(isnull(b.[zone]-lag(b.[zone],1) over(order by b._date),0))*.13 as drifttag
  from cte as a
       inner join #jvibez as b
               on b._date>=a.dt_from
              and b._date<a.dt_to
;