Sql Server Date Range - 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)

Sql Server Date Range

Author

Topic

Bryce Covert
Starting Member

12 Posts

Posted - 2005-07-06 : 18:08:22

I'm not sure how to describe this, but I know it's a frequent problem among SQL developers. I believe the solution has bean a pivot table in Access, but I'm not sure how to implement a similiar result in SQL Server.

Basically, I would like dates to be independent from actual records. It would sort of be like doing outer join. So I could get daily records when I might not necessarily have daily records. For example, I would like to get data like this:

Date Amount
--------
1/1/2005 <NULL>
1/2/2005 <NULL>
1/3/2005 100
1/4/2005 <NULL>

Any way of doing this? I think it's probably possible using some (lots) of subqueries, but that would not be worth the effort.

Thanks in advance,
Bryce

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-06 : 19:31:19

Create a table called MyDateTable, load it with the dates you are interested in, and then run this query:


select
	a.Date,
	b.Amount
from
	MyDateTable a
	left join
	MyDataTable b
	on (a.Date = b.Date)
where
	a.Date between @from_Date and @to_Date

CODO ERGO SUM

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-06 : 19:41:09

I agree with Michael, creating a date table is the way to go.

You can also create this table on the fly, though I only wrote this as an example to show you how the date table would work.



-- date table
declare @dateRange table (dtStamp datetime)

-- your data table
declare @sales table (dtStamp datetime, salesQty int)
insert into @sales 
	select getdate() - 10, 52 union all
	select getdate() - 10, 35 union all
	select getdate() - 9, 79 union all
	select getdate() - 7, 42 union all
	select getdate() - 7, 16 union all
	select getdate() - 7, 76 union all
	select getdate() - 6, 34 union all
	select getdate() - 5, 22 union all
	select getdate() - 4, 69


declare @startDate datetime,
	@endDate datetime

select 	@startDate = getdate()-10,
	@endDate = getdate()

-- populate range of dates
while @startDate < @endDate
begin
	insert into @dateRange
	select @startDate
	
	set @startDate = dateadd(dd, 1, @startDate)
end

-- left join on range, group by day, sum amounts
select 
	Convert(varchar(10), dr.dtStamp, 101), 
	sum(s.salesQty) 
from @dateRange dr left join @sales s
	on datepart(dayofyear, dr.dtStamp) = datepart(dayofyear, s.dtStamp)
group by dr.dtStamp

-- edit, fixed convert typo

Nathan Skerl

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-06 : 20:05:27

If you need to generate the date table on the fly, you can do it with the table function from this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

This query is the same as before modified to use a derived date table returning 1000 dates starting with 2005/1/1


select
	a.Date,
	b.Amount
from
	(
	select
		Date = dateadd(dd,aa.number,'2005/1/1 00:00:00')
	from
		dbo.F_TABLE_NUMBER_RANGE(0,1000) aa
	) a
	left join
	MyDataTable b
	on (a.Date = b.Date)
where
	a.Date between @from_Date and @to_Date

CODO ERGO SUM

Bryce Covert
Starting Member

12 Posts

Posted - 2005-07-07 : 10:44:28

Thanks! This works perfectly! Although, I'd sooner just figure out the number of days and use that as a parameter for the function. speaking of which, that function is intense... i'm not quite certain I understand it. :)

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-07 : 11:19:33

This should do what you want.

Yes, the function is a little complex, but it is the result of a lot of development/testing to find the fastest way to generate a table of numbers. I don't really have time to explain it in detail right now.


declare @from_Date	datetime
declare @to_Date	datetime
declare	@days		int

select	@from_Date	= '2005/01/01 00:00:00',
	@to_Date	= '2005/02/28 00:00:00'

select @days	= datediff(dd,@from_Date,@to_Date)

select
	a.Date,
	b.Amount
from
	(
	select
		Date = dateadd(dd,aa.number,@from_Date)
	from
		dbo.F_TABLE_NUMBER_RANGE(0,@days) aa
	) a
	left join
	MyDataTable b
	on (a.Date = b.Date)
order by
	a.Date

quote:
Originally posted by Bryce Covert

Thanks! This works perfectly! Although, I'd sooner just figure out the number of days and use that as a parameter for the function. speaking of which, that function is intense... i'm not quite certain I understand it. :)

Thanks!

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources