SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Advice?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 12/03/2008 :  04:30:01  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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

Sweden
30250 Posts

Posted - 12/03/2008 :  04:36:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use Cross Apply.
Visakh will show you shortly.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/03/2008 :  04:36:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/03/2008 :  04:37:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 12/03/2008 :  04:38:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 12/03/2008 :  05:24:37  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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"


Go to Top of Page

inbs
Aged Yak Warrior

836 Posts

Posted - 12/03/2008 :  09:44:05  Show Profile  Reply with Quote
2 questions:
where is this procedure "spt_values"?
what is the fiels "c"?

thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2008 :  10:16:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2008 :  10:20:25  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2008 :  10:26:19  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2008 :  11:02:34  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/03/2008 :  12:54:58  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/03/2008 :  13:43:13  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/05/2008 :  16:49:49  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 12/05/2008 :  17:19:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 12/05/2008 :  17:21:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 12/05/2008 17:25:03
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 12/08/2008 :  09:24:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30250 Posts

Posted - 12/08/2008 :  11:24:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000