Query with missing periods - 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 2005 Forums

Transact-SQL (2005)

Query with missing periods

Author

Topic

Jomypgeorge
Starting Member

31 Posts

Posted - 2010-10-18 : 09:03:41

Hi all,

I am dealing with a students point table which has name, from date, to date, and points. now i need to fetch points from this table for a given period. in this i need the empty periods with points 0. how to get it?

table looks like this

Create table #tempTable
(
name nvarchar(20),
FDate smalldatetime,
TDate smalldatetime,
Points decimal(8,2)
)

Insert into #tempTable (name, FDate, TDate, Points )
SELECT 'A' ,'1/6/2010', '1/8/2010',10
UNION ALL
SELECT 'A' ,'1/12/2010', '1/15/2010',15
UNION ALL
SELECT 'A' ,'1/21/2010', '1/24/2010',20

For a given dates say '1/5/2010' and '1/31/2010' i need the result as
A 1/5/2010 1/5/2010 0
A , 1/6/2010, 1/8/2010, 10
A , 1/9/2010, 1/11/2010, 0
A , 1/12/2010, 1/15/2010, 15
A , 1/16/2010, 1/20/2010, 0
A , 1/21/2010, 1/24/2010, 20
A , 1/25/2010, 1/31/2010, 0

How to get this result?
Thanks in advance.....

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-18 : 09:22:16

This should give you and idea.
http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx