UnInspected Items Report!!

Jay Balapa · Nov 3, 2006

Hello,
I have two 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

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 Uninspected Items report for the current period.
-Basically query TBLINSPECTIONPERIOD to get the current date period
(GETDATE() between Start & End Period) and list all items from the
TblInspection which does not fall with in that 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

Arnie Rowland · Nov 3, 2006

I think that this may work for you.

SET NOCOUNT ON

DECLARE @Inspection table
( BarCode int,
ItemType varchar(20),
Pass varchar(5),
DateInspected datetime
)

INSERT INTO @Inspection VALUES ( 1000, 'x', 'YES', '01/12/2006' )
INSERT INTO @Inspection VALUES ( 1001, 'x', 'YES', '02/12/2004' )
INSERT INTO @Inspection VALUES ( 1002, 'x', 'YES', '03/12/2006' )
INSERT INTO @Inspection VALUES ( 1003, 'y', 'NC', '04/12/2006' )
INSERT INTO @Inspection VALUES ( 1004, 'y', 'YES', '05/12/2006' )
INSERT INTO @Inspection VALUES ( 1005, 'z', 'YES', '06/12/2006' )

DECLARE @InspectionPeriod table
( ItemType varchar(20),
StartPeriod datetime,
EndPeriod datetime
)

INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2004', '12/31/2004' )
INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2005', '12/31/2005' )
INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2006', '12/31/2006' )
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2004', '12/31/2004' )
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2005', '12/31/2005' )
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2006', '12/31/2006' )
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2004', '12/31/2004' )
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2005', '12/31/2005' )
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2006', '12/31/2006' )

SELECT
i.BarCode,
i.ItemType,
i.Pass,
i.DateInspected
FROM @Inspection i
JOIN @InspectionPeriod ip
ON i.ItemType = ip.ItemType
WHERE ( i.DateInspected NOT BETWEEN ip.StartPeriod AND ip.EndPeriod
AND getdate() BETWEEN ip.StartPeriod AND ip.EndPeriod
)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf

Alex Kuznetsov · Nov 3, 2006

Arnie said:
I think that this may work for you.

SET NOCOUNT ON

DECLARE @Inspection table
( BarCode int,
ItemType varchar(20),
Pass varchar(5),
DateInspected datetime
)

INSERT INTO @Inspection VALUES ( 1000, 'x', 'YES', '01/12/2006' )
INSERT INTO @Inspection VALUES ( 1001, 'x', 'YES', '02/12/2004' )
INSERT INTO @Inspection VALUES ( 1002, 'x', 'YES', '03/12/2006' )
INSERT INTO @Inspection VALUES ( 1003, 'y', 'NC', '04/12/2006' )
INSERT INTO @Inspection VALUES ( 1004, 'y', 'YES', '05/12/2006' )
INSERT INTO @Inspection VALUES ( 1005, 'z', 'YES', '06/12/2006' )

DECLARE @InspectionPeriod table
( ItemType varchar(20),
StartPeriod datetime,
EndPeriod datetime
)

INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2004', '12/31/2004' )
INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2005', '12/31/2005' )
INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2006', '12/31/2006' )
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2004', '12/31/2004' )
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2005', '12/31/2005' )
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2006', '12/31/2006' )
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2004', '12/31/2004' )
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2005', '12/31/2005' )
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2006', '12/31/2006' )

SELECT
i.BarCode,
i.ItemType,
i.Pass,
i.DateInspected
FROM @Inspection i
JOIN @InspectionPeriod ip
ON i.ItemType = ip.ItemType
WHERE ( i.DateInspected NOT BETWEEN ip.StartPeriod AND ip.EndPeriod
AND getdate() BETWEEN ip.StartPeriod AND ip.EndPeriod
)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf

------=_NextPart_000_0696_01C6FF30.624267B0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 8793

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.5296.0" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Arial size=2>I think that this may work for you.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>SET NOCOUNT ON</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>DECLARE @Inspection table<BR>  
(  BarCode        
int,<BR>     
ItemType       
varchar(20),<BR>     
Pass           
varchar(5),<BR>      DateInspected  
datetime<BR>   )</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>INSERT INTO @Inspection VALUES ( 1000, 'x',
'YES', '01/12/2006' )<BR>INSERT INTO @Inspection VALUES ( 1001, 'x', 'YES',
'02/12/2004' )<BR>INSERT INTO @Inspection VALUES ( 1002, 'x', 'YES',
'03/12/2006' )<BR>INSERT INTO @Inspection VALUES ( 1003, 'y', 'NC', 
'04/12/2006' )<BR>INSERT INTO @Inspection VALUES ( 1004, 'y', 'YES',
'05/12/2006' )<BR>INSERT INTO @Inspection VALUES ( 1005, 'z', 'YES',
'06/12/2006' )</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>DECLARE @InspectionPeriod
table<BR>   ( 
ItemType          
varchar(20),<BR>     
StartPeriod       
datetime,<BR>     
EndPeriod         
datetime<BR>   )</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>INSERT INTO @InspectionPeriod VALUES ( 'x',
'01/01/2004', '12/31/2004' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'x',
'01/01/2005', '12/31/2005' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'x',
'01/01/2006', '12/31/2006' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'y',
'01/01/2004', '12/31/2004' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'y',
'01/01/2005', '12/31/2005' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'y',
'01/01/2006', '12/31/2006' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'z',
'01/01/2004', '12/31/2004' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'z',
'01/01/2005', '12/31/2005' )<BR>INSERT INTO @InspectionPeriod VALUES ( 'z',
'01/01/2006', '12/31/2006' )</FONT></DIV>
<DIV><FONT face="Courier New" size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=2>SELECT <BR>  
i.BarCode,<BR>   i.ItemType,<BR>   i.Pass,<BR>  
i.DateInspected<BR>FROM @Inspection i<BR>   JOIN @InspectionPeriod
ip<BR>      ON i.ItemType = ip.ItemType<BR>WHERE
(   i.DateInspected NOT BETWEEN ip.StartPeriod AND
ip.EndPeriod<BR>      AND getdate() BETWEEN
ip.StartPeriod AND ip.EndPeriod<BR>     
)<BR></FONT></DIV>
<DIV><BR><FONT face=Arial size=2>-- <BR>Arnie Rowland, Ph.D.<BR>Westwood
Consulting, Inc</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR>Most
experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>You can't help someone get up a hill without
getting a little closer to the top yourself.<BR>- H. Norman
Schwarzkopf</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>"Jay Balapa" <</FONT><A
href="mailto:[email protected]"><FONT face=Arial
size=2>[email protected]</FONT></A><FONT face=Arial size=2>> wrote in
message </FONT><A href="face=Arial size=2>face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> Hello,<BR>> I
have two tables-<BR>> TBLINSPECTION with the following fields-<BR>>
BARCODE  ITEMTYPE PASS DATEINSPECTED<BR>>
1000                   
x         YES  01/12/2006<BR>>
1001                   
x         YES  02/12/2004<BR>>
1002                   
x         YES  03/12/2006<BR>>
1003                   
y         NO  
04/12/2006<BR>>
1004                   
y         YES  05/12/2006<BR>>
1005                   
z         YES  06/12/2006<BR>>
<BR>> TBLINSPECTIONPERIOD lists all the inspection periods for each
itemtype<BR>> ITEMTYPE  STARTPERIOD      
ENDPERIOD<BR>>
x                  
01/01/2004           
12/31/2004<BR>>
x                  
01/01/2005           
12/31/2005<BR>>
x                  
01/01/2006           
12/31/2006<BR>>
y                  
01/01/2004           
12/31/2004<BR>>
y                  
01/01/2005           
12/31/2005<BR>>
y                  
01/01/2006           
12/31/2006<BR>>
z                  
01/01/2004           
12/31/2004<BR>>
z                  
01/01/2005           
12/31/2005<BR>>
z                  
01/01/2006           
12/31/2006<BR>> <BR>> Iam trying to create a Uninspected Items report for
the current period.<BR>> -Basically query TBLINSPECTIONPERIOD to get the
current date period <BR>> (GETDATE() between Start & End Period) and list
all items from the <BR>> TblInspection which does not fall with in that
period.<BR>> <BR>> Can this be accomplished in a single statement or
combination of tableview<BR>> and single statement  Will I need a stored
procedure to accomplish this<BR>> task?<BR>> Any help will be greatly
appreciated.<BR>> <BR>> Thanks<BR>> -jay <BR>>
<BR>></FONT></BODY></HTML>

------=_NextPart_000_0696_01C6FF30.624267B0--

Arnie,

let me modify your test data a little bit:

SET NOCOUNT ON

DECLARE @Inspection table
( BarCode int,
ItemType varchar(20),
Pass varchar(5),
DateInspected datetime
)

INSERT INTO @Inspection VALUES ( 1000, 'x', 'YES', '01/12/2006' )
INSERT INTO @Inspection VALUES ( 1001, 'x', 'YES', '02/12/2004' )
INSERT INTO @Inspection VALUES ( 1002, 'x', 'YES', '03/12/2006' )
INSERT INTO @Inspection VALUES ( 1003, 'y', 'NC', '04/12/2006' )
INSERT INTO @Inspection VALUES ( 1004, 'y', 'YES', '05/12/2006' )
INSERT INTO @Inspection VALUES ( 1005, 'z', 'YES', '06/12/2006' )
--- AK: added this
INSERT INTO @Inspection VALUES ( 1001, 'x', 'YES', '02/12/2006' )
INSERT INTO @Inspection VALUES ( 1005, 'z', 'YES', '06/12/2004' )

DECLARE @InspectionPeriod table
( ItemType varchar(20),
StartPeriod datetime,
EndPeriod datetime
)

INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2004', '12/31/2004'
)
INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2005', '12/31/2005'
)
INSERT INTO @InspectionPeriod VALUES ( 'x', '01/01/2006', '12/31/2006'
)
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2004', '12/31/2004'
)
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2005', '12/31/2005'
)
INSERT INTO @InspectionPeriod VALUES ( 'y', '01/01/2006', '12/31/2006'
)
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2004', '12/31/2004'
)
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2005', '12/31/2005'
)
INSERT INTO @InspectionPeriod VALUES ( 'z', '01/01/2006', '12/31/2006'
)

Clearly now all the items are inspected, yet your query returns

BarCode ItemType Pass DateInspected

----------- -------------------- -----
------------------------------------------------------
1001 x YES 2004-02-12 00:00:00.000
1005 z YES 2004-06-12 00:00:00.000

I would suggest this:

SELECT
d.BarCode,
d.ItemType,
i.Pass,
i.DateInspected
FROM @InspectionPeriod ip
JOIN (SELECT DISTINCT BarCode, ItemType FROM @Inspection) d ON
d.ItemType = ip.ItemType
LEFT OUTER JOIN @Inspection i
ON i.ItemType = ip.ItemType
AND d.BarCode = i.BarCode
AND i.DateInspected BETWEEN ip.StartPeriod AND ip.EndPeriod
WHERE getdate() BETWEEN ip.StartPeriod AND ip.EndPeriod
AND i.ItemType IS NULL

BTW this inline view (SELECT DISTINCT BarCode, ItemType FROM
@Inspection) indicates that there should be a table Items - existing
databse structure is not normalized properly.

WHat do you think?

Arnie Rowland · Nov 3, 2006

Nicely done Alex, I see that I made an assumption -and shouldn't have.

Your extension plugs the holes in my logic.

I appreciate the 'lesson'.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

UnInspected Items Report!!

Jay Balapa

Arnie Rowland

Alex Kuznetsov

Arnie Rowland