Filter for missing records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Filter for missing records

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-03-17 : 11:19:24

Good morning,

I'm trying to find missing data from a table that consists of Product and Location. This table is used to assign a product (SkuID) to a Location (LocID). I want to see which products are not at all locations, and identify which locations are missing. Here is a result of a simple select query:

SkuID LocID Qty
---------------- ---------------- -----------
1-1040 1 0
1-1040 2 0
1-1040 3 0
1-1040 4 0
1-1040 6 0
1-1040 7 0
1-1040 C -1
1001 1 6
1001 10 0
1001 2 5
1001 3 0
1001 4 11
1001 6 9
1001 7 0
1001 8 0
1001 C -6
1002 1 16
1002 10 0
1002 2 3
1002 3 1
1002 4 43
1002 6 0
1002 7 0
1002 8 0
1002 C 1467

As you can see in the above query result, some products have LocIDs 1,2,3,4,6,7,8,10, and C, while others have missing locations.

Here's the DDL of the table involved:

CREATE TABLE [dbo].[ProductLocation] (
[SkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Qty] [int] NULL ,
[DestLocID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ETA] [datetime] NULL ,
[LocationPrice] [money] NULL ,
[IsTaxed] [bit] NOT NULL ,
[StatusID] [int] NOT NULL ,
[MinLvl] [int] NOT NULL ,
[MaxLvl] [int] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OverrideQty] [int] NULL
) ON [PRIMARY]
GO

Thanks for your assistance.

D

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 11:26:59

Do you have a products table?

What the sql that generated your output you displayed?

Brett

8-)

uxphreak
Starting Member

38 Posts

Posted - 2005-03-17 : 11:29:10

Hi Brett,

Yes, I have a Product table. Here's the DDL for it:

CREATE TABLE [dbo].[Product] (
[SkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPC] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CatalogCode] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight] [float] NULL ,
[BasePrice] [money] NULL ,
[StatusID] [int] NOT NULL ,
[CategoryID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RefSkuID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
[XferInclude] [bit] NOT NULL
) ON [PRIMARY]
GO

Here's the query I used to get the previous results:

select top 25
SkuID
, LocID
, Qty
from
productlocation

Thanks,

D

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 11:36:41

Run this and paste the results for us. reduce the results with this


SELECT TOP 30 SQL FROM (
SELECT 'INSERT INTO ProductLocation (SkuID, LocID, Qty) ' AS SQL, 1 AS SQLOrder
UNION ALL
SELECT 'SELECT ' + ''''+RTRIM(SkuID)+''','''+ RTRIM(LocID)+''','+CONVERT(varchar(15),Qty)+ ' UNION ALL ' AS SQL, 2 AS SQLOrder
FROM ProductLocation) AS XXX ORDER BY SQLOrder

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 11:42:46

Never mind...

This is not tested, but I think you need to do this


    SELECT p.SkuID, l.LocID
      FROM Product p
CROSS JOIN Location l
     WHERE NOT EXISTS (SELECT * 
		    FROM ProdcutLocation pl
		   WHERE pl.Poduct = p.Product
		     AND pl.Location = l.Location)

Brett

8-)

uxphreak
Starting Member

38 Posts

Posted - 2005-03-17 : 11:42:55

It's rather large, displaying 25,149 rows. It might be better to e-mail the results in a text file?

D

uxphreak
Starting Member

38 Posts

Posted - 2005-03-17 : 12:06:00

Thanks Brett, your last post did the trick. Much appreciated.

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources