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)
 Please help with SQL statement.

Author  Topic 

vbogoly
Starting Member

1 Post

Posted - 2005-05-12 : 09:28:35
How to find records with historical gaps?
Let me explain the problem. For example I have table:

CustomerID Month Year
------------------------------------------
X 5 2003
X 6 2003
X 7 2003
X 8 2003
X 9 2003
X 11 2003
X 12 2003
X 1 2004
X 2 2004
X 3 2004

As you can see this customer has missed one record for month 10. So question is how to find all customers who have at least one missed record?

Thank you for any effort.

P.S. Historically records could start and finish on a middle of year. For example, for this customer there are records just from 5/2003 to 3/2004.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-12 : 10:18:07
It's more than just 10 isn't it..



USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE numbers(n int)
CREATE TABLE Years(y int)
CREATE TABLE Customer(c char(1))
GO

INSERT INTO numbers(n)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12

INSERT INTO years(y)
SELECT 2003 UNION ALL
SELECT 2004

INSERT INTO Customer(c)
SELECT 'X'
GO

CREATE TABLE myTable99(CustomerID char(1), [Month] int, [Year] int)
GO

INSERT INTO myTable99(CustomerID, [Month], [Year])
SELECT 'X', 5, 2003 UNION ALL
SELECT 'X', 6, 2003 UNION ALL
SELECT 'X', 7, 2003 UNION ALL
SELECT 'X', 8, 2003 UNION ALL
SELECT 'X', 9, 2003 UNION ALL
SELECT 'X', 11, 2003 UNION ALL
SELECT 'X', 12, 2003 UNION ALL
SELECT 'X', 1, 2004 UNION ALL
SELECT 'X', 2, 2004 UNION ALL
SELECT 'X', 3, 2004
GO

SELECT XXX.c, XXX.n, XXX.y
FROM (SELECT * FROM customer CROSS JOIN numbers CROSS JOIN years) AS XXX
LEFT JOIN myTable99 t
ON t.[Month] = XXX.n
AND t.[Year] = XXX.y
WHERE t.[Month] IS NULL
AND t.[Year] IS NULL
GO

SET NOCOUNT ON
DROP TABLE Customer
DROP TABLE numbers
DROP TABLE years
DROP TABLE myTable99
GO





Brett

8-)
Go to Top of Page
   

- Advertisement -