Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

query that divides month into "business weeks"

Posted on 2011-09-09
6
Medium Priority
?
2,746 Views
Last Modified: 2012-06-27
I need to break a report for date range of annual 09/01/YY - 08/31/YY+1) into weeks and months. Months is straightforward - Jan - Feb - Mar - April, etc.  The weeks are what I can't figure out - what I need is a function like so:

for a given annual period of time, divide results into regular calendar quarters, then calendar months (Q1 = Sept-Oct-Nov; Q2 = Dec-Jan-Feb; etc). Then break the calendar months into business weeks such that a business week is usually, but not always, Monday through Friday.  When the first week in a month doesn't start on a Monday, start that week on the first business day (Tues, wed, thurs, or Friday as applicable) and the week will have fewer than five days so that it ends on the first Friday. WHen the last week in a month doesn't end on Friday, end that week on the month's last business day and the week wil lhave fewer than five days.

End Result:

09/01/2010 - 08/31/2011 - annual period
09/01/2010 - 11/30/2010 = Q1
12/01/2010 - 02/27/2011 = Q2
03/01/2011 - 05/31/2011 = Q3
06/01/2011 - 08/31/2011 = Q4
09/01/2010 - 09/03/2010 = W1  (has only Wed - Friday)
09/06/2019 - 09/10/2010 = W2
09/13/2010 - 09/17/2010 = W3
09/17/2010 - 09/24/2010 = W4
09/27/2010 - 09/27/2010 = W5 (has only Monday - Thursday)
Week 1 - Week 5 belong to September 2010.

Assistance most welcome.
0
Comment
Question by:gberkeley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36512738
I think you meant that last row to be:

09/27/2010 - 09/30/2010 = W5 (has only Monday - Thursday)

Anyway, would Friday 1 Oct be the start of Week 1 (i.e., start the week numbering over with a change in month), or of Week 6 (i.e., continue the week numbering from the prior month)?

Also, what would we do about a month such as Jan 2011?  The 1st of the month is a Saturday; would we deem that the start of a week, and then that Monday, the 3rd, the start of another week?

I'm really not seeing the usefulness of breaking months up into individual weeks...
0
 

Author Comment

by:gberkeley
ID: 36513794
Hi Mathewspatrick:

- Correct.  09/27/2010 - 09/30/2010 = W5 (has only Monday - Thursday)
- Friday 1 Oct be the start of Week 6 (continue the week numbering from the prior month).
- For Jan 2011 the first business week of the month would be 1/3/11 through 1/7/11.
The weeks are divided up the way requested to accommodate performance measurements. Not the way I'd like to be doing it, believe me. I agree with you there. Craziness.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36513812
>>The weeks are divided up the way requested to accommodate performance measurements.

I'd be curious to see how those performance measurements accommodate the fact that a "week" can have anywhere from 1-5 days.

>>Craziness.

I feel your pain :)
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36514057
OK, I would start be creating a "calendar table".  For example:

CREATE TABLE tblCalendar(Dt datetime, FiscalYear int, FiscalQuarter int, 
	FiscalMonth int, FiscalWeek int, Weekday int)

DECLARE @Dt datetime, @EndDt datetime, @FY int, @FM int, @FW int, @WD int, 
	@DayName varchar(20), @FQ int
SET @Dt = '2000-09-01'
SET @EndDt = '2020-08-31'
SET @FW = 0

WHILE @Dt <= @EndDt BEGIN

    SET @FY = YEAR(@Dt) + CASE WHEN MONTH(@Dt) < 9 THEN 0 ELSE 1 END
	SET @FM = MONTH(@Dt)
	SET @DayName = LEFT(DATENAME(dw, @Dt), 2)
	SET @FQ = CASE WHEN MONTH(@Dt) IN (9, 10, 11) THEN 1
		WHEN MONTH(@Dt) IN (12, 1, 2) THEN 2
		WHEN MONTH(@Dt) IN (3, 4, 5) THEN 3
		ELSE 4 END

	IF @DayName = 'Mo' BEGIN
		SET @FW = @FW + 1
		SET @WD = 1 END

	IF @DayName = 'Tu' BEGIN
		IF DAY(@Dt) = 1 SET @FW = @FW + 1
		SET @WD = 2 END

	IF @DayName = 'We' BEGIN
		IF DAY(@Dt) = 1 SET @FW = @FW + 1
		SET @WD = 3 END

	IF @DayName = 'Th' BEGIN
		IF DAY(@Dt) = 1 SET @FW = @FW + 1
		SET @WD = 4 END

	IF @DayName = 'Fr' BEGIN
		IF DAY(@Dt) = 1 SET @FW = @FW + 1
		SET @WD = 5 END

	IF @DayName = 'Sa' SET @WD = 6

	IF @DayName = 'Su' SET @WD = 7

	INSERT INTO tblCalendar (Dt, FiscalYear, FiscalQuarter, FiscalMonth, 
		FiscalWeek, Weekday)
	VALUES (@Dt, @FY, @FQ, @FM, @FW, @WD)

	SET @Dt = DATEADD(day, 1, @Dt)
	IF MONTH(@Dt) = 9 AND DAY(@Dt) = 1 SET @FW = 0

END

Open in new window


With that in place, by joining just about any dated table to that calendar, you can generate your results for any given slice of time.
0
 

Author Closing Comment

by:gberkeley
ID: 36515380
Thank you so very much!!!  There' s no doubt in my mind why you're a "Hall of Famer"!!!!  The proposed solution is exactly what I needed and I could never have solved this on my own. I very much appreciate not just the answer itself, but the fast reply, the excellent questions to make sure you understood what I needed, and the clarity in the responses.  You've made my whole week so much better!!!!  A++++
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36516319
Glad to help :)
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question