Missed Inspections During the last period.

[Jay Balapa]

Note: I have also posted this in SqlProgramming goup and forgot to include
this group.


Hello,

I have three tables-

TBLINSPECTION with the following fields-
BARCODE ITEMTYPE PASS DATEINSPECTED
1000 x YES 01/12/2006
1001 x YES 02/12/2004
1002 x YES 03/12/2006
1003 y NO 04/12/2006
1004 y YES 05/12/2006
1005 z YES 06/12/2006


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 Missed Inspection report for the last period. Basically
compare each item in TBLInspection with TBLHistory and see how many items
were not inspected for the last period. Last period is referred to as
inspection period just older than the current period(GetDate() between start
and end period).

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

 

[Jay Balapa]

can anyone help?

thanks.

Note: I have also posted this in SqlProgramming goup and forgot to include
this group.


Hello,

I have three tables-

TBLINSPECTION with the following fields-
BARCODE ITEMTYPE PASS DATEINSPECTED
1000 x YES 01/12/2006
1001 x YES 02/12/2004
1002 x YES 03/12/2006
1003 y NO 04/12/2006
1004 y YES 05/12/2006
1005 z YES 06/12/2006


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 Missed Inspection report for the last period.
Basically
compare each item in TBLInspection with TBLHistory and see how many items
were not inspected for the last period. Last period is referred to as
inspection period just older than the current period(GetDate() between
start
and end period).

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

 

[RobinS]

I'm not getting the relationship between TBLInspection
and TBLHistory. They have the same fields. Can you give
more information? Does TBLInspection has the most current
values for each barcode?

Is the report only going to account for the period that
currently includes today's date? I.E. for the 1/1/2006
to 12/31/2006 period?

So you're trying to figure out what items (keyed by barcode?)
have not been inspected this year?

Robin S

 

[Cor Ligthert [MVP]]

Jay,

I have the idea that your question is not related to Adonet, can you tell us
how you get those tables?

Cor

 

[Jay Balapa]

Robin,

Yes, TblHistory is just appended version TblInspection. TblInspection gets
updated with the latest inspection but adds arecord in history.

 

[RobinS]

If your goal is to get a list of barcodes from
TblInspection that have not been inspected in
the most current time period, I think you
could just join TBLInspection with TBLInspectionPeriod.
Something like this:

select * from TBLInspection where DateInspected
< (select Max(StartPeriod from TblInspectionPeriod))

That would give you all the entries from TBLInspection
where the inspection date was less than the latest
start period.

Robin S.

------------------------------