# SQL Server Query to Calculate / Summarize Data

I have a table that holds individual defect data that are detected on a long piece of material.  The table includes the following fields:

Defect Name
Height (Length of Defect)
Width (Width of Defect)
BoxTop (Distance from from Head End to Defect Top)
BoxBottom (Distance from Head End to Defect Bottom)

In the attached image, you will see a representation of a 45 foot long piece of material with yellow defects.

I want to calculate the the length (linear feet) of prime material (zero defects across width of material).

Note that  that some defects overlap.  I do not want to double-count these non-prime areas along the length of material.

Thanks!
defects.pdf
###### Who is Participating?

freelancerCommented:
OK, please stay in touch. I'd like to suggest some test cases be designed with predicted results - I have probably focused on overlapping defects and may have missed other factors that you might know of. Whilst working with real data is useful - smaller sets of test data covering a range of possibilities is also useful - and if coupled to predicted (wanted) results then we can gauge success or failure.

Anyway - look forward to your feedback.
0

Could you provide some sample data to play with?
0

freelancerCommented:
& is there known way to determine the full length of the material?
or is this part of the question also?
0

Commented:
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?
0

freelancerCommented:
@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
could you review the questions above and help us with some sample data?
0

freelancerCommented:
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'
)
``````
{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}
0

freelancerCommented:
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
0

Author Commented:
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',
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
0

freelancerCommented:
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'
, 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
;
``````
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'
, 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
;
``````
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)
;
``````
Q-28148947.xls
0

freelancerCommented:
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_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_BOX_HEIGHT
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_BOX_HEIGHT
, cte2.prev_bottom
, case when cte2.DEFECT_BEGIN_LOCATION_FROM_HEAD - cte2.prev_bottom < 0
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
;
``````
0

Author Commented:
I have not had time to test this query yet.  I hope to soon!
0

Author Commented:
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!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.