cciesliga
asked on
Use SQL to get weekly date ranges in bi-monthly period
I need a SQL query to help me determine full weeks (Monday - Sunday) in a bi-monthly period.
For example:
June 1st - June 15th is first bi-monthly period. Week 1: 5/28-6/3. Week 2: 6/4-6/10. Week 3: 6/11-6/17.
June 16th - June 30th is second period. Week 1: 6/11-6/17. Week 2: 6/18-6/24. Week 3: 6/25-7/1.
I will be able to provide the end date for the specific period (ie: 6/15, 6/30, 7/15, 7/31). I need to have the query provide start and end dates for each week within the period.
Ideally, the result would be:
WK1start, WK1end, WK2start, WK2end, WK3start, WK3end
5/28/2007,6/3/2007,6/4/200
Let me know if additional information is needed.
Thanks!
chris.
For example:
June 1st - June 15th is first bi-monthly period. Week 1: 5/28-6/3. Week 2: 6/4-6/10. Week 3: 6/11-6/17.
June 16th - June 30th is second period. Week 1: 6/11-6/17. Week 2: 6/18-6/24. Week 3: 6/25-7/1.
I will be able to provide the end date for the specific period (ie: 6/15, 6/30, 7/15, 7/31). I need to have the query provide start and end dates for each week within the period.
Ideally, the result would be:
WK1start, WK1end, WK2start, WK2end, WK3start, WK3end
5/28/2007,6/3/2007,6/4/200
Let me know if additional information is needed.
Thanks!
chris.
_agx_
Which database are you using?
ASKER
SQL 2000
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
agx:
This seems to work, however, if I run it more than once, I am getting an error message:
There is already an object named 'GetFullWeeks' in the database.
Also, I'm trying to access these dates from an external program, but can't seem to figure out what to call to populate the fields in the other program.
This seems to work, however, if I run it more than once, I am getting an error message:
There is already an object named 'GetFullWeeks' in the database.
Also, I'm trying to access these dates from an external program, but can't seem to figure out what to call to populate the fields in the other program.
ASKER
One more thing, if the 15th is on a Sunday, it returns the 16th - 22nd as week 3, which is outside the range. Should be wk1- 26-1, wk2- 2-8 & wk3- 9-15.
@cciesliga,
You only need run the CREATE FUNCTION once. Then you can use it anywhere just using:
DECLARE @endDate datetime
SET @endDate = cast('2007-06-15' as datetime)
SELECT * FROM dbo.GetFullWeeks(@endDate)
You only need run the CREATE FUNCTION once. Then you can use it anywhere just using:
DECLARE @endDate datetime
SET @endDate = cast('2007-06-15' as datetime)
SELECT * FROM dbo.GetFullWeeks(@endDate)
Try
CREATE FUNCTION dbo.GetFullWeeks(@EndDate datetime)
RETURNS @results table
(
WK1start datetime,
WK1end datetime,
WK2start datetime,
WK2end datetime,
WK3start datetime,
WK3end datetime
)
BEGIN
DECLARE @sundaysDate datetime
-- insert empty row
INSERT INTO @results (WK1Start) VALUES (null)
SET @sundaysDate = @endDate
IF datePart(dw, @endDate) <> 1
BEGIN
SET @sundaysDate = dateAdd(d, 7-datepart(dw, @endDate)+1, @endDate)
END
-- add week3 dates
UPDATE @results
SET WK3start = dateAdd(d, -6, @sundaysDate),
WK3end = @sundaysDate
-- add week2 dates
SET @sundaysDate = dateAdd(d, -7, @sundaysDate)
UPDATE @results
SET WK2start = dateAdd(d, -6, @sundaysDate),
WK2end = @sundaysDate
-- add week1 dates
SET @sundaysDate = dateAdd(d, -7, @sundaysDate)
UPDATE @results
SET WK1start = dateAdd(d, -6, @sundaysDate),
WK1end = @sundaysDate
RETURN
END
GO
CREATE FUNCTION dbo.GetFullWeeks(@EndDate datetime)
RETURNS @results table
(
WK1start datetime,
WK1end datetime,
WK2start datetime,
WK2end datetime,
WK3start datetime,
WK3end datetime
)
BEGIN
DECLARE @sundaysDate datetime
-- insert empty row
INSERT INTO @results (WK1Start) VALUES (null)
SET @sundaysDate = @endDate
IF datePart(dw, @endDate) <> 1
BEGIN
SET @sundaysDate = dateAdd(d, 7-datepart(dw, @endDate)+1, @endDate)
END
-- add week3 dates
UPDATE @results
SET WK3start = dateAdd(d, -6, @sundaysDate),
WK3end = @sundaysDate
-- add week2 dates
SET @sundaysDate = dateAdd(d, -7, @sundaysDate)
UPDATE @results
SET WK2start = dateAdd(d, -6, @sundaysDate),
WK2end = @sundaysDate
-- add week1 dates
SET @sundaysDate = dateAdd(d, -7, @sundaysDate)
UPDATE @results
SET WK1start = dateAdd(d, -6, @sundaysDate),
WK1end = @sundaysDate
RETURN
END
GO
>>Also, I'm trying to access these dates from an external program, but can't seem to figure
>>out what to call to populate the fields in the other program.
The function is just like a regular SELECT query. It returns a resultset so you access it by retrieving the column values: WK1start, WK1end , ...
>>out what to call to populate the fields in the other program.
The function is just like a regular SELECT query. It returns a resultset so you access it by retrieving the column values: WK1start, WK1end , ...
ASKER
Sorry for the delay, got tied up with other things. Thanks agx, this gave me what I needed!