March 29, 2019 at 8:31 am
Hi All
I'm trying to pull out a week number but instead of using the default 01-Jan, I want to use a custom year and start date (in my case the year runs from 01-Aug to 31-Jul). Therefore 01-Aug becomes week number 1.
Anyone have a clue on how to do that?
March 29, 2019 at 8:55 am
There might be more elegant way of doing but one thing that comes to mind is that you can get a week number the usual way for a week before 01- Aug (select datepart(week, date)) - - then you can subtract that number from the week numbers after 1st august
The constant to be subtracted will also depend on which day you want to be 1st day of week - for e.g. 1st Aug 2018 is Wednesday - so do you want to start your week from Wednesday ...
March 29, 2019 at 9:01 am
One option might be to normalize all your dates back to starting on 1/1 then just use DATEDIFF week, there's some trickier here since you'd have to account for leap years so you'd probably want to normalize forward.
You could also look at the number of days and / by 7.
March 29, 2019 at 9:08 am
Aye, I started with getting the datepart week for 01-Aug and subtracting or adding as required but the leap years got me, and of course the standard week number also ticks forward based on the datefirst setting (1 in my case). I'm happy for it to follow the same guide as standard week number, therefore 01-Aug is always 1, but the following monday becomes 2.
March 29, 2019 at 11:26 am
;WITH
cteCalcBaseDates AS (
SELECT DATEADD(MONTH, 7 - CASE WHEN MONTH(GETDATE()) < 8 THEN 12 ELSE 0 END,
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS Aug01,
DATEADD(DAY, -1, DATEADD(MONTH, 19 - CASE WHEN MONTH(GETDATE()) < 8 THEN 12 ELSE 0 END,
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))) AS Jul31
),
cteCalcBaseMonday AS (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, Aug01) % 7, Aug01) AS FirstMonday
FROM cteCalcBaseDates
),
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS week#
FROM cteTally10 c1 CROSS JOIN cteTally10 c2
)
SELECT DATEADD(DAY, 7 * (week# - 1), FirstMonday) AS MondayDate, week#
FROM cteCalcBaseDates
CROSS JOIN cteCalcBaseMonday
INNER JOIN cteTally100 t ON t.week# BETWEEN 1 AND 53
WHERE DATEADD(DAY, 7 * (week# - 1), FirstMonday) <= Jul31
ORDER BY t.week#
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 29, 2019 at 12:14 pm
ldanks - Friday, March 29, 2019 8:31 AMHi AllI'm trying to pull out a week number but instead of using the default 01-Jan, I want to use a custom year and start date (in my case the year runs from 01-Aug to 31-Jul). Therefore 01-Aug becomes week number 1.
Anyone have a clue on how to do that?
Will the custom year always start on the same specified date? If so it may make sense to have some sort of calendar table or date dimension. If the custom year is dynamic depending on run time parameters of a report, then you'd have to calculate it on the fly, maybe with a ROW_NUIMBER() function.
March 29, 2019 at 1:16 pm
declare @start date = '2018/08/01'
;with tally (N) as (select top 366 row_number() over(order by (select null)) from sys.columns c1 cross join sys.columns c2)
select Dateadd(day,N-1,@start) as _Date, (n-1)/7+1 as Weekno
from tally
where Dateadd(day,N-1,@start) < dateadd(year,1,@Start)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 2, 2019 at 7:56 am
Thank you all for the ideas and the work! There is brilliance here methinks LOL.
I can just about work out how Dixie's Tally works, and Scott's just breaks my brain trying to break it down so see how it does the magic, but it does give the week numbers I need! And you're right Chris, an additional column on a calendar table is route I think - which would mean using Scott's logic to a table more like Dixie's!
April 2, 2019 at 7:58 am
If you work out what the last Monday was, and what the first Monday in August was, you can do a datediff in weeks to get the week number There’s some useful tips on date arithmetic here.
John
That's an excellent link John - brain busting for me, but definitely going to be useful, thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply