| Author |
 Topic  |
|
|
cipriani1984
Constraint Violating Yak Guru
United Kingdom
300 Posts |
 Posted - 12/03/2008 : 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
Sweden
29138 Posts |
Posted - 12/03/2008 : 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
India
22461 Posts |
Posted - 12/03/2008 : 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
India
22461 Posts |
Posted - 12/03/2008 : 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
Sweden
29138 Posts |
Posted - 12/03/2008 : 04:38:37
|
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" |
|
|
|
cipriani1984
Constraint Violating Yak Guru
United Kingdom
300 Posts |
Posted - 12/03/2008 : 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
811 Posts |
Posted - 12/03/2008 : 09:44:05
|
2 questions:
where is this procedure "spt_values"?
what is the fiels "c"?
thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/03/2008 : 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
India
47173 Posts |
Posted - 12/03/2008 : 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
India
47173 Posts |
Posted - 12/03/2008 : 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
India
47173 Posts |
Posted - 12/03/2008 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 12/03/2008 : 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
India
47173 Posts |
Posted - 12/03/2008 : 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
USA
7423 Posts |
Posted - 12/05/2008 : 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
Sweden
29138 Posts |
Posted - 12/05/2008 : 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
Sweden
29138 Posts |
Posted - 12/05/2008 : 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" |
Edited by - SwePeso on 12/05/2008 17:25:03 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/08/2008 : 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
Sweden
29138 Posts |
Posted - 12/08/2008 : 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" |
|
|
| |
Topic |
|
|
|
Advice?
