SQL : Making a table with only the past 14 days in 2

[AdamField]

Hey all,

I'm stuck on this problem here and hoping somebody can point me in the right direction

I need for a crystal report a database (SQL prefered) that has the last 14 dates in it and that is autoupdate (so no task that fill's it up daily)

It only needs to have 1 colum (if 2 are needed for a key also no problem) that sais DATE and the filling is just the previeus 14 dates

example:

DATE
16/04/2009
15/04/2009
14/04/2009
13/04/2009
...
02/04/2009

I guess it will be somthing with getdate() to getdate()-14 but can't get it working

All help appriciated

Greetings

Tom

 

[gmmastros]

select  Top 14
        DateAdd(Day, DateDiff(Day, 0, GetDate() - Number), 0)
From    master..spt_values
Where   Type = 'p'
Order By Number

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Welshbird]

Tom, Georges answer would seem to work fine to me.

Just as an extra, the between works better when its chronological, so you need something more like

between getdate()-14 and getdate()

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[AdamField]

This was Exactly what i needed

tnx a 1000 times for this fast fix.


Greetings

Tom

 

[Welshbird]

Tom,

I see you've recently joined our happy band, so just wanted to (gently) point out that it might be nice to click the

Thank gmmastros
for this valuable post!

and award George a star for his help.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[gmmastros]

Fee,

I would ordinarily agree with you (that helpful posts should be marked). However.... I'm not in it for stars. Seriously.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AdamField]

Never the less this seems to be a very resoursefull place and i'm quite the noob in SQL and programming.

Doesn't seem more than logical that if i'm going to "hang" out here i follow the rules

/time for me to read the faQ

greetings

Tom

 

[markros]

You can shed some to me then I love them in any color, but mostly in gold

 

[AdamField]

Hey again,

Got a aditionel problem in this querry now.

I use this view to get the last XX days in crystal report to get a view on sales of the last 14 days in crystal i can do this now.

DATUM (the view).dates > currentdate - 14
this gives me nice a database where crystal can get the dates from

now they want to go further in history (so i did a top 750 as they wanted 2 years back and grouped per week / month) but the SQL stops on 256 rows

Anybody a idee how i can work arround this ?

Thanks in advance !!

Tom

 

[gmmastros]

Let's take a closer look at how this query works. First, realize that master..spt_values is a system table in the master database. When you filter on type = 'P', there is a Number column that goes from 0 to 255.

My recommendation for you is to create a proper numbers table in the database. Every database I have contains a numbers tables with at least 100,000 rows. That may seem like a lot, but this table is very efficient and requires less than 1/2 a meg to store.

The easiest way to create a numbers table is like this...

Create Table Numbers(Num Int Identity(1,1) Primary Key)
go
Insert Into Numbers Default Values
go 100000

You should expect this to take a couple minutes to run, but it only needs to be done once.

Now you can use this numbers table to execute your query. One minor difference is that the numbers table starts at 1 whereas the spt_values table starts at 0. You'll need to adjust your query accordingly (or start your numbers table at 0).

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom