dateadd and looping - 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 2008 Forums

Transact-SQL (2008)

dateadd and looping

Author

Topic

papershop
Starting Member

27 Posts

Posted - 2011-07-22 : 21:42:54

Hi reader

i have a table

machineid taskid nterval startdate duedate
---------------------------------------------------
101 101.01 2 01/12/2011 03/12/2011

i insert this table using store procedure

create proc sp_insert

@machineid varchar(10)
@taskid varchar(10)
@interval int
@startdate datetime

as

declare @duedate datetime

set @duedate = dateadd(m,@interval, @startdate)

inset into table (machineid, taskid, interval, startdate, duedate)
values(@machineid, @taskid, @interval, @startdate, @duedate)

now i need that how to looping at sql so when i insert 1 data to the table i have data like this ...

machineid taskid nterval startdate duedate
---------------------------------------------------
101 101.01 2 01/12/2011 03/12/2011
101 101.01 2 03/12/2011 05/12/2011
101 101.01 2 05/12/2011 07/12/2011
101 101.01 2 07/12/2011 09/12/2011
101 101.01 2 09/12/2011 11/12/2011

@papershop

Jason W
Starting Member

19 Posts

Posted - 2011-07-23 : 01:03:42

I'm not quite sure what you are going for, but here is how to implement a while loop:

[url]http://www.sqloptimizationsschool.com/Pages/Advanced%20Concepts/Loops%20vs.%20Cursors.aspx[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-23 : 04:11:33

[code]
create proc sp_insert

@machineid varchar(10)
@taskid varchar(10)
@interval int
@startdate datetime

as

declare @duedate datetime

set @duedate = dateadd(m,@interval, @startdate)

inset into table (machineid, taskid, interval, startdate, duedate)
select @machineid, @taskid, @interval, dateadd(mm,number,@startdate), dateadd(mm,number,@duedate)
from master..spt_values
where type='p'
and number % 2 =0
and dateadd(mm,number,@duedate)<= dateadd(y,1, @startdate)
[/code]

if you dont want to use master..spt_values use a tally table instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-23 : 04:29:15

[code]-- definition of GetDates function
IF OBJECT_ID('dbo.GetDates', 'IF') IS NOT NULL
DROP FUNCTION dbo.GetDates;
GO

CREATE FUNCTION dbo.GetDates(@low AS DATE, @high AS DATE) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT TOP (DATEDIFF(day, @low, @high) + 1) DATEADD(day, rownum - 1, @low) AS dt
FROM Nums
ORDER BY rownum;[/code]

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

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-26 : 01:42:49

SwePeso, cool function. Just a quick question, I'm not familiar with the syntax of the L0 CTE. Can you tell me what function the "Values" plays within that line?

So for example, just writing the select statement:

Select C From (Values (1), (1)) AS D(C)) returns 2 rows:

C
1
1

NOt really following how that works. I can run the overall function with no problem, just not sure about that line.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-26 : 01:46:42

Ah, I think I may have it...I thought values was reserved for Inserts...and the 2 separate values are for multi inserts in MSSQLServer 2008?

So in an insert, would be:

insert into mytable values (1), (1) ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources