Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 TSQL Problem: Week between 2 different months Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 27, 2012 3:55 AM
 Valued Member Group: General Forum Members Last Login: Today @ 2:03 AM Points: 63, Visits: 243
 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 months2 - 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 monthOtherwise, 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 Group: General Forum Members Last Login: Friday, December 13, 2013 5:08 AM Points: 1,324, Visits: 1,503
Post #1389130
 Posted Tuesday, November 27, 2012 8:40 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:52 PM Points: 34,660, Visits: 28,848
 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 months2 - 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 monthOtherwise, 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1389489
 Posted Wednesday, November 28, 2012 2:48 AM
 Valued Member Group: General Forum Members Last Login: Today @ 2:03 AM Points: 63, Visits: 243
 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 dualconnect 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 calGROUP BY to_char(dt, 'yyyy-mm'), to_char(dt, 'iyyy"W"iw')) SELECT * FROM sr WHERE nb_mois = 2ORDER BY mois ASC, iweek ASC; MOIS IWEEK NB_DAY NB_MONTH------- ------- ---------- ----------2010-03 2010W13 3 22010-04 2010W13 4 22010-04 2010W17 5 22010-05 2010W17 2 22010-05 2010W22 1 22010-06 2010W22 6 22010-06 2010W26 3 22010-07 2010W26 4 22010-07 2010W30 6 22010-08 2010W30 1 22010-08 2010W35 2 22010-09 2010W35 5 22010-09 2010W39 4 22010-10 2010W39 3 22010-11 2010W48 2 22010-12 2010W48 5 2I am waiting your answer.
Post #1389597
 Posted Wednesday, November 28, 2012 11:09 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:52 PM Points: 34,660, Visits: 28,848
 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 dualconnect 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 calGROUP BY to_char(dt, 'yyyy-mm'), to_char(dt, 'iyyy"W"iw')) SELECT * FROM sr WHERE nb_mois = 2ORDER BY mois ASC, iweek ASC; MOIS IWEEK NB_DAY NB_MONTH------- ------- ---------- ----------2010-03 2010W13 3 22010-04 2010W13 4 22010-04 2010W17 5 22010-05 2010W17 2 22010-05 2010W22 1 22010-06 2010W22 6 22010-06 2010W26 3 22010-07 2010W26 4 22010-07 2010W30 6 22010-08 2010W30 1 22010-08 2010W35 2 22010-09 2010W35 5 22010-09 2010W39 4 22010-10 2010W39 3 22010-11 2010W48 2 22010-12 2010W48 5 2I 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1390006
 Posted Thursday, November 29, 2012 3:37 AM
 Valued Member Group: General Forum Members Last Login: Today @ 2:03 AM Points: 63, Visits: 243
 Thank You Jeff.
Post #1390389
 Posted Thursday, November 29, 2012 9:14 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:52 PM Points: 34,660, Visits: 28,848
 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1390997
 Posted Sunday, December 02, 2012 3:17 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:52 PM Points: 34,660, Visits: 28,848
 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1391750
 Posted Monday, December 03, 2012 8:51 AM
 Valued Member Group: General Forum Members Last Login: Today @ 2:03 AM Points: 63, Visits: 243
 Hi Jeff.The iso_wk function works wellThank You Jeff
Post #1392006
 Posted Monday, December 03, 2012 5:13 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 1:52 PM Points: 34,660, Visits: 28,848
 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." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1392195

 Permissions