|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
History Summary Analysis and Inspection Periods!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 Show quote "Jay Balapa" <jbal***@hotmail.com> wrote in message news:eQuq3r49GHA.2408@TK2MSFTNGP05.phx.gbl... > 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 > > > > 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 Show quote On Tue, 24 Oct 2006 11:37:50 -0500, "Jay Balapa" <jbal***@hotmail.com> wrote: >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 > > >  |
|||||||||||||||||||||||
Other interesting topics