HOW TO WRITE THIS CODE? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HOW TO WRITE THIS CODE?

Author

Topic

master640
Starting Member

6 Posts

Posted - 2008-04-04 : 09:20:49

OKAY I have one table "Data" with about 7 million records.
Now I want to create the following output table. THe range (1 to 15) can always change in the "data" table. How would I write a script to do this?

Data
Start End Time
1 15 5:45
20 26 2:26
152 160 7:52

Output
Value Time
1 5:45
2 5:45
3 5:45
4 5:45
5 5:45
6 5:45
7 5:45
8 5:45
9 5:45
10 5:45
11 5:45
12 5:45
13 5:45
14 5:45
15 5:45
20 2:26
21 2:26
22 2:26
23 2:26
24 2:26
25 2:26
26 2:26
152 7:52
153 7:52
154 7:52
155 7:52
156 7:52
157 7:52
158 7:52
159 7:52
160 7:52

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 09:26:00

[code]Select Min(Value) as Start, Max(Value) as End, Time
from Table
Group by Time[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 09:56:49

quote:
Originally posted by harsh_athalye

Select Min(Value) as Start, Max(Value) as End, Time
from Table
Group by Time

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

i read it as the other way round. they have the range and need to create a record for every 'number' in between

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 09:59:07

Oh, my bad! I thought it otherwise.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

master640
Starting Member

6 Posts

Posted - 2008-04-04 : 10:07:48

elancaster is correct

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 10:09:57

Try this:

declare @t table
(
	Start int,
	[End] int,
	[Time] datetime
)

insert @t
select 1, 15, '05:45' union all
select 20, 26, '02:26' union all
select 152, 160, '07:52' 

select distinct sv.number as value, t.[Time] from @t t join master..spt_values sv on sv.number between t.Start and t.[End] and sv.number > 0

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 10:17:35

[code]
create table #test (st int, en int, tim datetime)

insert into #test
select 1,15,'05:45' union all
select 20,26,'02:26'

;WITH test (st,en, tim, Level)
AS
(
select st,en,tim,level = st
from #test
UNION ALL
select t.st,t.en,t.tim, level + 1
from #test t
join test
on test.tim = t.tim
where level between t.st and (t.en - 1)
)

select * from test
[/code]

i think it works...?

Em

master640
Starting Member

6 Posts

Posted - 2008-04-04 : 10:18:30

But there is about 7 million of these records and ranges, so I need a more automated query

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-04 : 10:25:47

what do you mean by more automted?

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 10:28:16

Creation of table and insert part is just to mimic sample data. All you need is the final query in your case.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources