Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL Problem: Week between 2 different months Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 3:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:13 PM
Points: 69, Visits: 289
Hello,

I have a script problem.
I calculate data per week (group by week)
However, for indicators, I have business rules which apply in the month.
Here is the problem because I have weeks that are mounted on 2 different months.
I 'd like so:
1 - Identify the weeks that straddle two months
2 - For the week, identify the last day of the month is the day of the week.
3 - If the date is before Wednesday when the week belongs to month
Otherwise, the week belongs to the next month.

I hope I have clearly expressed my need.

And sorry for my English.:))
Post #1389062
Posted Tuesday, November 27, 2012 5:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
Kindly post some sample SQL that would definitely help!

Do read about best-practices link underneath my signature. That will always help you to get response to your queries in timely manner.

Thanks!


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1389130
Posted Tuesday, November 27, 2012 8:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 35,399, Visits: 31,960
Lidou123 (11/27/2012)
Hello,

I have a script problem.
I calculate data per week (group by week)
However, for indicators, I have business rules which apply in the month.
Here is the problem because I have weeks that are mounted on 2 different months.
I 'd like so:
1 - Identify the weeks that straddle two months
2 - For the week, identify the last day of the month is the day of the week.
3 - If the date is before Wednesday when the week belongs to month
Otherwise, the week belongs to the next month.

I hope I have clearly expressed my need.

And sorry for my English.:))


Hi, Lidou123,

What day of the week is the first day of the week for you?

Also, how do you want the weeks numbered? From the start of the year or the start of the month?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1389489
Posted Wednesday, November 28, 2012 2:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:13 PM
Points: 69, Visits: 289
Hi Jeff,

Thank you for your answer.
For my company, the first day of the week is Monday.
And the weeks are numbered by the start of the year.
We use the ISO Calendar.

All I want is to determine when a week is between two months, how many days are in the month M and how many are in the Month M+1.

I found the sql script in a Oracle forum but I don't know to translate it into T-SQL.

This is the URL where i found the Oracle Script. It s a french forum :)) : http://www.developpez.net/forums/d908298/bases-donnees/oracle/sql/nb-jours-semaine-cheval-2-mois/

And this the Oracle script:

WITH cal AS
(
SELECT date '2010-01-01' + level -1 AS dt
FROM dual
connect BY level <= 365
)
, sr AS
(
SELECT to_char(dt, 'yyyy-mm') AS mois ,
to_char(dt, 'iyyy"W"iw') AS iweek,
count(*) AS nb_jours,
count(*) over(partition BY to_char(dt, 'iyyy"W"iw')) AS nb_mois
FROM cal
GROUP BY to_char(dt, 'yyyy-mm'),
to_char(dt, 'iyyy"W"iw')
)
SELECT *
FROM sr
WHERE nb_mois = 2
ORDER BY mois ASC, iweek ASC;

MOIS IWEEK NB_DAY NB_MONTH
------- ------- ---------- ----------
2010-03 2010W13 3 2
2010-04 2010W13 4 2
2010-04 2010W17 5 2
2010-05 2010W17 2 2
2010-05 2010W22 1 2
2010-06 2010W22 6 2
2010-06 2010W26 3 2
2010-07 2010W26 4 2
2010-07 2010W30 6 2
2010-08 2010W30 1 2
2010-08 2010W35 2 2
2010-09 2010W35 5 2
2010-09 2010W39 4 2
2010-10 2010W39 3 2
2010-11 2010W48 2 2
2010-12 2010W48 5 2


I am waiting your answer.
Post #1389597
Posted Wednesday, November 28, 2012 11:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 35,399, Visits: 31,960
Lidou123 (11/28/2012)
Hi Jeff,

Thank you for your answer.
For my company, the first day of the week is Monday.
And the weeks are numbered by the start of the year.
We use the ISO Calendar.

All I want is to determine when a week is between two months, how many days are in the month M and how many are in the Month M+1.

I found the sql script in a Oracle forum but I don't know to translate it into T-SQL.

This is the URL where i found the Oracle Script. It s a french forum :)) : http://www.developpez.net/forums/d908298/bases-donnees/oracle/sql/nb-jours-semaine-cheval-2-mois/

And this the Oracle script:

WITH cal AS
(
SELECT date '2010-01-01' + level -1 AS dt
FROM dual
connect BY level <= 365
)
, sr AS
(
SELECT to_char(dt, 'yyyy-mm') AS mois ,
to_char(dt, 'iyyy"W"iw') AS iweek,
count(*) AS nb_jours,
count(*) over(partition BY to_char(dt, 'iyyy"W"iw')) AS nb_mois
FROM cal
GROUP BY to_char(dt, 'yyyy-mm'),
to_char(dt, 'iyyy"W"iw')
)
SELECT *
FROM sr
WHERE nb_mois = 2
ORDER BY mois ASC, iweek ASC;

MOIS IWEEK NB_DAY NB_MONTH
------- ------- ---------- ----------
2010-03 2010W13 3 2
2010-04 2010W13 4 2
2010-04 2010W17 5 2
2010-05 2010W17 2 2
2010-05 2010W22 1 2
2010-06 2010W22 6 2
2010-06 2010W26 3 2
2010-07 2010W26 4 2
2010-07 2010W30 6 2
2010-08 2010W30 1 2
2010-08 2010W35 2 2
2010-09 2010W35 5 2
2010-09 2010W39 4 2
2010-10 2010W39 3 2
2010-11 2010W48 2 2
2010-12 2010W48 5 2


I am waiting your answer.


There are a couple of fairly simple ways to start this. Please see "Example A" of "Create Function" in Books Online. I won't be able to write any code for this (the example is RBAR, which I recommend avoiding) until after work today. My recommendation is to search for "ISO Calendar Table) on Goolge for a quick legup on this problem, until then.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1390006
Posted Thursday, November 29, 2012 3:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:13 PM
Points: 69, Visits: 289
Thank You Jeff.

Post #1390389
Posted Thursday, November 29, 2012 9:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 35,399, Visits: 31,960
I'm so used to working in 2005 that I didn't think of a possible 2008 solution. Since you're posting in a 2008 forum, can I safely assume that you're using 2008?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1390997
Posted Sunday, December 2, 2012 3:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 35,399, Visits: 31,960
In SQLServer 2008, there is a DATEPART called ISOWK. I've not used it because I don't work with week numbers but that should fill the bill for you.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1391750
Posted Monday, December 3, 2012 8:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 12:13 PM
Points: 69, Visits: 289
Hi Jeff.

The iso_wk function works well

Thank You Jeff

Post #1392006
Posted Monday, December 3, 2012 5:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 35,399, Visits: 31,960
You're welcome. Are you all set on this problem or do you have additional questions on it?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1392195
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse