drew.allen (6/14/2011)

---

A couple of things that I wanted to point out.

* You use this formula repeatedly in your query DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) I moved it into the CTE so that...

Dan.Humphries (6/14/2011)

---

I might suggest reposting this thread with the scripts as an attachment. The Sample data scipt you posed is too large and causes the browser to lock up....

What Jeff's talking about is using a Tally table.

e.g.

DECLARE @date DATETIME, @number INT

SET @date...

Ordering is not guaranteed when you do not specify order by clause. So if you have no order by clause, then the query optimizer is free to return rows in...

drew.allen (6/3/2011)

---

skcadavre (6/3/2011)

---

When you post sweeping comments, you should probably include a test script that proves what you're claiming. Note, I'm not saying you're wrong. As I said before, I...

drew.allen (6/2/2011)

---

Jnrstevej (6/2/2011)

---

Hi skcadavre,

Thanks for your reply, its not a homework question i'm in the process of designing a SSRS report and i need this section in the store procedure...

Like so: -

DECLARE @date DATETIME, @number INT

SET @date = '2011-06-06 00:00:00'

SET @number = 12

;WITH CTE AS (

SELECT 0 AS months

UNION ALL

SELECT months + 1

FROM CTE

WHERE months < @number-1)

SELECT DATEADD(MONTH,months,@date),

CONVERT(VARCHAR(10),DATEADD(MONTH,months,@date),...

Trigger?

Only need to post once 😉

http://www.sqlservercentral.com/Forums/Topic1118796-338-1.aspx

Is this a homework question? I ask because I answered something almost identical to this a couple of days ago (here). . .

One way would be to do it like...

I guess that should've been my first comment - before complaining about presentation layer...

This is a presentation layer task, not a SQL task.

That being said, one way to do it in SQL would be: -

--Create test data

DECLARE @testdata AS TABLE (dates NTEXT)

INSERT INTO...

One simple way: -

DECLARE @date DATETIME, @number INT

SET @date = '2011-06-01 00:00:00'

SET @number = 6

;WITH CTE AS (

SELECT @number-1 AS months

UNION ALL

SELECT months - 1

FROM CTE

WHERE months > 0)

SELECT...

Whenever possible, I use set-based solutions. But it has occurred in the past where I simply couldn't think one.

For example, this is my answer to a question on this site...

Like this?

--First, lets build some ready consumable test data

DECLARE @TABLE AS TABLE(ID INT IDENTITY, CityName VARCHAR(20))

INSERT INTO @TABLE

SELECT 'Phnom Penh </Title>'

UNION ALL SELECT 'Siem Reap</Title>'

--Select from table

SELECT * FROM @TABLE

--Now...