October 23, 2008 at 9:13 pm
Need an example of how to calculate week ranges within a given month not to exceed into the next month. For example for September I would need to be able to dynamically create a week startdate and week enddate within a given month based upon when the script would run.
Example: Script runs on Sunday for data between previous Saturday to Sunday.
September week ranges:
1st thru the 6th
7th thru the 13th
14th thru the 20th
21st thru the 27th
28th thru 30th
Don't want to exceed into the next month. Any suggestions would be greatly appreciated.
Here is an example of what I have so far
DECLARE @WeekStart smalldatetime
DECLARE @WeekEnd smalldatetime
SET @WeekStart = (DATEADD(day, DATEDIFF(day, 1, getdate()) / 7 * 7, - 1))
SET @WeekEnd = (DATEADD(day, DATEDIFF(day, 7, getdate() - 1) / 7 * 7 + 7, 5))
/* Had some help with piece from a SQL guru on this site. Don't recall the name*/
IF (DATEPART(month, @WeekStart) <> DATEPART(month, @WeekEnd))
SET @WeekEnd = DATEADD(mm, DATEDIFF(m, 0, @WeekStart) + 1, 0)
It runs okay in the middle of the month, but once it reaches the 1st of the next month it does not run (return any data) have to force manual to run......hard coding values.
Hope this makes sense.
October 24, 2008 at 5:14 am
If I've understood correctly, run dates are the subsequent Sunday after the period being collected.
So, to use your example:
1st thru the 6th - run 7 September
7th thru the 13th - run 14 September
14th thru the 20th - run 21 September
21st thru the 27th - run 28 September
28th thru 30th - run 5 October
If this is the case, how will the data for 1-4 October be selected?
After 5 October, the next run is 12 October. Based on the rules you have given, the run on the 12th will pick up data for 5-11 October, and the data for 1-4 will never be collected.
Is this the issue about which you're asking for help?
If so, on which Sunday should the data for 1-4 October be reported - the 5th or the 12th?
October 24, 2008 at 7:18 am
You are correct to a point. The script will always run on the last day of the range so in your example it would run on the 6th. Sorry not very clear in my explanation.
Yes it does somehow exclude the 1st week of the month that is the stumper on how to fix.
October 24, 2008 at 7:36 am
Sorry, I'm not clear from your answer whether the script runs every Sunday, or every Sunday plus every month end.
October 24, 2008 at 7:40 am
1st thru the 6th - run 6 September
7th thru the 13th - run 13 September
14th thru the 20th - run 20 September
21st thru the 27th - run 27September
28th thru 30th - would run on the 30th
This is the way it would run each month based upon however many days are in the month. Just need weekly data within the month.
Hope this is a little clearer.
October 24, 2008 at 7:55 am
OK - I think I get it now. The reason you're missing the first few days of the month is that the "if" statement assumes you want the last few days of the preceeding month, and provides the same values for @weekstart and @weekend as it did on the last day of the month.
I think the code below does what you want based on the requirements you've given. You should test it with various values of @testdate to be sure.
declare @testdate datetime
set @testdate = '20080906'
--set @testdate = '20080913'
--set @testdate = '20080920'
--set @testdate = '20080927'
--set @testdate = '20080930'
--set @testdate = '20081004'
DECLARE @WeekStart smalldatetime
DECLARE @WeekEnd smalldatetime
SET @WeekStart = (DATEADD(day, DATEDIFF(day, 1, @testdate) / 7 * 7, - 1))
SET @WeekEnd = @testdate
--/* Had some help with piece from a SQL guru on this site. Don't recall the name*/
IF (DATEPART(month, @WeekStart) <> DATEPART(month, @WeekEnd))
SET @WeekStart = DATEADD(dd, datepart(dd,@WeekEnd) * -1, @WeekEnd) + 1
select @WeekStart,@WeekEnd
October 24, 2008 at 12:03 pm
Thank you for your reply Grasshopper and to everybody else.
November 15, 2010 at 1:15 pm
Hi all,
I was looking for examples for my specific situation and found this thread. I thought I would post my solution in case it helped others.
My specific requirement is to run a Crystal report each Monday for the previous weeks sales. I have an app that schedules and runs Crystal and outputs to PDF. I just need to pass the desired date range as parameters. My problem was when a week spans 2 months.
Example: run this report on Monday 12/6/2010
The previous week would span 11/28/2010 through 12/4/2010. The sales manager wants the report to stop at month end.
Using the example in this thread I created:
--Date range for previous week (this will run each Monday)
--If week spans two months, will return 2 date ranges
declare @testdate datetime
set @testdate = getdate()
--get start of last week (previous Sunday)
set @testdate = DateAdd(d, -8, @testdate)
DECLARE @WeekStart1 smalldatetime
DECLARE @WeekEnd1 smalldatetime
DECLARE @WeekStart2 smalldatetime
DECLARE @WeekEnd2 smalldatetime
SET @WeekStart1 = DATEADD(wk, DATEDIFF(wk, 6, @testdate), 6)
SET @WeekEnd1 = DATEADD(wk, DATEDIFF(wk, 5, @testdate), 5)
IF (DATEPART(month, @WeekStart1) <> DATEPART(month, @WeekEnd1))
Begin
SET @WeekStart2 = DATEADD(dd, datepart(dd,@WeekEnd1) * -1, @WeekEnd1) + 1
Set @WeekEnd2 = @WeekEnd1
Set @WeekEnd1 = DATEADD(dd, datepart(dd,@WeekEnd1) * -1, @WeekEnd1)
select @WeekStart1 as 'WeekBegin', @WeekEnd1 as 'WeekEnd'
union
select @WeekStart2 as 'WeekBegin', @WeekEnd2 as 'WeekEnd'
End
Else
select @WeekStart1 as 'WeekBegin', @WeekEnd1 as 'WeekEnd'
So when this runs today (11/15/2010), it will return:
WeekBegin WeekEnd
2010-11-07 00:00:00 2010-11-13 00:00:00
Ans when it runs on 12/6/2010, it will return:
WeekBegin WeekEnd
2010-11-28 00:00:00 2010-11-30 00:00:00
2010-12-01 00:00:00 2010-12-04 00:00:00
My app will simply run the report for each date range that is passed to it.
The midnight times do not matter since this particular database does not use time for this purpose.
November 15, 2010 at 1:18 pm
Honestly, this kind of thing becomes trivial to solve with a proper Calendar table. Anything else requires the kind of complex math you're looking at here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2010 at 1:33 pm
November 16, 2010 at 12:10 pm
Solution to the OP's question using a calendar table:
select
WEEK_NO=
min(a.WEEK_STARTING_TUE_SEQ_NO-b.WEEK_STARTING_TUE_SEQ_NO+1) ,
WEEK_START_DATE=
min(a.[DATE]),
WEEK_END_DATE=
max(a.[DATE])
from
-- Date Table Function F_TABLE_DATE available here:
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
F_TABLE_DATE('20100901','20100930') a
cross join
F_TABLE_DATE('20100901','20100901') b
group by
a.WEEK_STARTING_TUE_SEQ_NO
order by
a.WEEK_STARTING_TUE_SEQ_NO
Results:
WEEK_NO WEEK_START_DATE WEEK_END_DATE
----------- ----------------------- -----------------------
1 2010-09-01 00:00:00.000 2010-09-06 00:00:00.000
2 2010-09-07 00:00:00.000 2010-09-13 00:00:00.000
3 2010-09-14 00:00:00.000 2010-09-20 00:00:00.000
4 2010-09-21 00:00:00.000 2010-09-27 00:00:00.000
5 2010-09-28 00:00:00.000 2010-09-30 00:00:00.000
November 17, 2010 at 2:35 pm
Here's a sample calendar table, with some queries against it.
IF OBJECT_ID('dbo.Calendar', 'U') IS NOT NULL
DROP TABLE dbo.Calendar ;
GO
CREATE TABLE dbo.Calendar (
Date SMALLDATETIME PRIMARY KEY,
DateDay TINYINT NOT NULL,
DateMonth TINYINT NOT NULL,
DateYear INT NOT NULL,
DateWeek INT NOT NULL,
DateWeekDay TINYINT NOT NULL,
WorkDay BIT NOT NULL
DEFAULT (1)) ;
GO
INSERT INTO
dbo.Calendar (
Date,
DateDay,
DateMonth,
DateYear,
DateWeek,
DateWeekDay,
WorkDay)
SELECT
DATEADD(DAY, Number, '1/1/2000'),
DATEPART(DAY, DATEADD(DAY, Number, '1/1/2000')),
DATEPART(MONTH, DATEADD(DAY, Number, '1/1/2000')),
DATEPART(YEAR, DATEADD(DAY, Number, '1/1/2000')),
CASE WHEN Number % 7 = 0 THEN Number / 7 - 1
ELSE (Number) / 7
END,
DATEPART(weekday, DATEADD(DAY, Number, '1/1/2000')),
CASE WHEN DATEPART(weekday, DATEADD(DAY, Number, '1/1/2000')) IN (1, 7)
THEN 0
ELSE 1
END
FROM
DBA.dbo.Numbers ;
-- TODO: Update statements to mark holidays in WorkDay column.
GO
-- Number of working days between two dates
SELECT
COUNT(*)
FROM
dbo.Calendar
WHERE
Date BETWEEN GETDATE() - 30 AND GETDATE() + 30
AND WorkDay = 1 ;
-- Final Date of late month, Day of Week
SELECT
DateWeekDay
FROM
dbo.Calendar
WHERE
Date = DATEADD(MONTH, DATEDIFF(MONTH, '1/1/2000', GETDATE()), '1/1/2000')
- 1 ;
-- First and last days of week in date range
SELECT
MIN(Date),
MAX(Date)
FROM
dbo.Calendar
WHERE
Date BETWEEN GETDATE() - 30 AND GETDATE() + 30
GROUP BY
DateWeek
ORDER BY
DateWeek ;
You can easily find the year and month of the prior month using those columns instead of the weekday one I show, and use that either as a join or in the Where clause of the week-range query I show.
All kinds of date tricks are done easily this way. If you need to, index some of the columns you query against more frequently in it.
If you cross join this table with a WorkHours table, you can find the number of work hours spent on a task with a start and end datetime, even across weekends and shift differences.
If you add in a column, or modify the WorkDay column a bit, you can calculate for half-days.
If you have a PTO table, you can join it to this to calculate work days for a specific person in a time range.
All of those are just few joins and a Count(*) query. No complex math, no cursors, no complex Case statements.
The possibilities just go on and on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply