SQL Server Query to Calculate / Summarize Data

Could you provide some sample data to play with?

yes please, need data.
& is there known way to determine the full length of the material?
or is this part of the question also?

Based on your sample image, you have 45 rows of data each with a field left, center left, center right, right.  Each of those fields has a bit state of flawed (1) or not flawed (0).  With that data in your data base you can then start  to query for only completely unflawed rows (where your data is all 0 for unflawed rather than 1 for flawed)  or rows where any of the fields are flawed. Your Head end is row 1 and the start of the defect would be calculated from there.

Does that get you where you need to go?

@doryllis
IF there are 45 rows...
The fields indicated suggest to me that only the defects are recorded and if that is true then total length cannot be determined from just those defect records, and so perhaps:
a. maybe the total length is consistent (constant), or
b. there are more tables involved that provide a 'lot number' or similar, and in those tables length of each lot is provided

e.g. see ID: 38991056

@sainiak
we are all keen to assist but we need more information
could you review the questions above and help us with some sample data?

this is derived from a previous query which I think reveals some facts we need
it appears that length might be available as a field (i.length) and this is stored in metres
- seems metric measurements are stored throughout but imperials used in output

SELECT
      i.inspectionname              AS 'BAR_NUMBER'
    , (i.length / 304.8)            AS 'LENGTH'         /* 1 foot = 0.3048 metres */
      
    , (dd.height / 25.4)            AS 'DEFECT_HEIGHT'  /* 1 inch = 2.54 centimetres */
    , (dd.width / 25.4)             AS 'DEFECT_WIDTH'
      
    , (d.boxtop / 304.8)            AS 'DEFECT_BEGIN_LOCATION_FROM_HEAD'
    , (d.boxbottom / 304.8)         AS 'DEFECT_END_LOCATION_FROM_HEAD'
      
    , (dd.distanceleftedge / 25.4)  AS 'DEFECT_DISTANCE_FROM_LEFT_EDGE'
    , (dd.distancerightedge / 25.4) AS 'DEFECT_DISTANCE_FROM_RIGHT_EDGE'
    , (dd.distancecenter / 25.4)    AS 'DEFECT_DISTANCE_FROM_CENTER'
    , (dd.defectarea / 645.16)      AS 'DEFECT_AREA'           /* 1 sq. inch = 645.16 sq. millimetres */
      
    , pW.fieldvalue                 AS 'WIDTH'

FROM vc_inspections i
INNER JOIN vc_Defects d ON (
        d.MetaInspectionID = i.MetaInspectionID
        )
INNER JOIN vc_DefectDetails dd ON (
        dd.MetaInspectionID = i.MetaInspectionID
        AND dd.DefectID = d.DefectID
        )
INNER JOIN vc_processinfo pW ON (
        pW.MetaInspectionID = i.MetaInspectionID
        AND pW.label = 'Width'
        )

Select allOpen in new window

{edit I cut this down in a second edit to remove unwanted detail}
{and a third edit to restore width in the mix in case its also needed}

HI,

I was looking at some previous data you uploaded that appears to be relevant to this question - and was wondering if my interpretation is correct?

In the spreadsheet below I have charted 100 of the defect records, commencing at DEFECT_BEGIN_LOCATION_FROM_HEAD and finishing at DEFECT_END_LOCATION_FROM_HEAD these records can overlap one another (as shown by the chart).

In this particular set of data (for those 100 defects) almost no prime material would be the result (the only prime material in that result is before the first recorded defect).

Is this interpretation correct? is that how the begin location and end location should be used?

If you do provide sample data for this question I wonder it it could be left "as is" (i.e. before conversion to imperial measurements).

and a new question:
when assessing the material are you looking for a minimum length unit (e.g. 1 foot) or is it any prime material that gets included in the % calculation no matter how short it is?
defect-over-material-length.xls

Thanks to everyone for your help and patience.  I created a simple query with data that will better illustrate my dilemma.  I hope this helps and answers the posted questions.

Here is the query I am currently working with:

SET NOCOUNT ON
DECLARE @MetaID               nvarchar(30)

SELECT @MetaID = MetaInspectionID
FROM vc_Inspections
WHERE InspectionName = '778619-11';
 
SET NOCOUNT ON

SELECT
i.inspectionname as 'BAR_NUMBER',
i.length as 'BAR_LENGTH',  
CONVERT(VARCHAR( 1), d.viewname) as 'DEFECT_TOP_BOTTOM_CODE',  
CONVERT(CHAR(60), dd.class) as 'DEFECT_CLASS_NAME',  
d.boxtop as 'DEFECT_BEGIN_LOCATION_FROM_HEAD',
d.boxbottom as 'DEFECT_END_LOCATION_FROM_HEAD',
dd.height as 'DEFECT_BOX_HEIGHT'
FROM COGNEXDB.dbo.vc_inspections i
JOIN COGNEXDB.dbo.vc_Defects d
ON (i.MetaInspectionID = @MetaID ) AND (d.MetaInspectionID = i.MetaInspectionID)
JOIN COGNEXDB.dbo.vc_DefectDetails dd
ON (dd.MetaInspectionID = @MetaID ) AND (dd.DefectID = d.DefectID);

Some supporting details...
1.  BAR_LENGTH is is the length of the material.
2.  I want to calculate the the length (linear feet) of prime material.
3.  I want to do this calculation for each side of the material (Top = T and Bottom = B in DEFECT_TOP_BOTTOM_CODE).
4.  No metric/imperical data conversions are required.
5.  There is no minimum length to calculate the prime material.  All prime material should be included.
data.csv

I believe the essence of the issue is that because overlapping defects can be recorded arriving at a prime material length must cater for this possibility. To do this ideally the lag() function would be available but you indicate you use sql 2008 (& lag() is available in sql 2012).

I think I have an approach for 2008 - that caters for overlaps. Your sample data only contained one overlap (in the data for top) and I found it easier to devise some simple test cases on a smaller set of data to trail this on. I also tried to emulate the source table structure and have used source table names and field names as I also found this easier for me to follow (& I trust you can replace fieldnames with desired aliases). Note also that one of my test cases was material without any defects recorded hence I used a left join - this is noted in the code below for sql 2008.

;with
CTE as (
        select
          i.testcase
        , i.inspectionname --as 'BAR_NUMBER'
        , i.MetaInspectionID
        , d.DefectID
        , i.length --as 'BAR_LENGTH'
        , CONVERT(VARCHAR( 1), d.viewname) as 'DEFECT_TOP_BOTTOM_CODE'
        , CONVERT(CHAR(60), dd.class) as 'DEFECT_CLASS_NAME'
        , d.boxtop --as 'DEFECT_BEGIN_LOCATION_FROM_HEAD'
        , d.boxbottom --as 'DEFECT_END_LOCATION_FROM_HEAD'
        , dd.height --as 'DEFECT_BOX_HEIGHT'
  
        , row_number() over (partition by i.MetaInspectionID,CONVERT(VARCHAR( 1), d.viewname) order by  boxbottom) as row_ref
        FROM vc_inspections as i
        LEFT JOIN  vc_Defects as d ON (i.MetaInspectionID = d.MetaInspectionID)
        LEFT JOIN  vc_DefectDetails as dd ON (d.MetaInspectionID = dd.MetaInspectionID )
                                         AND (d.DefectID = dd.DefectID)
        /* left join used only for the no defect test case */
        )
, CTE2 AS (
          select
            cte.testcase
          , cte.inspectionname
          , cte.defect_top_bottom_code
          , cte.defect_class_name
          , cte.length
          , cte.boxtop
          , cte.boxbottom
          , cte.height
          , prev.boxbottom as prev_bottom
          from CTE
          left join CTE as prev ON (CTE.MetaInspectionID = prev.MetaInspectionID )
                               and (CTE.defect_top_bottom_code = prev.defect_top_bottom_code)
                               AND (CTE.row_ref - 1  = prev.row_ref)
          )
select
  testcase
, inspectionname
, defect_top_bottom_code
, isnull(sum(height),0) defect_height_raw
, isnull(sum(overlap),0) defect_overlap
, isnull(sum(height + overlap),0) defect_deduct
, length - isnull(sum(height + overlap),0) prime_material

, (isnull(sum(height + overlap),0))*100 / length defect_deduct_pct
, (length - isnull(sum(height + overlap),0))*100 / length  prime_material_pct
from (
        select
          cte2.testcase
        , cte2.inspectionname
        , cte2.defect_top_bottom_code
        , cte2.length
        , cte2.boxtop
        , cte2.boxbottom
        , cte2.height
        , cte2.prev_bottom
        , case when cte2.boxtop - cte2.prev_bottom < 0
               then cte2.boxtop - cte2.prev_bottom
               else 0
          end 
          as overlap
        from CTE2
      ) derived
group by
  testcase
, inspectionname
, defect_top_bottom_code
, length
order by inspectionname,defect_top_bottom_code
;

Select allOpen in new window

for comparison, in sql 2012 with the lag() function:

;with
CTE2 as (
        select
          i.testcase
        , i.inspectionname --as 'BAR_NUMBER'
        , i.MetaInspectionID
        , d.DefectID
        , i.length --as 'BAR_LENGTH'
        , CONVERT(VARCHAR( 1), d.viewname) as 'DEFECT_TOP_BOTTOM_CODE'
        , CONVERT(CHAR(60), dd.class) as 'DEFECT_CLASS_NAME'
        , d.boxtop --as 'DEFECT_BEGIN_LOCATION_FROM_HEAD'
        , d.boxbottom --as 'DEFECT_END_LOCATION_FROM_HEAD'
        , dd.height --as 'DEFECT_BOX_HEIGHT'
  
        , lag(boxbottom,1) over (partition by i.MetaInspectionID,CONVERT(VARCHAR( 1), d.viewname) order by boxbottom) prev_bottom
  
        FROM vc_inspections i
        LEFT JOIN  vc_Defects d  ON (i.MetaInspectionID = d.MetaInspectionID )
        LEFT JOIN  vc_DefectDetails dd ON (dd.MetaInspectionID = d.MetaInspectionID )
                                      AND (dd.DefectID = d.DefectID)
        /* left join used only for the no defect test case */
        )
select
  testcase
, inspectionname
, defect_top_bottom_code
, isnull(sum(height),0) defect_height_raw
, isnull(sum(overlap),0) defect_overlap
, isnull(sum(height + overlap),0) defect_deduct
, length - isnull(sum(height + overlap),0) prime_material

, (isnull(sum(height + overlap),0))*100 / length defect_deduct_pct
, (length - isnull(sum(height + overlap),0))*100 / length  prime_material_pct
from (
        select
          cte2.testcase
        , cte2.inspectionname
        , cte2.defect_top_bottom_code
        , cte2.length
        , cte2.boxtop
        , cte2.boxbottom
        , cte2.height
        , cte2.prev_bottom
        , case when cte2.boxtop - cte2.prev_bottom < 0
               then cte2.boxtop - cte2.prev_bottom
               else 0
          end 
          as overlap
        from CTE2
      ) derived
group by
  testcase
, inspectionname
, defect_top_bottom_code
, length
order by inspectionname,defect_top_bottom_code
;

Select allOpen in new window

sql 2008 sqlfiddle: http://sqlfiddle.com/#!3/473cf/1
sql 2012 sqlfiddle: http://sqlfiddle.com/#!6/d7bd2/3

both sets of code contain columns you hadn't requested but these are included here to help identify the calculations. the following test cases and data was used:

CREATE TABLE vc_inspections 	
	([MetaInspectionID] int, [inspectionname] varchar(12), [testcase] varchar(14), [length] int)
;
	
INSERT INTO vc_inspections 	
	([MetaInspectionID], [inspectionname], [testcase], [length])
VALUES
	(1, '778619-11', 'No Defect', 100.00),
	(2, '778619-12', 'No Overlap', 100.00),
	(3, '778619-13', 'Overlap Top', 100.00),
	(4, '778619-14', 'Overlap Bottom', 100.00),
	(5, '778619-15', 'Overlap Both', 100.00)
;

CREATE TABLE vc_Defects	
	([MetaInspectionID] int, [DefectID] int, [viewname] varchar(1), [boxtop] int, [boxbottom] int)
;
	
INSERT INTO vc_Defects	
	([MetaInspectionID], [DefectID], [viewname], [boxtop], [boxbottom])
VALUES
	(2, 1, 'T', 10, 20),
	(2, 2, 'T', 60, 70),
	(2, 3, 'B', 40, 50),
	(2, 4, 'B', 80, 90),
	(3, 5, 'T', 10, 40),
	(3, 6, 'T', 30, 70),
	(3, 7, 'B', 40, 50),
	(3, 8, 'B', 80, 90),
	(4, 9, 'T', 10, 20),
	(4, 10, 'T', 60, 70),
	(4, 11, 'B', 60, 80),
	(4, 12, 'B', 70, 90),
	(5, 13, 'T', 10, 40),
	(5, 14, 'T', 30, 70),
	(5, 15, 'B', 60, 80),
	(5, 16, 'B', 70, 90)
;

CREATE TABLE vc_DefectDetails
	([MetaInspectionID] int, [DefectID] int, [class] varchar(14), [height] int)
;
	
INSERT INTO vc_DefectDetails
	([MetaInspectionID], [DefectID], [class], [height]) 
VALUES
	(2, 1, 'Bright Scratch', 10),
	(2, 2, 'Black Line', 10),
	(2, 3, 'Bright Scratch', 10),
	(2, 4, 'Black Line', 10),
	(3, 5, 'Bright Scratch', 30),
	(3, 6, 'Black Line', 40),
	(3, 7, 'Bright Scratch', 10),
	(3, 8, 'Black Line', 10),
	(4, 9, 'Bright Scratch', 10),
	(4, 10, 'Black Line', 10),
	(4, 11, 'Bright Scratch', 20),
	(4, 12, 'Black Line', 20),
	(5, 13, 'Bright Scratch', 30),
	(5, 14, 'Black Line', 40),
	(5, 15, 'Bright Scratch', 20),
	(5, 16, 'Black Line', 20)
;

Select allOpen in new window

{+edit added test case spreadsheet}
Q-28148947.xls

and, this is an example against your provided sample data, using the derived table and aliases as provided. The sample is just the for the top as this held one overlap.

http://sqlfiddle.com/#!3/68c9d/5

;with
CTE as (
        select
          BAR_NUMBER --i.inspectionname
        --, i.MetaInspectionID
        --, d.DefectID
        , LENGTH --i.length
        , DEFECT_TOP_BOTTOM_CODE --CONVERT(VARCHAR( 1), d.viewname)
        , DEFECT_CLASS_NAME -- CONVERT(CHAR(60), dd.class)
        , DEFECT_BEGIN_LOCATION_FROM_HEAD --d.boxtop
        , DEFECT_END_LOCATION_FROM_HEAD --d.boxbottom
        , DEFECT_BOX_HEIGHT --dd.height
  
        , row_number() over (partition by BAR_NUMBER ,DEFECT_TOP_BOTTOM_CODE order by  DEFECT_END_LOCATION_FROM_HEAD) as row_ref
        FROM derived
        )
, CTE2 AS (
          select
            cte.BAR_NUMBER
          , cte.DEFECT_TOP_BOTTOM_CODE
          , cte.DEFECT_CLASS_NAME
          , cte.LENGTH
          , cte.DEFECT_BEGIN_LOCATION_FROM_HEAD
          , cte.DEFECT_END_LOCATION_FROM_HEAD
          , cte.DEFECT_BOX_HEIGHT
          , prev.DEFECT_END_LOCATION_FROM_HEAD as prev_bottom
          from CTE
          left join CTE as prev ON (CTE.BAR_NUMBER = prev.BAR_NUMBER )
                               and (CTE.DEFECT_TOP_BOTTOM_CODE = prev.DEFECT_TOP_BOTTOM_CODE)
                               AND (CTE.row_ref - 1  = prev.row_ref)
          )
select
  BAR_NUMBER
, DEFECT_TOP_BOTTOM_CODE
--, isnull(sum(DEFECT_BOX_HEIGHT),0) defect_DEFECT_BOX_HEIGHT_raw
--, isnull(sum(overlap),0) defect_overlap
--, isnull(sum(DEFECT_BOX_HEIGHT + overlap),0) defect_deduct
, LENGTH - isnull(sum(DEFECT_BOX_HEIGHT + overlap),0) prime_material

, (isnull(sum(DEFECT_BOX_HEIGHT + overlap),0))*100 / LENGTH defect_deduct_pct
, (LENGTH - isnull(sum(DEFECT_BOX_HEIGHT + overlap),0))*100 / LENGTH  prime_material_pct
from (
        select
          cte2.BAR_NUMBER
        , cte2.DEFECT_TOP_BOTTOM_CODE
        , cte2.LENGTH
        , cte2.DEFECT_BEGIN_LOCATION_FROM_HEAD
        , cte2.DEFECT_END_LOCATION_FROM_HEAD
        , cte2.DEFECT_BOX_HEIGHT
        , cte2.prev_bottom
        , case when cte2.DEFECT_BEGIN_LOCATION_FROM_HEAD - cte2.prev_bottom < 0
               then cte2.DEFECT_BEGIN_LOCATION_FROM_HEAD - cte2.prev_bottom
               else 0
          end 
          as overlap
        from CTE2
      ) derived
group by
  BAR_NUMBER
, DEFECT_TOP_BOTTOM_CODE
, LENGTH
order by BAR_NUMBER,DEFECT_TOP_BOTTOM_CODE
;

Select allOpen in new window

I have not had time to test this query yet.  I hope to soon!

Your 2008 example worked great.  I am slowly testing cases and will let you know if I run into any issues.  I am also testing with 2012 and the LAG function.  Thanks for your patience!!!