0

Actual start / end time columns are representing instruction start/end dates.

But real instruction proceeds until both Min, Aim or Max changes.

So, I need to group by the raws as per Min-Aim and Max group.

I can't use directly them as group by, because 3 months ago and this month can have same Min-Aim-Max.

Name ActualStartTime ActualEndTime FLOW_SUM LIMS_AVG Min Aim Max
DOI 10:15:25.793 18:30:53.027 5884 148 140 155 160
DOI 18:30:53.027 01:14:50.177 2170 149 140 155 160
DOI 01:14:50.177 11:18:58.383 3008 148 140 145 150

data to be grouped as per min-aim-max

For example in the screenshot; Row number 1,2 are in the same instruction, need to sum flow_sum and average lims_avg Row number 3 is an instruction itself Row number 4,5,6,7 is an instruction

Somehow, after ordered as below, I need to create a relationship with 1 previous raw, then another raw until I find a different tripple Min-Aim_Max.

How can I query that?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Yusuf Mert
  • 49
  • 1
  • 8
  • 2
    *Consumable* sample data (not an image), expected results, and **importantly** *your* attempts will help us help you. – Thom A Aug 20 '21 at 12:11
  • Note that _raw_ <> _row_. – jarlh Aug 20 '21 at 12:12
  • Name ActualStartTime ActualEndTime FLOW_SUM LIMS_AVG Min Aim Max DOI_DEVIATION_Unstabilized_Naphtha_Distillation-T95 2020-12-31 10:15:25.793 2021-01-01 18:30:53.027 5884.66134019654 148.705551147461 140 155 160 DOI_DEVIATION_Unstabilized_Naphtha_Distillation-T95 2021-01-01 18:30:53.027 2021-01-02 01:14:50.177 2170.5074143874 149.257141113281 140 155 160 DOI_DEVIATION_Unstabilized_Naphtha_Distillation-T95 2021-01-02 01:14:50.177 2021-01-02 11:18:58.383 3008.00764893346 148.219998168945 140 145 150 – Yusuf Mert Aug 20 '21 at 12:14
  • That isn't consumable (it's a mess), @YusufMert . There's an [edit] feature, please use that. – Thom A Aug 20 '21 at 12:15
  • OMG Larnu, I can't stop laughing. LMAO. Sorry. – Andy3B Aug 20 '21 at 12:28
  • Thanks for editing. Tried row number, rank, partition by etc. didnt help – Yusuf Mert Aug 20 '21 at 12:41

2 Answers2

1

This is a type of gaps-and-islands problem.

There are many solutions, here is one:

  • Calculate starting points of each island using LAG
  • Assign a group ID to each island using a windowed COUNT
  • Group by the group ID and return aggregates
WITH StartPoints AS (
    SELECT *,
      IsStart = CASE WHEN
            LAG(dc.Min) OVER (PARTITION BY dc.Name ORDER BY dc.ActualStartTime) = dc.Min
        AND LAG(dc.Aim) OVER (PARTITION BY dc.Name ORDER BY dc.ActualStartTime) = dc.Aim
        AND LAG(dc.Max) OVER (PARTITION BY dc.Name ORDER BY dc.ActualStartTime) = dc.Max
        THEN NULL ELSE 1 END
    FROM dbo.vw_DOI_DEVIATION_CONSISTENCY dc
),
Groupings AS (
    SELECT *,
      GroupId = COUNT(dc.IsStart) OVER (PARTITION BY dc.Name ORDER BY dc.ActualStartTime ROWS UNBOUNDED PRECEDING)
    FROM StartPoints dc
)
SELECT
  dc.Name
  StartTime = MIN(dc.ActualStartTime),
  EndTime =   MAX(dc.ActualEndTime
  FLOW_SUM =  SUM(dc.FLOW_SUM),
  LIMS_AVG =  AVG(dc.LIMS_AVG),
  Min =       MIN(dc.Min),
  Aim =       MIN(dc.Aim),
  Max =       MIN(dc.Max)
FROM Groupings dc
GROUP BY dc.Name, dc.GroupId;
Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

I am not sure this might help, but have no more time for now. I hope it helps some how:

DECLARE @Table TABLE    
        (
                ActualStartTime     datetime
            ,   AimMin              int
            ,   AimCurrent          int
            ,   AimMax              int
        )   
;
INSERT INTO @Table
    VALUES  
    
    ('2020-12-31 10:15',    140,    155,    160)
,   ('2021-01-01 18:30',    140,    155,    160)
,   ('2021-01-02 01:14',    140,    145,    150) 
,   ('2021-01-02 11:18',    135,    140,    150)
,   ('2021-01-04 10:47',    135,    140,    150) 
,   ('2021-01-05 09:54',    135,    140,    150)
,   ('2021-01-05 16:36',    135,    140,    150) 
,   ('2021-01-05 19:33',    140,    155,    160)
,   ('2021-01-05 23:49',    140,    155,    160) 
,   ('2021-01-06 20:06',    140,    155,    160)
,   ('2021-01-07 10:32',    140,    155,    160) 
,   ('2021-01-07 16:53',    140,    155,    160)
,   ('2021-01-08 09:54',    140,    160,    160) 
;

;WITH Table1 AS
    (
        SELECT  TOP 100 PERCENT
                RowNo = RANK() OVER(PARTITION BY AimMin, AimCurrent, AimMax ORDER BY ActualStartTime)
            ,   *
        FROM    @Table
        ORDER BY ActualStartTime
    )
,   Table2  AS
    (
        SELECT  TOP 100 PERCENT
                RowNo = RANK() OVER(PARTITION BY AimMin, AimCurrent, AimMax ORDER BY ActualStartTime)
            ,   *
        FROM    @Table
        ORDER BY ActualStartTime
    )


SELECT  DISTINCT
        T1.* 
FROM    Table1  T1
JOIN    Table2  T2  ON T2.RowNo = T1.RowNo+1
WHERE   T2.RowNo = 3
    AND DATEDIFF(DAY,T1.ActualStartTime, T2.ActualStartTime) BETWEEN 0 AND 2
ORDER BY T1.ActualStartTime
Andy3B
  • 444
  • 2
  • 6