How to find dates that are missing

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

General SQL Server Forums

New to SQL Server Programming

How to find dates that are missing

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-05-13 : 11:52:10

Hi,

i have a db that gets real time min by min datas everyday but sometimes somehow some of those dates did not written into that db and i wanna know which dates are missing? how can i do it?

thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-13 : 12:50:52

Do u want to check
a set of distinct dates in a column, against, all dates within a period ?

Srinika

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-13 : 12:53:08

Before anyone takes a wild guess at at a solution could you please answer the following questions:

what is the datatype of your "min by min date" column and is there any sequencing column besides the date column? (post the applicable DDL - create table script)

Are the values exactly 1 minute apart:
2006-05-13 12:40:00.000
2006-05-13 12:41:00.000
2006-05-13 12:43:00.000
or are they about a minute apart? (Post an example of your data - as insert statements to your DDL)

what is the desired output:
let's say 2 sequencial rows are 5 minutes apart, do you want to see just the row preceding the gap or do you want a datetime value returned for each of the 4 missing rows?

Be One with the Optimizer
TG

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-05-13 : 16:39:26

well,

my values are like
2006-05-13 12:40:00.000
2006-05-13 12:41:00.000
2006-05-13 12:42:00.000

and what i wanna exactly do is, to determine what dates are missing and then insert those values into my table..
Lets say

2006-05-13 12:40:00.000
2006-05-13 12:41:00.000
2006-05-13 12:43:00.000
..
2006-05-13 12:47:00.000
2006-05-13 12:48:00.000

i need to get the dates between 12:43:00 and 12:47:00 and insert those missing values.

thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-13 : 18:47:46

I think u may need to have something like
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

and find the missing date-time

Srinika

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-14 : 02:47:23

Have a look at this


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetDates]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetDates]
GO



Create Function GetDates(@startDate DateTime,@EndDate DateTime, @pDiff smallint)
RETURNS @DateTime table 
(
	TmpDate DateTime 
) 
Begin 
While @startDate < @EndDate
Begin 
	Insert @DateTime 
	Select @StartDate
	Set @StartDate = DateAdd(mi,@pDiff,@StartDate)
End 
return 
End 

GO

Declare @TblDate Table 
(
TmpDate DateTime 
)
Insert @TblDate 
Select '2006-05-13 12:40:00.000' Union All 
Select '2006-05-13 12:41:00.000' Union All 
Select '2006-05-13 12:43:00.000' Union All 
Select '2006-05-13 12:47:00.000' Union All 
Select '2006-05-13 12:48:00.000' 

Select * From @TblDate 

Insert @TblDate
Select * From Dbo.GetDates('2006-05-13 12:40:00.000','2006-05-13 12:48:00.000',1) Where 
TmpDate not In (Select TmpDate From @TblDate)

Select * From @TblDate  order by 1

Here i have created the function which will get all the dates between 2 particular dates.

Then you can compare that with the orginal date and find out the dates which are missing and then insert them

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-14 : 12:12:16

Here is another way. This one should be more efficient on larger sets of data becuase it doesn't require any looping, but it does require a "numbers" table. It only returns the missing dates (unless you comment out the last where criteria)


Declare @TblDate Table (TmpDate DateTime)
declare @numbers table (n int)

--sample dates
Insert @TblDate 
Select '2006-05-13 12:40:00.000' Union All 
Select '2006-05-13 12:41:00.000' Union All 
Select '2006-05-13 12:43:00.000' Union All 
Select '2006-05-13 12:47:00.000' Union All 
Select '2006-05-13 12:48:00.000' 

--any existing table of sequencial ints
insert @numbers (n)
select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8

declare @mindt datetime ,@maxdt datetime
select @mindt = min(tmpDate), @maxdt = max(tmpdate) from @tbldate


select	dateadd(minute, n.n, @mindt) as missingDates
from	@numbers n
left join @tblDate d
		on datediff(minute, @minDt, d.tmpdate) = n.n

--only use numbers we need
where	n.n <= datediff(minute,@mindt, @maxdt)

--exclude dates we already have
and	d.tmpdate is null

output:
missingDates                                           
------------------------------------------------------ 
2006-05-13 12:42:00.000
2006-05-13 12:44:00.000
2006-05-13 12:45:00.000
2006-05-13 12:46:00.000

Be One with the Optimizer
TG

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-05-15 : 09:45:56

Hi,

i wanna ask something TG,
first of, my table which has dates is app. 34000000 rows and do i need to do the insertion below?:

--sample dates
Insert @TblDate
Select '2006-05-13 12:40:00.000' Union All
Select '2006-05-13 12:41:00.000' Union All
Select '2006-05-13 12:43:00.000' Union All
Select '2006-05-13 12:47:00.000' Union All
Select '2006-05-13 12:48:00.000'

and i also wanna know what does this for?

--any existing table of sequencial ints
insert @numbers (n)
select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8

i would be so grateful if you can tell me more about this. Thanks so much

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-15 : 09:58:40

raysefo,
U don't have to write statements for each record

TG has given those as sample data
U can use ur data as they r.

Srinika

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-15 : 15:30:41

Hi raysefo,

Yes, as Srinika says, the @tblDate object is a table variable that I used simply to have a table with sample data to use with my statement. (since you didn't provide any DDL/DML for me to use)

The Numbers table (some people call it a tally table) is just a handy thing to have in your database for things like this. How you can make use of it is only limited by your imagination. As an alternative to a permanent table you can also use a derived table or a user-defined function as your numbers table. Below is a a link to an exceptional example of one of those functions.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=Numbers,table

Be One with the Optimizer
TG

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-05-16 : 10:54:29

Hi,

TG i wanna ask one thing, how can i declare my existing table like you did in your example code?

Declare @TblDate Table (TmpDate DateTime)

Lets say i have [EUR/USD] table and i wanna use this table in your code.

thanks much

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-16 : 12:56:07

>>TG i wanna ask one thing, how can i declare my existing table like you did in your example code?
Why do you want to declare a table variable?

If you have an existing table, you don't need to declare a table variable. Just change the FROM clause to use your existing table rather than the table variable. The code in blue is all you need to run against your table. And, of course, you need the numbers table (if you go with my solution).

Be One with the Optimizer
TG

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-05-16 : 14:50:55

Hi,

TG, i changed your code as you said;

declare @mindt datetime ,@maxdt datetime
declare @numbers table (n int)

--any existing table of sequencial ints
insert @numbers (n)
select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8

select @mindt = min(fTime), @maxdt = max(fTime) from [EUR/USD]

select dateadd(minute, n.n, @mindt) as missingDates
from @numbers n
left join [EUR/USD]
on datediff(minute, @minDt, [EUR/USD].fTime) = n.n

--only use numbers we need
where n.n <= datediff(minute,@mindt, @maxdt)

--exclude dates we already have
and [EUR/USD].fTime is null

it worked but it only gave 7 results however i have 1619 rows and as far as i know there are much more than 7 missing dates. Would you please help me on this situation?

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-16 : 15:43:43

the @numbers table variable only has 8 numbers in it. You need to either generate your own numbers table (with lots of numbers) or use the udf that Michael Valentine Jones wrote (in the link above)

Depending on how much data you want to check at one shot, you may want to limit the daterange for each call:
where ftime > <someDatetime>
and ftime < <someotherDatetime>

Be One with the Optimizer
TG

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2006-05-16 : 17:03:14

Hello,

TG, thanks for advice and one last thing left to do. i need to avoid weekends. Saturday and Sunday should NOT be in the missingdates. How can i do it?

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-16 : 22:25:45

add this to the where clause:

--exclude weekends
and datename(weekday, dateadd(minute, n.n, @mindt)) not in ('Saturday', 'Sunday')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources