"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