History Summary Analysis and Inspection Periods!

  • Thread starter Thread starter Jay Balapa
  • Start date Start date
J

Jay Balapa

Hello,

I have two tables-

TBLHISTORY with the following fields-

BARCODE ITEMTYPE PASS DATEINSPECTED
1000 x YES 01/12/2004
1000 x YES 06/12/2004
1000 x NO 01/12/2005
1000 x NO 06/12/2005
1000 x YES 01/12/2006
1001 x YES 02/12/2004
1001 x YES 02/12/2005
1001 x YES 02/12/2006
1002 x NO 03/12/2004
1002 x YES 03/12/2005
1002 x YES 03/12/2006
1003 y YES 04/12/2004
1003 y YES 04/12/2005
1003 y NO 04/12/2006
1004 y YES 05/12/2004
1004 y YES 06/12/2004
1004 y YES 05/12/2005
1004 y YES 08/12/2005
1004 y YES 05/12/2006
1005 z NO 06/12/2004
1005 z NO 06/12/2005
1005 z YES 06/12/2006


TBLINSPECTIONPERIOD lists all the inspection periods for each itemtype

ITEMTYPE STARTPERIOD ENDPERIOD
x 01/01/2004 12/31/2004
x 01/01/2005 12/31/2005
x 01/01/2006 12/31/2006
y 01/01/2004 12/31/2004
y 01/01/2005 12/31/2005
y 01/01/2006 12/31/2006
z 01/01/2004 12/31/2004
z 01/01/2005 12/31/2005
z 01/01/2006 12/31/2006

Iam trying to create a summary resultset which has the following-

ITEMTYPE STARTPERIOD ENDPERIOD [# of items Inspected] [# of
Items Passed] [Percentage Passed]
x 01/01/2004 12/31/2004 3
2 66%
x 01/01/2005 12/31/2005 3
1 33%
x 01/01/2006 12/31/2006 3
3 100%
y 01/01/2004 12/31/2004 2
2 100%
y 01/01/2005 12/31/2005 2
2 100%
y 01/01/2006 12/31/2006 2
1 50%
z 01/01/2004 12/31/2004 1
0 0%
z 01/01/2005 12/31/2005 1
0 0%
z 01/01/2006 12/31/2006 1
1 100%



- Iam trying to group the ItemTypes in tblhistory into the corresponding
InspectionPeriod based
on the Inspectiondates.
- We get Sum of all uniquevItems Inspected for each ItemType which falls
under each InspectionPeriod.
- If an item is inspected more than once during an Inspection Period only
the most recent result is taken and older
inspection is discarded. Then we get the number of items passed. Finally
the Pass %.

Can this be accomplished in a single statement or combination of tableview
and single statement Will I need a stored procedure to accomplish this
task?
Any help will be greatly appreciated.

Thanks

Jay
 
Can someone give me some directions on how to go about solving this problem?

Thanks.
jay


Jay Balapa said:
Hello,

I have two tables-

TBLHISTORY with the following fields-

BARCODE ITEMTYPE PASS DATEINSPECTED
1000 x YES 01/12/2004
1000 x YES 06/12/2004
1000 x NO 01/12/2005
1000 x NO 06/12/2005
1000 x YES 01/12/2006
1001 x YES 02/12/2004
1001 x YES 02/12/2005
1001 x YES 02/12/2006
1002 x NO 03/12/2004
1002 x YES 03/12/2005
1002 x YES 03/12/2006
1003 y YES 04/12/2004
1003 y YES 04/12/2005
1003 y NO 04/12/2006
1004 y YES 05/12/2004
1004 y YES 06/12/2004
1004 y YES 05/12/2005
1004 y YES 08/12/2005
1004 y YES 05/12/2006
1005 z NO 06/12/2004
1005 z NO 06/12/2005
1005 z YES 06/12/2006


TBLINSPECTIONPERIOD lists all the inspection periods for each itemtype

ITEMTYPE STARTPERIOD ENDPERIOD
x 01/01/2004 12/31/2004
x 01/01/2005 12/31/2005
x 01/01/2006 12/31/2006
y 01/01/2004 12/31/2004
y 01/01/2005 12/31/2005
y 01/01/2006 12/31/2006
z 01/01/2004 12/31/2004
z 01/01/2005 12/31/2005
z 01/01/2006 12/31/2006

Iam trying to create a summary resultset which has the following-

ITEMTYPE STARTPERIOD ENDPERIOD [# of items Inspected] [# of
Items Passed] [Percentage Passed]
x 01/01/2004 12/31/2004 3 2
66%
x 01/01/2005 12/31/2005 3 1
33%
x 01/01/2006 12/31/2006 3 3
100%
y 01/01/2004 12/31/2004 2 2
100%
y 01/01/2005 12/31/2005 2
2 100%
y 01/01/2006 12/31/2006 2
1 50%
z 01/01/2004 12/31/2004 1
0 0%
z 01/01/2005 12/31/2005 1
0 0%
z 01/01/2006 12/31/2006 1
1 100%



