How to Find Gaps in Dates? 1

[lrdave36]

Hey guys,

I am trying to write a query that will show me anyone who has a gap or missing time period in their payment date records.

For example, a normal record would look like this:


SSN Payment Date


113335656 2006-12-01
113335656 2007-01-01



I want to find people who might be missing a year or month such as this:



SSN Payment Date

113435656 2006-12-01
113435656 2009-12-01



The closet I've come up with is finding people who don't have all 12 months, but since some people may start work in the middle of the year, they shouldn't be in my query results. If I can at least find people who have gaps in the years, that would be a good start.


If possible, I want to find a way with derived tables. I don't have WRITE access to the database so I don't believe I can use CREATE TABLE or CREATE VIEWS to hold date values.

Can anyone help me? Thanks

 

[gmmastros]

Just to be clear... there should be one row for each month, right?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try:

1. Assuming you have a numbers table in your database
(if not, you can use this Itzik Ben-Gan function instead:

CREATE FUNCTION dbo.GetNumbers
(
@Start BIGINT,
@End BIGINT
)
RETURNS @ret TABLE(Number BIGINT)
AS
BEGIN
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
INSERT INTO @ret(Number)
SELECT N FROM NUM WHERE N BETWEEN @Start AND @End
RETURN
END
go

2. Create a range of dates within the passed dates (one date for each month):

;with AllDates as (select dateadd(month, N.n, @StartDate) 
as MonthStart from Numbers N where N.n between 0 and datediff(month, @StartDate, @EndDate)),
AllInfo as (select A.*, E.EmployeeID from AllDates, Employees) -- we need a cross join between all possible dates and all distinct EmployeeIDs


select A.MonthStart from AllInfo A LEFT JOIN EmployeePayments E
on A.EmployeeID = E.EmployeeID and 
E.PaymentDate >=A.MonthStart and E.PaymentDate < dateadd(month, 1, A.MonthStart) where E.EmployeeID IS NULL

The last LEFT JOIN (or you can use NOT EXISTS instead) will select all employees' missing dates.

PluralSight Learning Library

 

[gmmastros]

I have an idea. It may not work based on how the data is stored, but it's an idea worth exploring.

So... for each SSN, you could get the min date and the max date. You can also get the count of rows. If the count of rows is equal to the month difference (+1) between the min and max date, then there are no gaps.

For example, this data...

[tt][blue]
1 2007-10-01 00:00:00.000
1 2007-11-01 00:00:00.000
1 2007-12-01 00:00:00.000
1 2008-01-01 00:00:00.000
1 2008-02-01 00:00:00.000
1 2008-03-01 00:00:00.000
1 2008-04-01 00:00:00.000
1 2008-05-01 00:00:00.000
1 2008-06-01 00:00:00.000

2 2008-01-01 00:00:00.000
2 2008-02-01 00:00:00.000
2 2008-03-01 00:00:00.000
2 2008-04-01 00:00:00.000
-- Missing row for 2008-05-01
2 2008-06-01 00:00:00.000
2 2008-07-01 00:00:00.000
2 2008-08-01 00:00:00.000
2 2008-09-01 00:00:00.000
[/blue][/tt]

Looking at id= 1.

Min Date = 2007-10-01
Max Date = 2008-06-01
Count = 9
Month difference is 8.

So. If count = Month Difference + 1, then there are no gaps.

Looking at id = 2
Min Date = 2008-01-01
Max Date = 2008-09-01
Count = 8
Month difference = 8

Since Count <> Month Difference + 1, there must be a missing row.

In code...

Declare @Temp Table(Id Int, PaymentDate DateTime)

Insert Into @Temp Values(1, '2007-10-01')
Insert Into @Temp Values(1, '2007-11-01')
Insert Into @Temp Values(1, '2007-12-01')
Insert Into @Temp Values(1, '2008-01-01')
Insert Into @Temp Values(1, '2008-02-01')
Insert Into @Temp Values(1, '2008-03-01')
Insert Into @Temp Values(1, '2008-04-01')
Insert Into @Temp Values(1, '2008-05-01')
Insert Into @Temp Values(1, '2008-06-01')

Insert Into @Temp Values(2, '2008-01-01')
Insert Into @Temp Values(2, '2008-02-01')
Insert Into @Temp Values(2, '2008-03-01')
Insert Into @Temp Values(2, '2008-04-01')
Insert Into @Temp Values(2, '2008-06-01')
Insert Into @Temp Values(2, '2008-07-01')
Insert Into @Temp Values(2, '2008-08-01')
Insert Into @Temp Values(2, '2008-09-01')

Select   Id
From     @Temp
Group By ID
Having   1 + DateDiff(Month, Min(PaymentDate), Max(PaymentDate)) <> Count(PaymentDate)

Note that there are a couple of potential problems, but it all relates to the way your data is stored, specifically, how clean your data is. If there are duplicate payment dates, the count will not be right. If there can be multiple payments per month, or payments can be made at the end of one month instead of the beginning of the next month, then the datediff won't work the way you expect it to either.

But, under tightly controlled circumstances, this query just might work out well for you, and I bet it's pretty fast too.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

The above didn't account for the fact that some employees may have joined the company in the middle of the year. If so, we need to ALTER the last select statement by also adding min(PaymentDate) (or better using StartDate from the Employees table).

To make it work correctly for employees who left the company and then returned back, will be a bit more trickier.

PluralSight Learning Library

 

[lrdave36]

Hi George,

Yes, there is one row per month, and the day is always on the 1st. I think your query may just work. I'll give it a shot. Thanks!

 

[markros]

The difference in our queries as George's will find people with missing dates. Mine will list people + the dates that were missed (month + year).

PluralSight Learning Library

 

[simian336]

You might also want to consider a calendar table... It makes all kinds of queries involving dates easier....

Here is a brief tutorial.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi