Posted by teddysnips on 02/08/07 13:06

SQL 2k, DDL below.

I have a simple table with the following data:

fldYear fldCode1 fldCode2
2000 ABC1 ABC12
2000 ABC1 ABC13
2001 ABC1 ABC12
2002 ABC1 ABC12
2002 ABC1 ABC13

I need to know, for every distinct combination of fldCode1 and
fldCode2, if there are any years missing.

For example,

SELECT DISTINCT fldCode1, fldCode2 FROM MyTable

returns
ABC1 ABC12
ABC1 ABC13

I need to know that in 2001 there was no entry for ABC1/ABC13

Thanks!

Edward

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyTable]
GO

CREATE TABLE [dbo].[MyTable] (
[fldYear] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

[Back to original message]