Adding x records by quarter - 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 2000 Forums

Transact-SQL (2000)

Adding x records by quarter

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2004-05-16 : 18:16:29

I have 2 dates; a startdate and an enddate. I need to add 4 records per year over the difference of these two dates to a table. The field of concern being "cost_quarter". I'm tracking costs of an item per quarter over a period of time.

Here is an example:
startdate = 3/1/2003
enddate = 8/1/2010

I need to populate my table with:

cost_quarter-------cost_value
1/1/2003------------NULL
4/1/2003------------NULL
7/1/2003------------NULL
10/1/2003-----------NULL
1/1/2004------------NULL
4/1/2004------------NULL
7/1/2004------------NULL
10/1/2004-----------NULL
1/1/2005------------NULL
4/1/2005------------NULL
7/1/2005------------NULL
10/1/2005-----------NULL
.... and so on

table:
costid int
cost_qtr datetime
cost_value int

Can someone help me with the INSERT INTO query?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-16 : 20:04:57

declare @d datetime
select @d = convert(varchar(4),@startdate,112) + '0101'
while @d <= @enddate
begin
insert tbl (cost_qtr)
select dateadd(mm,d,@d)
from (select d=0 union select 3 union select 6 union select 9) a
where dateadd(mm,d,@d) between @startdate and @enddate
select @d = dateadd(yy,1,@d)
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

davidliv
Starting Member

45 Posts

Posted - 2004-05-16 : 20:44:33

Thanks nr. That's awesome. Much better than the route I was going with it.

Can you explain the logic within the query a bit though? I'm not sure what the 112 and the addition of 0101 is and how would I add additional fields to the insert.

Oh. One problem. Even though the start date may be '5/1/2003' I'll need to create a record for all quarters of 2003.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-16 : 21:03:40

Here is another technique using a tally table:


--create tally table
create table numbers (n int primary key)
declare @n int; set @n = 1
while @n <=100
begin
	insert numbers
	select @n
	set @n = @n+1
end

--perform insert
insert 
	tbl (cost_qtr)
select 
	dateadd(mm,n,'10/1/2002')
from 
	numbers
where 
	n%3 = 0
	and dateadd(mm,n,'1/1/2003') <= '8/1/2010'

davidliv
Starting Member

45 Posts

Posted - 2004-05-16 : 21:06:36

ok. with a little more time i figure everything out. (nr's example)

I added the declaration of @yr1 to capture the 1st year which is then used in the where clause of the insert.

Declare @StartDate datetime
Declare @EndDate datetime
SELECT @StartDate = '8/1/2003'
SELECT @EndDate = '3/11/2008'

declare @d datetime
declare @yr1 datetime
select @d = convert(varchar(4),@startdate,112) + '0101'
select @yr1 = @d
while @d <= @enddate
begin
insert into tbl_acedetail_costs (cost_qtr, fld1, fld2, fld3)
(select dateadd(mm,d,@d),1, 1000, 101 from (select d=0 union select 3 union select 6 union select 9) a
where dateadd(mm,d,@d) between @yr1 and @enddate)
select @d = dateadd(yy,1,@d)
end

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-17 : 04:56:30

>> SELECT @StartDate = '8/1/2003'
Don't use that character date format without an explicit convert style - it will give the wrong date if run with a different default style.
The only unambiguous date formt (I think) is yyyymmdd (style 112)
so
SELECT @StartDate = '20030801'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources