November 2, 2015 at 1:44 pm
Hi guys,
Hope you can help me as I'm stuck. I am trying to create a TSQL function that returns the Retail Week No for a given date. A Retail Calendar table is OK as well just having hard time trying to put the logic behind the Retail Week number.
Retail Week Requirements:
Starts on: February 1st
Ends on: January 31st
First day of week: Monday
E.g.
Friday 30-Jan-2015 = Week 53, 2014
Saturday 31-Jan-2015 = Week 53, 2014
Sunday 01-Feb-2015 = Week 1, 2015
Monday 02-Feb-2015 = Week 2, 2015
...
Saturday 07-Feb-2015 = Week 2, 2014
Sunday 08-Feb-2015 = Week 2, 2015
Monday 09-Feb-2015 = Week 3, 2015
So in 2015, Week 1 will have one day only. This is the requirement.
I struggled to create a user defined function, but my head won't come up with any viable solutions, nor could I find this over the internet.
Any guidance will be appreciated!
Regards,
Mihai
November 3, 2015 at 11:02 am
Assuming that you have a calendar table, the following variation on a gaps and islands solution will work:
WITH retail_weeks AS (
SELECT rdt.retail_year, rdt.retail_dt
, ROW_NUMBER() OVER(PARTITION BY rdt.retail_year ORDER BY cal.dt, rdt.is_boundary DESC)
- DENSE_RANK() OVER(PARTITION BY rdt.retail_year ORDER BY cal.dt)
+ 1 AS retail_week,
rdt.is_boundary
FROM calendar cal
CROSS APPLY (
SELECT YEAR(DATEADD(MONTH, -1, cal.dt)), cal.dt, CAST(0 AS BIT)
UNION
SELECT YEAR(DATEADD(MONTH, -1, cal.dt)), cal.dt, 1
WHERE DATEPART(WEEKDAY, cal.dt) = 2
) AS rdt(retail_year, retail_dt, is_boundary)
)
SELECT *
FROM retail_weeks
WHERE retail_dt BETWEEN '2014-12-25' AND '2015-03-01'
AND is_boundary = 0
If you don't have a calendar table, you can use a CTE to create a calendar table on the fly. There are plenty of articles on how to do so.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 4, 2015 at 3:41 am
Just date and integer arithmetic version
create function retailWeekNumbers(@dt date)
returns table
as
return select
[Year]
,[Week] = datediff(day
,dateadd(day, (t1.ws - datepart(weekday,YStartDt) -7) % 7, YStartDt) -- first week start
,@dt) / 7 + 1
from (
select
-- requierments parameters
ysm = 2
,ws = datepart(weekday,cast('09-Feb-2015' as date)) -- monday, per regional settings
) as t1
cross apply(
select
[Year] = year(dateadd(month, 1-t1.ysm, @dt))
) as t2
cross apply(
select
YStartDt = datefromparts([Year], t1.ysm, 1)
) as t3
go
with tst as (
select top(30) dt=dateadd(day, row_number() over(order by (select null)), DATEFROMPARTS(2015, 1, 20))
from sys.all_objects
)
select dt, r.Year, r.Week
from tst
cross apply retailWeekNumbers(dt) r;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply