T-Sql - Calculate Each Monday for a given time period. Help??!!

[CrystalDBA]

I am using SQL Server 2000. I need to query my database for all the
contracts that came in during a certain time frame (user is prompted
for reportingperiodid).

Table - Periods
Fields - Reporting Period id int
Reporting Period desc varchar(30)
Reporting Period Begin Date datetime
Reporting Period End Date datetime

If the user selects a 3 then the begin date is Jan. 1, 2004 and the
end date is June 30, 2004.

Now I need to calculate did any money come in for each week in that
time frame. I need to create a weekly list of all the weeks in that
time frame. Each time frame begins on a Monday. So my list would
look like
1/5/2004
1/12/2004
1/19/2004
1/26/2004

All the way to the end of that time period.

How do I create this weekly list from a given time period using T-SQL?
I would appreciate any and all help on this.

Thanks,

Tony

[Simon Hayes]

"CrystalDBA" <tt******@hotmail.com> wrote in message
news:b0**************************@posting.google.c om...

I am using SQL Server 2000. I need to query my database for all the
contracts that came in during a certain time frame (user is prompted
for reportingperiodid).

Table - Periods
Fields - Reporting Period id int
Reporting Period desc varchar(30)
Reporting Period Begin Date datetime
Reporting Period End Date datetime

If the user selects a 3 then the begin date is Jan. 1, 2004 and the
end date is June 30, 2004.

Now I need to calculate did any money come in for each week in that
time frame. I need to create a weekly list of all the weeks in that
time frame. Each time frame begins on a Monday. So my list would
look like
1/5/2004
1/12/2004
1/19/2004
1/26/2004

All the way to the end of that time period.

How do I create this weekly list from a given time period using T-SQL?
I would appreciate any and all help on this.

Thanks,

Tony

If you're going to do a lot of this sort of thing, then you should probably
create a calendar table - it would make a lot of date-related queries much
easier. It's also useful for dealing with things like holidays, stock market
trading days etc. which vary widely from one place to another:

create table dbo.Calendar (
BaseDate datetime primary key,
DayOfWeek varchar(10) not null,
WeekOfYear int not null,
MonthOfYear varchar(10) not null,
Quarter int not null
/* Add any other useful columns */
)
go

declare @d datetime
set @d = '20040101'
while @d < '20050101'
begin
insert into dbo.Calendar values (
@d,
datename(dw, @d),
datepart(ww, @d),
datename(mm, @d),
datepart(qq, @d))
set @d = dateadd(dd, 1, @d)
end
go

select *
from dbo.Calendar
where DayOfWeek = 'Monday' and
BaseDate between '20040101' and '20040630'
go

Note that the week of the year may vary - see DATEPART in Books Online.

Simon

[John Gilson]

"CrystalDBA" <tt******@hotmail.com> wrote in message
news:b0**************************@posting.google.c om...

I am using SQL Server 2000. I need to query my database for all the
contracts that came in during a certain time frame (user is prompted
for reportingperiodid).

Table - Periods
Fields - Reporting Period id int
Reporting Period desc varchar(30)
Reporting Period Begin Date datetime
Reporting Period End Date datetime

If the user selects a 3 then the begin date is Jan. 1, 2004 and the
end date is June 30, 2004.

Now I need to calculate did any money come in for each week in that
time frame. I need to create a weekly list of all the weeks in that
time frame. Each time frame begins on a Monday. So my list would
look like
1/5/2004
1/12/2004
1/19/2004
1/26/2004

All the way to the end of that time period.

How do I create this weekly list from a given time period using T-SQL?
I would appreciate any and all help on this.

Thanks,

Tony

Here's a UDF that will get all dates, for a specific day of the week, within
a period. The day of the week is given by an integer where Sunday is 1,
Monday is 2,..., and Saturday is 7.

-- Helper views to enumerate nonnegative integers to some upper bound

CREATE VIEW Digits (d)
AS
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9

CREATE VIEW NonnegativeIntegers (n)
AS
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds

CREATE FUNCTION GetDatesInPeriod
(@start_date DATETIME,
@end_date DATETIME,
@day INT)
RETURNS TABLE
AS
RETURN(
SELECT @start_date +
(((@day - DATEPART(WEEKDAY, @start_date) + 7) % 7) +
(7 * I.n)) AS date_of_day,
I.n + 1 AS date_number,
@start_date AS start_period,
@end_date AS end_period
FROM NonnegativeIntegers AS I
WHERE @start_date +
(((@day - DATEPART(WEEKDAY, @start_date) + 7) % 7) +
(7 * I.n)) <= @end_date
)

-- Every Monday from 20040101 to 20040630
SELECT date_of_day, date_number, start_period, end_period
FROM GetDatesInPeriod('20040101', '20040630', 2)
ORDER BY date_number ASC

date_of_day date_number start_period end_period
2004-01-05 00:00:00.000 1 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-01-12 00:00:00.000 2 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-01-19 00:00:00.000 3 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-01-26 00:00:00.000 4 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-02-02 00:00:00.000 5 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-02-09 00:00:00.000 6 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-02-16 00:00:00.000 7 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-02-23 00:00:00.000 8 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-03-01 00:00:00.000 9 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-03-08 00:00:00.000 10 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-03-15 00:00:00.000 11 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-03-22 00:00:00.000 12 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-03-29 00:00:00.000 13 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-04-05 00:00:00.000 14 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-04-12 00:00:00.000 15 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-04-19 00:00:00.000 16 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-04-26 00:00:00.000 17 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-05-03 00:00:00.000 18 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-05-10 00:00:00.000 19 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-05-17 00:00:00.000 20 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-05-24 00:00:00.000 21 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-05-31 00:00:00.000 22 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-06-07 00:00:00.000 23 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-06-14 00:00:00.000 24 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-06-21 00:00:00.000 25 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000
2004-06-28 00:00:00.000 26 2004-01-01 00:00:00.000 2004-06-30 00:00:00.000

Regards,
jag

[Jason Sauer]

Here's a solution that's pretty simple:

CREATE FUNCTION [dbo].[udf_Dates] (@BeginDate datetime, @EndDate datetime,
@DOW int)
-- Where @DOW represents the integer value for the day of the week (Sunday =
1, Monday = 2, Tuesday = 3, etc)

RETURNS @Days TABLE(DOWDate datetime) AS

BEGIN

DECLARE @BDOW int

-- Determine what day of the week your begin date is
SELECT @BDOW = DATEPART(dw, @BeginDate)

-- If the current day of the week is not the desired day, advance the begin
date to that dow
IF @BDOW != @DOW SELECT @BeginDate = CASE WHEN @BDOW < @DOW THEN DATEADD(dd,
@DOW - @BDOW, @BeginDate) ELSE DATEADD(dd, @BDOW - @DOW, @BeginDate) END + 1

-- Then just loop until you get to your end date, adding seven days at a
time
WHILE @BeginDate <= @EndDate BEGIN
INSERT INTO @Days SELECT @BeginDate

SELECT @BeginDate = DATEADD(dd, 7, @BeginDate)
END

RETURN
END

Then just call :

select * from udf_Dates('1/1/2004', '6/30/2004', 2)

and you will get 28 dates from 1/5/2004 to 6/28/2004.

"CrystalDBA" <tt******@hotmail.com> wrote in message
news:b0**************************@posting.google.c om...

I am using SQL Server 2000. I need to query my database for all the
contracts that came in during a certain time frame (user is prompted
for reportingperiodid).

Table - Periods
Fields - Reporting Period id int
Reporting Period desc varchar(30)
Reporting Period Begin Date datetime
Reporting Period End Date datetime

If the user selects a 3 then the begin date is Jan. 1, 2004 and the
end date is June 30, 2004.

Now I need to calculate did any money come in for each week in that
time frame. I need to create a weekly list of all the weeks in that
time frame. Each time frame begins on a Monday. So my list would
look like
1/5/2004
1/12/2004
1/19/2004
1/26/2004

All the way to the end of that time period.

How do I create this weekly list from a given time period using T-SQL?
I would appreciate any and all help on this.

Thanks,

Tony