Return a list of the last 30 days

Question

Return a list of the last 30 days

Dom Horton

SSCrazy

Points: 2202
More actions
November 18, 2010 at 7:19 am

#243062

Hello,
Can anyone point me in the right direction? I'm after a 'self contained' select statement that will simply return a list of the last 30 days. It cannot use CTEs nor can it refer to a numbers table or the like.
thanks,
Dom

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Dom Horton (11/18/2010)
Hello,
Can anyone point me in the right direction? I'm after a 'self contained' select statement that will simply return a list of the last 30 days. It cannot use CTEs nor can it refer to a numbers table or the like.
thanks,
Dom

Why the restrictions, Dom?

If it can't refer to a numbers table (or the like), can it use a system table as a source of rows?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dom Horton SSCrazy Points: 2202 More actions · Answer 2

Thanks for the reply Chris,

I'm actually trying to write a 'Sql command' in Crystal Reports. I've a report that lists application usage over time....I'm trying to find usage or lack of over the last 30 days....whereby a join the '30 day list' to the usage (I may even be not going about this effectively!...open to suggestions).

I can't create temp tables, but could possibly refer to an existing table in the db to use as a source of rows.

thanks,

dom

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Dom

I did a lot of Crystal reports from 3 to 2 years ago. Stored procedures were far away the best data source then, but I can't even remember the Crystal version. Can you use a sproc, and integrate this 30-day rowsource into it? If not, it's easy enough to do it using system tables:

SELECT Reportdate = DATEADD(dd, -d.rn, DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))

FROM (

SELECT TOP 30 rn = ROW_NUMBER() OVER (ORDER BY Name) -1 -- starts at rn = 0 i.e. today

FROM master.dbo.syscolumns

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dom Horton SSCrazy Points: 2202 More actions · Answer 4

That's great Chris...very useful (and can be adapted for various uses)

Unfortunately I'm not allowed to create sprocs! Just writing 'sql commands' is rocket science to those who require the reports!

Once again thanks for the swift and prompt response.

cheers,

dom

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Dom - Can you use a table variable?

DECLARE @I INT

SET @I = 0

DECLARE @T AS TABLE(d DATETIME)

WHILE @I < 30

BEGIN

INSERT INTO @T

SELECT DATEADD (dd , -@I, GETDATE())

SET @I = @I + 1

END

--To test results

SELECT D FROM @T ORDER BY D

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dom Horton SSCrazy Points: 2202 More actions · Answer 6

Hi Ron,

Thanks for the reply but Crystal Reports XI doesn't support table variables.

For info/closure I've rejigged the code supplied by Chris to work with my clients SQL Server 2k db.

SELECT DATEADD(dd, -d.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate

FROM (

SELECT TOP 30 number rn FROM master.dbo.spt_values

WHERE NAME is null

) d

cheers,

dom