Advice? - 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)

Advice?

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-03 : 04:30:01

Hi,

Again, I dont know how to phrase this, so I will just show example.

I have the following table.

Coursecode, startweek, endweek
1000AAA, 7, 13

What I wanna do is expand this from 1 row, with an extra field to:

Coursecode, startweek, endweek, weekno
1000AAA, 7, 13, 7
1000AAA, 7, 13, 8
1000AAA, 7, 13, 9
1000AAA, 7, 13, 10
1000AAA, 7, 13, 11
1000AAA, 7, 13, 12
1000AAA, 7, 13, 13

So itll spread them out from the ranging values in start week and end week. Itll differ as some other courses are spread through different weeks.

Any ideas?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 04:36:13

Use Cross Apply.
Visakh will show you shortly.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 04:36:49

declare @t table(Coursecode varchar(20), startweek int, endweek int)
insert into @t
select '1000AAA', 7, 13

select Coursecode,startweek,endweek,startweek+number as weekno
from @t cross join master..spt_values
where type='p' and startweek+number<=endweek

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 04:37:39

quote:
Originally posted by Peso

Use Cross Apply.
Visakh will show you shortly.

E 12°55'05.63"
N 56°04'39.26"

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 04:38:37

[code]DECLARE @Sample TABLE
(
CourseCode VARCHAR(10),
StartWeek TINYINT,
EndWeek TINYINT
)

INSERT @Sample
SELECT '1000AAA', 7, 13

SELECT *
FROM @Sample

SELECT s.*,
t.c AS WeekNo
FROM @Sample AS s
CROSS APPLY (
SELECT Number
FROM master..spt_values AS v
WHERE Type = 'P'
AND Number BETWEEN s.StartWeek AND s.EndWeek
) AS t(c)[/code]Strange... Visakh bot must be out of order

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-03 : 05:24:37

Thank you!

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		CourseCode VARCHAR(10),
		StartWeek TINYINT,
		EndWeek TINYINT
	)

INSERT	@Sample
SELECT	'1000AAA', 7, 13

SELECT	*
FROM	@Sample

SELECT		s.*,
		t.c AS WeekNo
FROM		@Sample AS s
CROSS APPLY	(
			SELECT	Number
			FROM	master..spt_values AS v
			WHERE	Type = 'P'
				AND Number BETWEEN s.StartWeek AND s.EndWeek
		) AS t(c)

Strange... Visakh bot must be out of order

E 12°55'05.63"
N 56°04'39.26"

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-03 : 09:44:05

2 questions:
where is this procedure "spt_values"?
what is the fiels "c"?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 10:16:52

quote:
Originally posted by Peso

Use Cross Apply.
Visakh will show you shortly.

E 12°55'05.63"
N 56°04'39.26"

Sorry
I just rebooted my bot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 10:20:25

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		CourseCode VARCHAR(10),
		StartWeek TINYINT,
		EndWeek TINYINT
	)

INSERT	@Sample
SELECT	'1000AAA', 7, 13

SELECT	*
FROM	@Sample

SELECT		s.*,
		t.c AS WeekNo
FROM		@Sample AS s
CROSS APPLY	(
			SELECT	Number
			FROM	master..spt_values AS v
			WHERE	Type = 'P'
				AND Number BETWEEN s.StartWeek AND s.EndWeek
		) AS t(c)

Strange... Visakh bot must be out of order

E 12°55'05.63"
N 56°04'39.26"

System just back up and running

Looks like failover happened at right time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 10:26:19

Also


;With WeekData_CTE (Coursecode, startweek, endweek, weekno) AS
(
SELECT Coursecode, startweek, endweek,CAST(NULL AS int)
FROM table
UNION ALL
SELECT Coursecode, startweek, endweek, startweek+1
FROM WeekData_CTE
WHERE startweek+1<= endweek
)
SELECT Coursecode, startweek, endweek, weekno FROM WeekData_CTE

OPTION (MAXRECURSION 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 11:02:34

quote:
Originally posted by inbs

2 questions:
where is this procedure "spt_values"?
what is the fiels "c"?

thanks

spt_values is a internal count table with SQL Server.
c is alias for derived table column.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-12-03 : 12:54:58

Visakh, That CTE seems to go into an infinate loop. Here is an updated version that seems to work:

;With WeekData_CTE (Coursecode, startweek, endweek, weekno) AS
(
	SELECT 
		Coursecode, startweek, endweek, startweek AS WeekNo
	FROM 
		@Sample
		
	UNION ALL

	SELECT 
		CTE.Coursecode, CTE.startweek, CTE.endweek, WeekNo + CAST(1 AS TINYINT)
	FROM 
		WeekData_CTE AS CTE
	INNER JOIN
		@Sample AS S
		ON CTE.CourseCode = S.CourseCode
	WHERE 
		CTE.WeekNo + 1 <= S.endweek
)

SELECT Coursecode, startweek, endweek, weekno FROM WeekData_CTE

OPTION (MAXRECURSION 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:43:13

quote:

Originally posted by Lamprey

Visakh, That CTE seems to go into an infinate loop. Here is an updated version that seems to work:

;With WeekData_CTE (Coursecode, startweek, endweek, weekno) AS
(
	SELECT 
		Coursecode, startweek, endweek, startweek AS WeekNo
	FROM 
		@Sample
		
	UNION ALL

	SELECT 
		CTE.Coursecode, CTE.startweek, CTE.endweek, WeekNo + CAST(1 AS TINYINT)
	FROM 
		WeekData_CTE AS CTE
	INNER JOIN
		@Sample AS S
		ON CTE.CourseCode = S.CourseCode
	WHERE 
		CTE.WeekNo + 1 <= S.endweek
)

SELECT Coursecode, startweek, endweek, weekno FROM WeekData_CTE

OPTION (MAXRECURSION 0)

Ah..i see that...couldnt test it then..
Thanks for sorting it out

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-12-05 : 16:49:49

CROSS APPLY and recursive CTE's are fun, but a CROSS JOIN is all that is needed here and that will be the most efficient.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 17:19:18

They are more than fun. They are useful too

DECLARE	@Sample TABLE
	(
		CourseCode VARCHAR(10),
		StartWeek TINYINT,
		EndWeek TINYINT
	)

INSERT	@Sample
SELECT	'1000AAA', 7, 13

-- Peso
SELECT		s.*,
		t.c AS WeekNo
FROM		@Sample AS s
CROSS APPLY	(
			SELECT	Number
			FROM	master..spt_values AS v
			WHERE	Type = 'P'
				AND Number BETWEEN s.StartWeek AND s.EndWeek
		) AS t(c)

-- Madhi
select Coursecode,startweek,endweek,startweek+number as weekno
from @sample cross join master..spt_values
where type='p' and startweek+number<=endweek

-- visakh16
;With WeekData_CTE (Coursecode, startweek, endweek, weekno) AS
(
	SELECT 
		Coursecode, startweek, endweek, startweek AS WeekNo
	FROM 
		@Sample
		
	UNION ALL

	SELECT 
		CTE.Coursecode, CTE.startweek, CTE.endweek, WeekNo + CAST(1 AS TINYINT)
	FROM 
		WeekData_CTE AS CTE
	INNER JOIN
		@Sample AS S
		ON CTE.CourseCode = S.CourseCode
	WHERE 
		CTE.WeekNo + 1 <= S.endweek
)
SELECT Coursecode, startweek, endweek, weekno FROM WeekData_CTE
OPTION (MAXRECURSION 0)

And results according to SQL Profiler are...


Peso        6 reads
Madhi      12 reads
Visakh16   82 reads

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-05 : 17:21:40

If you modify madhi's query to this

-- Madhi modified
select Coursecode,startweek,endweek,startweek+number as weekno
from @sample cross join master..spt_values
where type='p' and number<=endweek-startweek

the query need 6 reads too.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-08 : 09:24:56

quote:
Originally posted by Peso

If you modify madhi's query to this
-- Madhi modified
select Coursecode,startweek,endweek,startweek+number as weekno
from @sample cross join master..spt_values
where type='p' and number<=endweek-startweek
the query need 6 reads too.

E 12°55'05.63"
N 56°04'39.26"

Thanks. Interesting

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 11:24:26

That's because Number column is indexed. When you do calculation over an indexed column, you loose the index seek and get index scan instead. Which is almost same as table scan.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources