I would like to create a view or something that functions like a table that
contains like 4 years of records (1 record per day). Each record only has
that date in it. That's it. Simple enough, except I don't want this to be
a real table because it will be different day by day/year by year and it
needs to be relatively efficient. SQL2000.
What I have so far is:
begin
declare @.currdate SmallDateTime;
Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
Create Table #temp(
aDate SmallDateTime
)
While year(@.CurrDate)<=Year(GetDate())+2
begin
Set @.CurrDate=DateAdd(d,1,@.CurrDate)
Insert Into #temp (aDate) values(@.CurrDate);
end
Select * from #Temp
Drop Table #Temp
end
The problem I see is the use of a temporary table and how that will impact
the server when it gets really loaded. Besides, this is just a procedure, I
need this to function "as if" it were a table to query against.
Any ideas?Why a temporary and not a real one?, you will not hurt your server for havin
g
one, instead, you can make life easier.
Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
AMB
"Jon Glazer" wrote:
> I would like to create a view or something that functions like a table tha
t
> contains like 4 years of records (1 record per day). Each record only has
> that date in it. That's it. Simple enough, except I don't want this to b
e
> a real table because it will be different day by day/year by year and it
> needs to be relatively efficient. SQL2000.
> What I have so far is:
> begin
> declare @.currdate SmallDateTime;
> Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
> Create Table #temp(
> aDate SmallDateTime
> )
> While year(@.CurrDate)<=Year(GetDate())+2
> begin
> Set @.CurrDate=DateAdd(d,1,@.CurrDate)
> Insert Into #temp (aDate) values(@.CurrDate);
> end
> Select * from #Temp
> Drop Table #Temp
> end
> The problem I see is the use of a temporary table and how that will impact
> the server when it gets really loaded. Besides, this is just a procedure,
I
> need this to function "as if" it were a table to query against.
> Any ideas?
>
>|||Lets just say from a learning perspective, how would I achieve what I wish
to here?
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5DC6B54C-311C-4E6E-A07B-1A864041DBE2@.microsoft.com...
> Why a temporary and not a real one?, you will not hurt your server for
> having
> one, instead, you can make life easier.
> Why should I consider using an auxiliary calendar table?
> http://www.aspfaq.com/show.asp?id=2519
>
> AMB
>
> "Jon Glazer" wrote:
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
Example:
use northwind
go
select
identity(int , 0, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
alter table number
add constraint pk_number primary key (number)
go
create function ufn_function1 (
@.sd datetime,
@.ed datetime
)
returns table
as
return (
select
dateadd(day, n.number, @.sd) as col_the_date
from
number as n
where
n.number <= datediff(day, @.sd, @.ed)
)
go
select
*
from
ufn_function1('20050128', '20050204')
order by
1
go
drop function ufn_function1
go
drop table number
go
AMB
"Jon Glazer" wrote:
> Lets just say from a learning perspective, how would I achieve what I wish
> to here?
> Jon
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:5DC6B54C-311C-4E6E-A07B-1A864041DBE2@.microsoft.com...
>
>|||You can't generate data out of nothing. Two alternatives to using an actual
table would be to use a table-valued function containing a loop or to use a
view with a large UNION statement and one SELECT for each date. Either of
those structures would get materialized as data the moment they were used,
so they would offer no obvious benefits over using a table.
Auxiliary tables (Calendars, Numbers, etc) are a standard technique for
doing this sort of thing. After all, even 10 years of dates in a Calendar
table is still less than 4000 rows.
David Portas
SQL Server MVP
--|||If you must...
select
dateadd(year,- 1,dateadd(day,datediff(day,0,getdate()),
0)-datepart(dy,getdate
()))
+digit as dt
from (
select 1 + unit.digit +
10 * ten.digit +
100 * hundred.digit +
1000 * thousand.digit as [number]
from (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as unit(digit)
cross join (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as ten(digit)
cross join (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as hundred(digit)
cross join (
select 0 union select 1 union select 2 ) as thousand(digit)
) numbers(digit)
where digit <= datediff(day,dateadd(year,-1,
dateadd(day,datediff(day,0,getdate()),0)
-datepart(dy,getdate()))
,dateadd(year,3,getdate()-datepart(dy,getdate())))
order by digit
-oj
"Jon Glazer" <jglazer.delete.me@.adconn.com> wrote in message
news:SoxKd.62801$re1.24174@.fe2.columbus.rr.com...
>I would like to create a view or something that functions like a table that
>contains like 4 years of records (1 record per day). Each record only has
>that date in it. That's it. Simple enough, except I don't want this to be
>a real table because it will be different day by day/year by year and it
>needs to be relatively efficient. SQL2000.
> What I have so far is:
> begin
> declare @.currdate SmallDateTime;
> Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
> Create Table #temp(
> aDate SmallDateTime
> )
> While year(@.CurrDate)<=Year(GetDate())+2
> begin
> Set @.CurrDate=DateAdd(d,1,@.CurrDate)
> Insert Into #temp (aDate) values(@.CurrDate);
> end
> Select * from #Temp
> Drop Table #Temp
> end
> The problem I see is the use of a temporary table and how that will impact
> the server when it gets really loaded. Besides, this is just a procedure,
> I need this to function "as if" it were a table to query against.
> Any ideas?
>
sql
