UnInspected Items Report!!

3 Nov 2006 5:17 PM

Jay Balapa

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

3 Nov 2006 6:11 PM

Arnie Rowland

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

Show quote

"Jay Balapa" <jbal***@hotmail.com> wrote in message news:OdOaxw2$GHA.2328@TK2MSFTNGP02.phx.gbl...
> 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
>
>

3 Nov 2006 7:07 PM

Alex Kuznetsov

Arnie Rowland wrote:
Show quote

> 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
>
>
> "Jay Balapa" <jbal***@hotmail.com> wrote in message news:OdOaxw2$GHA.2328@TK2MSFTNGP02.phx.gbl...
> > 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
> >
> >
> ------=_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:jbal***@hotmail.com"><FONT face=Arial
> size=2>jbal***@hotmail.com</FONT></A><FONT face=Arial size=2>> wrote in
> message </FONT><A href="news:OdOaxw2$GHA.2***@TK2MSFTNGP02.phx.gbl"><FONT
> face=Arial size=2>news:OdOaxw2$GHA.2328@TK2MSFTNGP02.phx.gbl</FONT></A><FONT
> 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?

3 Nov 2006 8:35 PM

Arnie Rowland

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

Show quote

"Alex Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1162580836.403536.102900@e3g2000cwe.googlegroups.com...
>
> Arnie Rowland wrote:
>> 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
>>
>>
>> "Jay Balapa" <jbal***@hotmail.com> wrote in message
>> news:OdOaxw2$GHA.2328@TK2MSFTNGP02.phx.gbl...
>> > 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
>> >
>> >
>> ------=_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:jbal***@hotmail.com"><FONT face=Arial
>> size=2>jbal***@hotmail.com</FONT></A><FONT face=Arial size=2>> wrote
>> in
>> message </FONT><A href="news:OdOaxw2$GHA.2***@TK2MSFTNGP02.phx.gbl"><FONT
>> face=Arial
>> size=2>news:OdOaxw2$GHA.2328@TK2MSFTNGP02.phx.gbl</FONT></A><FONT
>> 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?
>