Generate missed date - SQL Server Forums

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

SQL Server Development (2000)

Generate missed date

Author

Topic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-12-13 : 08:45:04

Hi,
I have a table datetest having the records

Dat Value
2004-11-10 00:00:00.000 1
2004-11-11 00:00:00.000 3
2004-11-12 00:00:00.000 23
2004-11-14 00:00:00.000 7
2004-11-16 00:00:00.000 87

I want to generate the missed dates with value 0. My desired output is

Dat Value

2004-11-10 00:00:00.000 1
2004-11-11 00:00:00.000 3
2004-11-12 00:00:00.000 23
2004-11-13 00:00:00.000 0
2004-11-14 00:00:00.000 7
2004-11-15 00:00:00.000 0
2004-11-16 00:00:00.000 87

I used the following query


select distinct * from datetest
union
Select distinct * from(
select (Select case when t1.dat-t2.dat=1 then t1.dat-1 else t2.dat end as new from datetest t1
where t1.dat-t2.dat=1) dat,0 value
from datetest t2 
union
select(Select case when t1.dat-t2.dat=2 then t1.dat+1 else t2.dat end as new from datetest t1
where t1.dat-t2.dat=2) dat,0 value
from datetest t2 
) t1 where dat is not null

which produced the result

2004-11-10 00:00:00.000 0
2004-11-10 00:00:00.000 1
2004-11-11 00:00:00.000 0
2004-11-11 00:00:00.000 3
2004-11-12 00:00:00.000 23
2004-11-13 00:00:00.000 0
2004-11-14 00:00:00.000 7
2004-11-15 00:00:00.000 0
2004-11-16 00:00:00.000 87
2004-11-17 00:00:00.000 0

From this I want the required result
But this query will work only if the difference between successive dates is 1
Is there any generalised way?

Madhivanan

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-13 : 08:48:22

See
http://www.nigelrivett.net/SQLTsql/FindGapsInSequence.html

But to get the data in the format you have will need a tally table with all dates which you can join to.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-12-14 : 05:33:54

Thanks nr. It helped me

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources