Stored Procedure help - 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)

Stored Procedure help

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-23 : 06:57:16

Hi,

I have the following table;

acad_period, start_date, end_date
07/08, 2007-07-29, 2008-07-31
08/09, 2008-08-01, 2009-07-31

Im hoping to create a row for each individual day in the academic year so should look like the following;

acad_period, start_date, end_date, day
07/08, 2007-07-29, 2008-07-31, 2007-07-29
07/08, 2007-07-29, 2008-07-31, 2007-07-30
07/08, 2007-07-29, 2008-07-31, 2007-07-31
AND SO ON...

Ive used the following query which ive used on data similary to this but no luck. Any help??

select s.*, t.c AS day
from #temp_stsacper AS s cross apply
(select Number FROM master..spt_values AS v
where type = 'P' AND Number BETWEEN s.start_date AND s.end_date) AS t (c)

Thanks Cipriani

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 06:59:07

why u have used cross apply and can u post ur desired output

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-23 : 07:02:09

The above was my desired output;

acad_period, start_date, end_date, day
07/08, 2007-07-29, 2008-07-31, 2007-07-29
07/08, 2007-07-29, 2008-07-31, 2007-07-30
07/08, 2007-07-29, 2008-07-31, 2007-07-31
AND SO ON...

So a row for each day between start_date and end_date in a field called days group in acad_period as there will be multiple.

Thanks

quote:
Originally posted by bklr

why u have used cross apply and can u post ur desired output

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 07:27:42

check this two queries


declare @t table(acad_period varchar(32), start_date datetime, end_date datetime)
insert into @t select 
'07/08', '2007-07-29', '2008-07-31' union all select
'08/09', '2008-08-01', '2009-08-04'
---1
select distinct acad_period,start_date,end_date, start_date + number as dates  from @t 
inner join master..spt_values m on m.type='p'
where  number <= datediff(d,start_date,end_date)

---2
select *,replace(end_Date,year(end_Date),year(start_date))as val into #temp from @t 

select distinct acad_period,start_date,end_date, start_date + number as dates  from #temp 
inner join master..spt_values m on m.type='p'
where  number <= datediff(d,start_date,CONVERT(DATETIME, VAL))
order by acad_period

drop table #temp

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-23 : 08:36:35

spt_values looks very useful, is it standard to use? (will it be deprecated in future versions etc)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-23 : 08:37:37

Use same approach showed to you in your other question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124297

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

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-23 : 08:58:27

But it didnt understand the approach on both.

Im more after the understanding then just getting a quick answer.

I have the following table;

acad_period, start_date, end_date
07/08, 2007-07-29, 2008-07-29
08/09, 2008-07-30, 2009-07-29

I just want to expand this table and add rows for each year, for the days inbetween, so a row for each day in the year between the start and end dates.

I was looking into using a cursor but im always advised here to use ..spt_values.

Any tips?

quote:
Originally posted by Peso

Use same approach showed to you in your other question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124297

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

maeenul
Starting Member

20 Posts

Posted - 2009-04-23 : 11:38:41

select row_number() over (order by name) as int_seq
into #temp
from sys.all_objects

this will create a integer sequence starting from 1 upto the no of rows in the sys.all_objects table.

select start_date, start_date+int_seq
from test1,
#temp
where start_date+int_seq < end_date

then this will generate all the dates between the start and end_date.

Cheers

-----------------------
maeenul
http://www.programmingsolution.net
http://sqlservertipsntricks.blogspot.com

maeenul
Starting Member

20 Posts

Posted - 2009-04-23 : 11:40:45

sorry, one more thing to mention. The columns must be datetime so that the addition of integer with the datetime works. If your column is not datetime, then you can cast it to datetime in the query.

-----------------------
maeenul
http://www.programmingsolution.net
http://sqlservertipsntricks.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources