How to calculate Work Week based on the Next logic
-
Hi All,
I would like to calculate the work week starting from the Sunday of the week in which the 01/01 of the year falls in.
For example:
01/01/2021 is Friday, so WW1 is the all week starting from Sunday 27/12/2020 to 02/01/2021
Any ideas for the efficient way of doing it?
Thanks,
Amir -
Quick thought, would that be the 53rd week of each year unless the 31st of December falls on Saturday?
😎 -
Thanks for your insight ! that was fast 🙂
so would this script be the most efficient
case
when DATEPART(WEEK,Cal_Date)=53 and DATENAME(WEEKDAY,DATEADD(yy, DATEDIFF(yy, 0, Cal_Date) + 1, -1))<>'Saturday'
then 1
else DATEPART(WEEK,Cal_Date)
end as Work_Week -
Removed this post because I didn't read the rules requested well enough.
--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - amir.kachlon - Sunday, February 17, 2019 8:12 AM
I believe the following will do it for you for every week that the DATETIME datatype can handle except for dates < 07 Jan 1753 or dates > 25 Dec 9999.
SELECT Work_Week = (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'07 Jan 1753',SomeColumn)/7*7+6,'07 Jan 1753'))+6)/7
FROM dbo.SomeTable--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Thanks for your answer.
Wow incredible math equation, it actually works perfect !
Thanks 🙂
- amir.kachlon - Monday, February 18, 2019 1:42 AM
Thanks for the feedback. While the calculation may look complicated, all it does is find the previous Sunday for any given date and adds 6 to that to find the Saturday at the end of that week. Then, it counts the number of days since the first of the year for that Saturday date and does a Mod 7 to come up with the Week Number for the year. It's very similar to the ISO week calculation found in the following article, which has a much deeper explanation of the parts of the formula if you're interested...
http://www.sqlservercentral.com/articles/T-SQL/97910/--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, February 18, 2019 7:41 AM
Thanks for the link Jeff, I didn't find the article when I first responded to the OP
😎One thought, I like to use the number -5369 rather than the implicit conversion implied by using '07 Jan 1753'
- Eirikur Eiriksson - Monday, February 18, 2019 8:16 AM
I know you probably just phat phingered it but the correct number would be -53684 at the day level.
Proof:
SELECT CONVERT(INT,CONVERT(DATETIME,'07 Jan 1753'));--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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, February 18, 2019 9:39 AM
Almost there but, applied to the equation, one has to cancel out the 0, hence -5369 instead of -5368
😎 - Jeff Moden - Monday, February 18, 2019 9:39 AM
Ran it against this data set
😎
USE TEEST;
GO
SET NOCOUNT ON;--https://www.sqlservercentral.com/Forums/2020451/How-to-calculate-Work-Week-based-on-the-Next-logic
--/* -- UNCOMMENT THIS LINE TO SKIP THE TEST DATASET CREATION
IF OBJECT_ID(N'dbo.TBL_TEST_FIRST_WORK_WEEK') IS NOT NULL DROP TABLE dbo.TBL_TEST_FIRST_WORK_WEEK;
CREATE TABLE dbo.TBL_TEST_FIRST_WORK_WEEK
(
TFWW_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_FIRST_WORK_WEEK_TFWW_IDD PRIMARY KEY CLUSTERED
,TTFWW_DATE DATETIME NOT NULL
);DECLARE @TEST_SIZE INT = 1000;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (@TEST_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,TDFSET(TDATE) AS
(
SELECT
DATEADD(DAY,365 - (ABS(CHECKSUM(NEWID())) % 7),DATETIMEFROMPARTS((1899 + (ABS(CHECKSUM(NEWID())) % 119)),1,1,0,0,0,0))
FROM NUMS NM
)
INSERT INTO dbo.TBL_TEST_FIRST_WORK_WEEK WITH (TABLOCKX) (TTFWW_DATE)
SELECT
TFS.TDATE
FROM TDFSET TFS
OPTION (MAXDOP 1);
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply