SQL statement to populate Start and End dates

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

General SQL Server Forums

New to SQL Server Programming

SQL statement to populate Start and End dates

Author

Topic

Migs
Starting Member

5 Posts

Posted - 2009-08-31 : 10:59:15

I had the following fields:

start_date = "08/01/09" (starts on)
end_date = "08/31/09" (ends on)
the_length = "1" (everyday)
repeat_every = "2" (with an interval of 2 days)

Now, I want to use SQL statement to populate "daily" but with an interval of "2" days, starting from "start_date" up to "end_date".
I was able to solve the problem on the application side, but I want to use SQL Statement.

NOTE: I was able to solved the problem without using the "repeat_every" field.

I'm looking if Stored Procedure is an option.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-31 : 11:32:49

>>I want to use SQL statement to populate "daily"

Is [Daily] a table? What is the structure of [Daily]?

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-31 : 11:34:41

DECLARE @start_date datetime
SET @start_date = '08/01/09'-- (starts on)
DECLARE @end_date datetime
SET @end_date = '08/31/09'-- (starts on)
DECLARE @the_length int
SET @the_length = 1

DECLARE @repeat_every int
SET @repeat_every = 3

SELECT DATEADD(day,number-1+@the_length,@start_date) FROM
master..spt_values
where type = 'p'
and number % @repeat_every = 0
and DATEADD(day,number+@the_length,@start_date) <= @end_date

Jim

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-31 : 11:37:36

or this?


declare @sDt datetime
       ,@eDt datetime
       ,@l int
       ,@i int

select @sDt = '2009-08-01'
       ,@eDt = '2009-08-31'
       ,@l = 1
       ,@i = 2

;with cte as
(
       select @sDt as d
       union all
       select dateadd(day, @i, d)
       from   cte
       where  dateadd(day, @i, d) <= @eDt
)
--insert [Daily] (start_date, end_date)
select d as start_date
       ,dateadd(day, @l, d) as end_date
from cte

output:
start_date              end_date
----------------------- -----------------------
2009-08-01 00:00:00.000 2009-08-02 00:00:00.000
2009-08-03 00:00:00.000 2009-08-04 00:00:00.000
2009-08-05 00:00:00.000 2009-08-06 00:00:00.000
2009-08-07 00:00:00.000 2009-08-08 00:00:00.000
2009-08-09 00:00:00.000 2009-08-10 00:00:00.000
2009-08-11 00:00:00.000 2009-08-12 00:00:00.000
2009-08-13 00:00:00.000 2009-08-14 00:00:00.000
2009-08-15 00:00:00.000 2009-08-16 00:00:00.000
2009-08-17 00:00:00.000 2009-08-18 00:00:00.000
2009-08-19 00:00:00.000 2009-08-20 00:00:00.000
2009-08-21 00:00:00.000 2009-08-22 00:00:00.000
2009-08-23 00:00:00.000 2009-08-24 00:00:00.000
2009-08-25 00:00:00.000 2009-08-26 00:00:00.000
2009-08-27 00:00:00.000 2009-08-28 00:00:00.000
2009-08-29 00:00:00.000 2009-08-30 00:00:00.000
2009-08-31 00:00:00.000 2009-09-01 00:00:00.000

Be One with the Optimizer
TG

Migs
Starting Member

5 Posts

Posted - 2009-08-31 : 11:37:47

"daily" are the days in certain month.
I only had 1 table and 4 fields (start_date, end_date, the_length, repeat_every).

quote:
Originally posted by TG

>>I want to use SQL statement to populate "daily"

Is [Daily] a table? What is the structure of [Daily]?

Be One with the Optimizer
TG

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-31 : 12:00:15

Still not exactly clear, a sample of what output you expect would be helpful

DECLARE @start_date datetime
SET @start_date = '08/01/09'-- (starts on)
DECLARE @end_date datetime
SET @end_date = '08/31/09'-- (starts on)
DECLARE @the_length int
SET @the_length = 1

DECLARE @repeat_every int
SET @repeat_every = 3

SELECT
[StartDate] = DATEADD(day,number-1+@the_length,@start_date)
,[EndDate] = DATEADD(day,number+@repeat_every,@start_date)
,[TheLength] = @the_length
,[RepeatEvery] = @repeat_every
FROM
master..spt_values
WHERE [type] = 'p'
and number % @repeat_every = 0
and DATEADD(day,number+@the_length,@start_date) <= @end_date

Jim

Migs
Starting Member

5 Posts

Posted - 2009-08-31 : 12:20:02

Here's a sample out. (by the way is there a way of doing this in a plain/single SQL Query without using Stored Procedure, just curious.)

SAMPLE 1:
IF
my_table.start_date = "08/01/09"
my_table.end_date = "08/31/09"
my_table.the_length = 1
my_table.repeat_every = 2

EXPECTED OUTPUT
08/01....08/02
08/03....08/04
08/05....08/06 (so on)

SAMPLE 2:
IF
my_table.start_date = "08/01/09"
my_table.end_date = "08/31/09"
my_table.the_length = 1
my_table.repeat_every = 4

EXPECTED OUTPUT
08/01....08/02
08/06....08/07
08/11....08/12
08/16....08/17 (so on)

SAMPLE 3:
IF
my_table.start_date = "08/01/09"
my_table.end_date = "08/31/09"
my_table.the_length = 3
my_table.repeat_every = 2

EXPECTED OUTPUT
08/01....08/03
08/05....08/07
08/09....08/11
08/13....08/15 (so on)

quote:
Originally posted by jimf

Still not exactly clear, a sample of what output you expect would be helpful

DECLARE @start_date datetime
SET @start_date = '08/01/09'-- (starts on)
DECLARE @end_date datetime
SET @end_date = '08/31/09'-- (starts on)
DECLARE @the_length int
SET @the_length = 1

DECLARE @repeat_every int
SET @repeat_every = 3

SELECT
[StartDate] = DATEADD(day,number-1+@the_length,@start_date)
,[EndDate] = DATEADD(day,number+@repeat_every,@start_date)
,[TheLength] = @the_length
,[RepeatEvery] = @repeat_every
FROM
master..spt_values
WHERE [type] = 'p'
and number % @repeat_every = 0
and DATEADD(day,number+@the_length,@start_date) <= @end_date

Jim

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-31 : 12:38:22

In sample1 each day is (repeat_every) days away from your Start_date
But in sample2 each day is (repeat_every) days away from your End_date.

Which is it?

EDIT:
in Sample3 the length is 3 but the difference between start_date and end_date is only 2 days???

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-31 : 12:44:43

please explain the repeat_every is with reference to what date ?

unless i misunderstood you, the sample you post and the required result does not match. Please confirm

KH
[spoiler]Time is always against us[/spoiler]

Migs
Starting Member

5 Posts

Posted - 2009-08-31 : 12:52:03

Sorry for the sample output.

(repeat_every) is the number of days away from (Start_date)

---

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-31 : 12:54:43

Then I believe my original solution is an option for you.

Be One with the Optimizer
TG

Migs
Starting Member

5 Posts

Posted - 2009-08-31 : 14:31:11

TG,
Your original solution is "PERFECT"!!!
I forgot using DATEADD().

BTW, is it possible to this in a single SQL statement without using Stored Procedure?

Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-31 : 15:02:29

well, you can't assign variable values and select out data in a single statement. So you would have to hard-code the variable values into the statement rather than use the variables. But with the exception of the DECLARE/SELECT var vals, it is one statement.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources