Solved

SQL Server Query to Calculate / Summarize Data

Posted on 2013-06-05
12
248 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now