I have a table that has a period field (a date) and a Group of other fields.
This makes up the primary key.
Example Fields: (The 3 together make up the primary key)
Period
AccountType
AccountSubType
Lets say there are 400 unique AccountType/AccountSubTypes
AT AST
A A
A B
A C
B A
B B
C A
C B
And we have a set of Periods
1/1/2001
2/1/2001
...
...
...
12/1/2001
For each period, I need to make sure that every AccountType/AccountSubTypes
has a record
So lets say for 1/1/2001 AccountType A, AccountSubTypes B is missing.
I need to be able to get a list of these records so that they can be
inserted back into the database.
I can't figure out a way without using a cursor to find the missing rows and
insert them into the table.
--Begin of SQL Script
--Create Table -------------------------------
CREATE TABLE [dbo].[TestTable] (
[Period] [datetime] NOT NULL ,
[AccountType] [varchar] (2) NOT NULL ,
[AccountSubType] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
--Insert statements --------------------------
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('1/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('2/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('3/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('4/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('5/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('6/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('7/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('8/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('9/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('10/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('11/1/2001','C','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','A','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','A','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','A','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','A','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','B','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','B','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','B','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','B','D')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','C','A')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','C','B')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','C','C')
Insert into TestTable (Period, AccountType, AccountSubType) Values ('12/1/2001','C','D')
--Delete Statements To create "holes"---------
Delete from TestTable Where AccountType = 'C' and AccountSubType = 'D'
------------------------------------------------------------------------
--End of SQL Script