Solved

SQL Server Query to Calculate / Summarize Data

Posted on 2013-06-05
12
251 Views
Last Modified: 2013-07-26
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
0
Comment
Question by:sainiak
12 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39224163
Could you provide some sample data to play with?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39224642
yes please, need data.
& is there known way to determine the full length of the material?
or is this part of the question also?
0
 
LVL 2

Expert Comment

by:doryllis
ID: 39226853
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227826
@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?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39228366
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'
        )

Open 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}
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39232458
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 Comment

by:sainiak
ID: 39233120
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39233861
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
;

Open 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
;

Open 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)
;

Open in new window

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

Expert Comment

by:PortletPaul
ID: 39233893
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
;

Open in new window

0
 

Author Comment

by:sainiak
ID: 39248846
I have not had time to test this query yet.  I hope to soon!
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39249334
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
 

Author Closing Comment

by:sainiak
ID: 39359947
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question