CODEWITHSUNDEEP
sqlsql-server-2012
Rapunzo
Rapunzo

Reputation: 986

Making series with interval data of SQL rows

Sorry if I used wrong words to explain my problem.

I have a table which keeps errors on a woven fabric while the Fabric Quality Control process. I have datas like this.

result from table

(in first roll there are an error which continues for 8 meters without any interruption. But in second roll even errors are same but has interruption they are not continuous errors)

how can I get such kind of result below?

enter image description here

Upvotes: 0

Views: 91

Answers (2)

Andomar
Andomar

Reputation: 238106

That's known as the "Islands" problem. One solution is to use row_number() to form groups where the ErrorMeter changes in a different way from 1:

select  Fabric
,       Roll
,       min(ErrorMeter) as ErrorBeginMeter
,       max(ErrorMeter) as ErrorEndMeter
,       min(ErrorCode) as ErrorCode
from    (
        select  row_number() over (partition by Fabric, Roll 
                    order by ErrorMeter) - cast(ErrorMeter as int) as grp
        ,       *
        from    FabricErrors
        ) as SubQueryAlias
group by
        Fabric
,       Roll
,       grp

Live example at SQL Fiddle.

Upvotes: 1

Sean Johnson
Sean Johnson

Reputation: 5607

You'll want to use good ol' GROUP BY, with an aggregate MIN and MAX.

SELECT *,
    MIN(ErrorMeter) ErrorBeginMeter,
    MAX(ErrorMeter) ErrorEndMeter
FROM fabric_quality
GROUP BY Fabric,Roll

Edit: I just realized your result set is aggregating only rows with sequential error meters, this will require a more complicated solution.

Upvotes: 0

Related Questions