- Iam trying to group the ItemTypes in tblhistory into the corresponding
InspectionPeriod based
on the Inspectiondates.
- We get Sum of all uniquevItems Inspected for each ItemType which falls
under each InspectionPeriod.
- If an item is inspected more than once during an Inspection Period only
the most recent result is taken and older
inspection is discarded. Then we get the number of items passed.
Finally the Pass %.

Can this be accomplished in a single statement or combination of tableview
and single statement Will I need a stored procedure to accomplish this
task?
Any help will be greatly appreciated.

Thanks

Jay
Click to expand...
 
The following is untested, but I think it will return what was
specified.

SELECT I.ITEMTYPE, I.STARTPERIOD, I.ENDPERIOD,
COUNT(H.BARCODE) as Inspected,
SUM(CASE WHEN H.PASS = 'YES'
THEN 1
ELSE 0
END) as Passed,
SUM(CASE WHEN H.PASS = 'YES'
THEN 1
ELSE 0
END) /
COUNT(H.BARCODE) * 100.0 AS PctPassed
FROM (select P1.ITEMTYPE, P1.STARTPERIOD, P1.ENDPERIOD,
H1.BARCODE, MAX(H1.DATEINSPECTED) as DATEINSPECTED
from TBLINSPECTIONPERIOD as P1
join TBLHISTORY as H1
on P1.ITEMTYPE = H1.ITEMTYPE
and H1.DATEINSPECTED BETWEEN P1.STARTPERIOD
AND P1.ENDPERIOD) AS I
JOIN TBLHISTORY as H
ON I.ARCODE = H.ARCODE
AND I.ITEMTYPE = H.ITEMTYPE
AND I.DATEINSPECTED = H.DATEINSPECTED
GROUP BY I.ITEMTYPE, I.STARTPERIOD, I.ENDPERIOD

The derived table I (I as in Included) takes care of eliminating the
unwanted rows in History. Then we join it back to History for the
counts. Note that the calculation of the percentage requires
repetition of the other two calculations; this could have been avoided
by making the query (minus the percentage calculation) a view (or
derived table) and the calculating the percentage in a query against
the view.

Roy Harvey
Beacon Falls, CT

Hello,

I have two tables-

TBLHISTORY with the following fields-

BARCODE ITEMTYPE PASS DATEINSPECTED
1000 x YES 01/12/2004
1000 x YES 06/12/2004
1000 x NO 01/12/2005
1000 x NO 06/12/2005
1000 x YES 01/12/2006
1001 x YES 02/12/2004
1001 x YES 02/12/2005
1001 x YES 02/12/2006
1002 x NO 03/12/2004
1002 x YES 03/12/2005
1002 x YES 03/12/2006
1003 y YES 04/12/2004
1003 y YES 04/12/2005
1003 y NO 04/12/2006
1004 y YES 05/12/2004
1004 y YES 06/12/2004
1004 y YES 05/12/2005
1004 y YES 08/12/2005
1004 y YES 05/12/2006
1005 z NO 06/12/2004
1005 z NO 06/12/2005
1005 z YES 06/12/2006


TBLINSPECTIONPERIOD lists all the inspection periods for each itemtype

ITEMTYPE STARTPERIOD ENDPERIOD
x 01/01/2004 12/31/2004
x 01/01/2005 12/31/2005
x 01/01/2006 12/31/2006
y 01/01/2004 12/31/2004
y 01/01/2005 12/31/2005
y 01/01/2006 12/31/2006
z 01/01/2004 12/31/2004
z 01/01/2005 12/31/2005
z 01/01/2006 12/31/2006

Iam trying to create a summary resultset which has the following-

ITEMTYPE STARTPERIOD ENDPERIOD [# of items Inspected] [# of
Items Passed] [Percentage Passed]
x 01/01/2004 12/31/2004 3
2 66%
x 01/01/2005 12/31/2005 3
1 33%
x 01/01/2006 12/31/2006 3
3 100%
y 01/01/2004 12/31/2004 2
2 100%
y 01/01/2005 12/31/2005 2
2 100%
y 01/01/2006 12/31/2006 2
1 50%
z 01/01/2004 12/31/2004 1
0 0%
z 01/01/2005 12/31/2005 1
0 0%
z 01/01/2006 12/31/2006 1
1 100%



- Iam trying to group the ItemTypes in tblhistory into the corresponding
InspectionPeriod based
on the Inspectiondates.
- We get Sum of all uniquevItems Inspected for each ItemType which falls
under each InspectionPeriod.
- If an item is inspected more than once during an Inspection Period only
the most recent result is taken and older
inspection is discarded. Then we get the number of items passed. Finally
the Pass %.

Can this be accomplished in a single statement or combination of tableview
and single statement Will I need a stored procedure to accomplish this
task?
Any help will be greatly appreciated.

Thanks

Jay
Click to expand...
 
Back
Top