MannuBhai


Hi All,

Can we use the while loop inside a select statement Meaning, something like this:

Code Block

SELECT DATE,

WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23

(SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) )

FROM SC_DATEDIMENSION_TABLE

What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right.

Hope the question is clear.

Thanks a lot.

Mannu.




Re: Using a While inside a select statement

Manivannan.D.Sekaran


You caní»t do loop on the select query you have to do it manually,

Code Block

SELECT

DATE,

DATEADD(HH,6,DATE) [+6],

DATEADD(HH,12,DATE) [+12],

DATEADD(HH,18,DATE) [+18],

DATEADD(HH,23,DATE) [+23]

END

FROM

SC_DATEDIMENSION_TABLE







Re: Using a While inside a select statement

Kent Waldrop Oc07

If you are truely wanting to loop inside of a select statement consider joining to a table of numbers; give a look to this article and maybe this post. For example:

Code Block

select n-1 as number,
dateadd(hh, n-1, '10/19/7')
as hoursOfToday
from numbers
where n <= 24

/* -------- Sample Output: --------
number hoursOfToday
----------- -----------------------
0 2007-10-19 00:00:00.000
1 2007-10-19 01:00:00.000
2 2007-10-19 02:00:00.000
3 2007-10-19 03:00:00.000
...
22 2007-10-19 22:00:00.000
23 2007-10-19 23:00:00.000

(24 row(s) affected)
*/






Re: Using a While inside a select statement

Manivannan.D.Sekaran

The following dynamic SQL also help,

Code Block

Create table #numbers (number int);

Declare @i as int

Set @i=0

While @i<=23

Begin

Insert Into #numbers values(@i)

Select @i = @i + 6

End

Declare @columns as varchar(max);

Declare @prepared_columns as varchar(max);

Set @prepared_columns = ',max(case When number= Then [values] end) as [+ ]'

Set @columns = 'Date'

Select @columns = @columns + Replace(@prepared_columns, ' ', number)

From #numbers

Exec

('

Select ' + @columns + ' From

(select

date,

number,

dateadd(HH,number,date) [values]

from

SC_DATEDIMENSION_TABLE

cross join #numbers) as data

Group by date

')

Drop table #Numbers