Query for getting missing dates

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 2005 Forums

Transact-SQL (2005)

Query for getting missing dates

Author

Topic

surendra_kla
Starting Member

6 Posts

Posted - 2008-02-05 : 05:51:57

I have a table like

FK_ID, Value, Date (here FK_ID is foreign key)

this table getting updated frequently by daily bases that means one record per one day and there is no necessity to insert all days records for every month.

(For example in January month it has maximum 31 records or minimum 0 records, in feb it has maximum 28 or 29 or minimum 0 records, based on calender year)

the table is look like this

FK_ID -- Value -- Date

111 -- 1.11 -- 2008-01-01 12:00:00.000

111 -- 2.11 -- 2008-01-02 12:00:00.000

111 -- 1.12 -- 2008-01-05 12:00:00.000

111 -- 1.12 -- 2008-01-10 12:00:00.000

111 -- 1.12 -- 2008-02-01 12:00:00.000

111 -- 1.12 -- 2008-02-02 12:00:00.000

...

I need to query this table to get missing dates in between particular months

for example for one FK_ID has only 25 records in Jan 2008 month and in feb2008 it has 10 records , so i need to get those missing 6 dates from jan month and 18 dates from feb month

example2 : if the table have Zero records for Jan Month, we need to get all 31 dates

how can i do this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 05:57:26

You need to either use F_TABLE function posted in this forum or should have a calendar table in your db to compare against and return them.

surendra_kla
Starting Member

6 Posts

Posted - 2008-02-05 : 05:59:55

quote:
Originally posted by visakh16

You need to either use F_TABLE function posted in this forum or should have a calendar table in your db to compare against and return them.

I don't have calendar table in my db.

(And can i have the complete URL for F_TABLE function in this forum)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 06:20:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-05 : 06:35:12

You can try this one.Here you need to send the start date and end date of a particular month.
For this particular example I used the month of December.
I sent the start date and end date of December as startdate & enddate for the datediff fucntion.

Declare @t table(Id int,Date datetime)

Insert into @t
select 1,'12/01/2008'union all
select 1,'12/13/2008' union all
select 1,'12/15/2008'

select distinct '12/' + cast(master.dbo.spt_values.number+1 as varchar(20))+ '/2008'from
@t inner join master.dbo.spt_values on master.dbo.spt_values.type='p' where master.dbo.spt_values.number<=datediff(dd,'12/01/2008','12/31/2008')and
'12/' + cast(master.dbo.spt_values.number+1 as varchar(20))+ '/2008' not in
(
select Date from @t

)

Regards

surendra_kla
Starting Member

6 Posts

Posted - 2008-02-05 : 06:51:47

quote:
Originally posted by ayamas

@t inner join spt_values on spt_values.type='p' where

Hi,

Thank you for your reply

I did not get this "spt_values.type='p'" can you explain bit clearly,

is this spt_values is the table name

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-05 : 06:56:30

This is some kind of a internal lookup table used by SQL server.It resides in the master database.
You can try this

select * from master.dbo.spt_values.

You can understand it better.Was first introduced to me by Peso.Thanks to him that I could solve so many problems of this kind of queries.

surendra_kla
Starting Member

6 Posts

Posted - 2008-02-05 : 07:43:36

quote:
Originally posted by ayamas

This is some kind of a internal lookup table used by SQL server.It resides in the master database.
You can try this

select * from master.dbo.spt_values.

You can understand it better.Was first introduced to me by Peso.Thanks to him that I could solve so many problems of this kind of queries.

hi ayamas,

Thanks for your reply, its working, great
can you write this query more specific way, like a procedure with parameters start_date, end_date, FK_ID

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-05 : 07:54:54

You are welcome.
But the drawback is that above query will return the missing dates only for a particular month.Like in the above example I selected December.It wont return values for more than one month.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-05 : 08:09:31

With date range for a single month

Declare @t table(Id int,Date datetime)

Insert into @t
select 1,'12/01/2008'union all
select 1,'12/13/2008' union all
select 1,'12/15/2008'

Declare @startdate datetime
Declare @enddate datetime
Set @startdate='12/01/2008'
Set @enddate='12/31/2008'

Select Distinct
cast(datepart(mm,@startdate)as varchar(10))+ '/' +
cast(master.dbo.spt_values.number+1 as varchar(20))+ '/' +
cast(datepart(YYYY,@startdate)as varchar(10)) from
@t inner join master.dbo.spt_values on master.dbo.spt_values.type='p'
where master.dbo.spt_values.number<=datediff(dd,@startdate,@enddate)and
master.dbo.spt_values.number+1 not in

(
Select datepart(dd,Date) from @t

)

All SQL gurus any other way around?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 08:14:29

In SQL Server 2005, you can generate number table like

select row_number() over (order by s1.name) as number from sysobjects s1 cross join sysobjects s2

and use it in the query

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 08:32:57

quote:
Originally posted by ayamas

With date range for a single month

Declare @t table(Id int,Date datetime)

Insert into @t
select 1,'12/01/2008'union all
select 1,'12/13/2008' union all
select 1,'12/15/2008'

Declare @startdate datetime
Declare @enddate datetime
Set @startdate='12/01/2008'
Set @enddate='12/31/2008'

Select Distinct
cast(datepart(mm,@startdate)as varchar(10))+ '/' +
cast(master.dbo.spt_values.number+1 as varchar(20))+ '/' +
cast(datepart(YYYY,@startdate)as varchar(10)) from
@t inner join master.dbo.spt_values on master.dbo.spt_values.type='p'
where master.dbo.spt_values.number<=datediff(dd,@startdate,@enddate)and
master.dbo.spt_values.number+1 not in

(
Select datepart(dd,Date) from @t

)

All SQL gurus any other way around?

Also consider

Select dateadd(day,number,@startdate) from master.dbo.spt_values
where master.dbo.spt_values.type='p' and dateadd(day,number,@startdate)<=@enddate
and dateadd(day,number,@startdate) not in
(select date from @t)

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-05 : 08:38:00

quote:
Originally posted by madhivanan

quote:
Originally posted by ayamas

With date range for a single month

Declare @t table(Id int,Date datetime)

Insert into @t
select 1,'12/01/2008'union all
select 1,'12/13/2008' union all
select 1,'12/15/2008'

Declare @startdate datetime
Declare @enddate datetime
Set @startdate='12/01/2008'
Set @enddate='12/31/2008'

Select Distinct
cast(datepart(mm,@startdate)as varchar(10))+ '/' +
cast(master.dbo.spt_values.number+1 as varchar(20))+ '/' +
cast(datepart(YYYY,@startdate)as varchar(10)) from
@t inner join master.dbo.spt_values on master.dbo.spt_values.type='p'
where master.dbo.spt_values.number<=datediff(dd,@startdate,@enddate)and
master.dbo.spt_values.number+1 not in

(
Select datepart(dd,Date) from @t

)

All SQL gurus any other way around?

Also consider

Select dateadd(day,number,@startdate) from master.dbo.spt_values
where master.dbo.spt_values.type='p' and dateadd(day,number,@startdate)<=@enddate
and dateadd(day,number,@startdate) not in
(select date from @t)

Madhivanan

Failing to plan is Planning to fail