| Author |
Topic |
|
navneet01
Starting Member
6 Posts |
 Posted - 2007-04-02 : 15:27:53
|
| Hi,Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday.Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.Help!!!Regards,Navneet |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-02 : 15:43:17
|
| We need more detail about the definition of the week. Can the same week span months or years (include data from two different months or years). What is the naming convention for your weeks? Are you following the ISO week definition?CODO ERGO SUM |
 |
|
|
navneet01
Starting Member
6 Posts |
Posted - 2007-04-02 : 15:57:58
|
| To explain, Let me give an example.I have table with few columns listed below.1)Date (Format, eg:2007-03-16) The date goes on.2)Server IP Address --Format(5.205.158.58)3)Server Utilization --Format(1.36)and some more.Hence I require an average of Server utilization of a server for a week. The week ranges from Week1 to Week5.Week Starts from Monday.Hence If I calculate Server Utilization for Week1 it should conside data for the month of (eg: April) from 2nd April to 8th April as week starts from First Monday of the Month.PLease let me know if any more info is requiredRegards,Navneet |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-03 : 01:52:33
|
| Yes, as MVJ stated, what happens over new year? Between December 31 and January 1? Where is week 1 then?Peter LarssonHelsingborg, Sweden |
|
|
|
navneet01
Starting Member
6 Posts |
Posted - 2007-04-03 : 06:20:28
|
| If we consdier for the period from December 2006 to Jan 2007, then For December it will have 4 weeks i.e Starting from 4th Dec which is first Monday of the Month. and continues till Sunday.Hence (Week 4) for December 2006 will be from 25th dec to 31st December.If the report was generated on 2 nd Jan then once again week 1 will start form 1st Jan 2007.(1st Monday of the Month)This is a requirement for a report which has to generated every day.If the report is generated on Jan 2 2007, then for week 1 it should consider data from 1st Jan to 7 th Jan. But Since the data is not available in the warehouse for 3 rd Jan, it will consider data for Week 1 from Jan 1st to Jan 2nd 2007.2)In the period of April 2007.There will be 5 weeks, as it has 5 Mondays.The week 5 will start from 30th April to 6th May 2007.i.e if the report is generated on 5th May, then it will calcuate data from 30th April to 6th May 2007 as Week 5.and the report will also calculate data from 2nd April 2007 as Week 1 and so on till Week 5.This is how I need calculate data weekly wise. Hence I Need to select only that data which falls under the date range for the respective week.Hence the requirement is for date range which should automatically give the date range for all the weeks for the current Month. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-03 : 06:40:39
|
| What for those years where December 29 is a monday?Peter LarssonHelsingborg, Sweden |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
navneet01
Starting Member
6 Posts |
Posted - 2007-04-03 : 09:33:27
|
avneet[quote]Originally posted by Peso
What for those years where December 29 is a monday?If December 29 is a monday? then it should see which monday is it of a month.If its 5th Monday, or 4 th Monday, the date range it will consider will be from Monday to Sunday. i.e 29th December to 4th Jan. Hence report generated on 3 rd Jan will show data for all the weeks for the month December to Jan Current date.The Report generated will have below columns.Week1 Week2, Week 3, Week 4, Week 5.Please Help!Regards,Navneet |
|
|
|
navneet01
Starting Member
6 Posts |
Posted - 2007-04-03 : 09:46:26
|
| All I need is a functionwhich can calculte date range based on weekly wise.i.e The date Range for Week1 should start from 1st monday of the Month till next Sunday.If a month has 5 Mondays and if its on 29th or 30th of a month then the date will be for Week 5 with date range from 5 th Monday to Sunday of the Next Month.The same goes for New Year.if 29 th Dec is Monday,and it 5 th Monday then this will be Week 5 for December and date range will be from Monday to Sunday( Where is the first sunday of the month)Help!Regards,Navneet |
|
|
|
navneet01
Starting Member
6 Posts |
Posted - 2007-04-03 : 09:46:42
|
| All I need is a functionwhich can calculte date range based on weekly wise.i.e The date Range for Week1 should start from 1st monday of the Month till next Sunday.If a month has 5 Mondays and if its on 29th or 30th of a month then the date will be for Week 5 with date range from 5 th Monday to Sunday of the Next Month.The same goes for New Year.if 29 th Dec is Monday,and it 5 th Monday then this will be Week 5 for December and date range will be from Monday to Sunday( Where is the first sunday of the month)Help!Regards,Navneet |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Need weekly wise data