January 4, 2012 at 12:08 pm
Hello,
I need to calculate previous week numbers based on current date. I used datepart function but it is giving me 53. But usually we have only 52 weeks.
We have a function which calculates previous starting date, so I used on that date and it is giving me 53.
Select datepart(wk,(Select SalesWeekStart from fn_PrevSalesWeek(getdate())))
So can you please help me on this?
January 4, 2012 at 12:14 pm
May have to explain more for someone to help you out. Sample data, ddl scripts, and expected output help. Hard to say without seeing under the hood...
January 4, 2012 at 12:18 pm
Ok, the current date is 2012/01/04. So for previous week will be 2011/12/25.
When we use datepart for 2011/12/25, it gives 53. But it must 52 as we have 52 weeks only.
January 4, 2012 at 12:42 pm
Oh, I see what you're talking about now. That's the equivalent of this.
select datepart(wk,'12/25/2011')
Do you want iso_week instead?
Select datepart(iso_week,(Select SalesWeekStart from fn_PrevSalesWeek(getdate())))
January 4, 2012 at 12:43 pm
I think that the only way that you're going to resolve this is to have date table with a week column that, in this case, would hold 52 for 12/25/2011 if that is the business rule for your organization. That's what I've done for data warehouses in the past. What week would 12/26/2010 have been in? I guess that you also just not allow a week 53 in your code, but that doesn't seem accurate.
January 4, 2012 at 12:46 pm
There's an ISO week function on this page that may be applicable too.
http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx
January 4, 2012 at 12:49 pm
The ISO Week for above statement gives 51 but it should be 52.
January 4, 2012 at 12:55 pm
Do your weeks start on Sunday then? So 1/1/2011 was in week 52, not week 1?
January 4, 2012 at 1:03 pm
Shree-903371 (1/4/2012)
Ok, the current date is 2012/01/04. So for previous week will be 2011/12/25.When we use datepart for 2011/12/25, it gives 53. But it must 52 as we have 52 weeks only.
There lies your challenge. There is not 52 weeks. It is actually 52.14...weeks. Every year covers at least parts of 53 weeks. At the very least either the first or last day of the year will fall in week 53 (week 52 of previous year, or week 1 of next year).
You will probably have to get business rules for which week certain dates lie within if there is only 52 weeks to work with.
Take a look at datepart on bol. http://msdn.microsoft.com/en-us/library/ms174420.aspx. This explains the calculation a lot better than I would be able to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 4, 2012 at 1:11 pm
Shree-903371 (1/4/2012)
The ISO Week for above statement gives 51 but it should be 52.
Week 51 of 2011 is the correct ISO week for 2011-12-25. The ISO week is defined by an international standard, but it is not necessarily what your organization defines as a week.
If you really want help with this, you need to provide a definition of exactly what you you mean by "week" in your organization.
Examples of the start and end dates for Week 1, 25, 52, etc. would also help us to help you.
For example, here are ISO Week Start and End dates for 2011 and 2012
ISO_YEAR_WEEK START_OF_WEEK END_OF_WEEK
------------- ------------- -----------
2011-W01 2011-01-03 2011-01-09
2011-W02 2011-01-10 2011-01-16
...
2011-W25 2011-06-20 2011-06-26
2011-W26 2011-06-27 2011-07-03
...
2011-W50 2011-12-12 2011-12-18
2011-W51 2011-12-19 2011-12-25
2011-W52 2011-12-26 2012-01-01
2012-W01 2012-01-02 2012-01-08
2012-W02 2012-01-09 2012-01-15
...
2012-W26 2012-06-25 2012-07-01
2012-W27 2012-07-02 2012-07-08
...
2012-W50 2012-12-10 2012-12-16
2012-W51 2012-12-17 2012-12-23
2012-W52 2012-12-24 2012-12-30
January 4, 2012 at 1:36 pm
Sean and Michael touched on where I was going with my last question. If the datepart function returns data that doesn't match your company's definition of a week then you'd have to use a calendar table that has your business rules for weeks in place or code around the datepart approach with probably case statements. That last option could get messy though depending upon how you define a week. There might be another option I'm not thinking of though.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply