Datefirst format
-
Thanks for the info... I'm still waiting on data...
Jeff Moden wrote:Ok... I think I'm ready for ya! 😀 Post some readily consumable data in the form of a CREATE TABLE statement and some data that's coded to be inserted into that test table and we can put this problem to bed. 😉
Please see the article at the first link in my signature line below for why I ask for such a thing and one of many ways to pull it off.
--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) -
When I paste the OP's latest code into VS and run it it generates an incorrect calendar table. My interpretation of the OP's unresponsiveness amounts to "just make my kludge work, thanks". The OP has an incorrect calendar and would like the correct one. The OP did post data but in another thread (where I took a shot at a query before realizing the calendarization issue). The OP probably just assumes you're omniscient about what happens in other threads. It's not true? To fill in missing gaps between 2020W52 and 2021W02 it was unclear whether the missing element(s) is/are { 2020W53 , 2021W01 } or { 2021W01 }
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Ok... so no test data provided in a readily consumable format. The following code will help you do what you're trying to do. It shouldn't take much a a leap on your part to incorporate it into a solution. (see my next post after this one for a slightly different version that has a few additional columns
CREATE FUNCTION dbo.CalendarByDateFirst
/**********************************************************************************************************************
Purpose:
Returns whole-week dates for a given date range of one or more dates based on the given "@pDateFirst" parameter.
When a value of "1" (Monday) is used for "@pDateFirst", it returns the same week numbers according to ISO Rules and
does similar for any other starting day of the week. Note that Week #1 does NOT necessarily start on 01 January like
the WK date-part does.
-----------------------------------------------------------------------------------------------------------------------
Example usage:
--===== Syntax only
SELECT *
FROM dbo.CalendarByDateFirst(@pLoDate,@pHiDate,@pDateFirst) --No defaults assumed.
;
--===== Normal usage for "ISO" Monday Weeks
-- Use variable or literals.
SELECT *
FROM CalendarByDateFirst(@pLoDate,@pHiDate,1) -- "1" is Monday.
;
--===== Normal usage for weeks that start on Sundays, follow similar rules as ISO for week numbering but for Sundays.
-- Use variable or literals.
SELECT *
FROM CalendarByDateFirst(@pLoDate,@pHiDate,7) -- "7" is for "Sunday".
;
--===== Self-identifying weeks based on the setting of DATEFIRST.
-- Use variable or literals.
SELECT *
FROM CalendarByDateFirst(@pLoDate,@pHiDate,@@DATEFIRST) -- Language dependent! Be careful!
;
-----------------------------------------------------------------------------------------------------------------------
Dependencies:
This function uses as unit series generator function called "dbo.fnTally". That function can be downloaded from the
following URL: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
At the time of this writing, SQL Server 2022 RTM had not yet hit the streets and an RTM. When it does and when you
upgrade, it could be substituted for dbo.fnTally().
In the meantime, this will work in all versions of SQL Server from 2012 and on. It could be modified to work with
earlier versions but you really should upgrade. :D
-----------------------------------------------------------------------------------------------------------------------
Programmer's Notes:
Basically, all the info you may need is in the comments in the code. The results from this code can be used as a
Calendar Table or be used to create one, either of which can be used to solve a multitude of date/week related
problems including a typical "fill in the missing dates" problems.
If you don't like the names of the columns being used, change them!
For more information on how the formulas work, please see the following article.
https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
-----------------------------------------------------------------------------------------------------------------------
Disclaimer:
It's a shame that when someone provides something for free, that people think they can sue them. So, just to be up
front and although I've tested the heck out of it, I make NO claims as to the suitability for use of this code either
by itself or in the presence of other code. I also make no guarantees that it works 100%. I cannot and will not be
held liable in any way, shape, fashion, or form for any use, abuse, misuse, disuse, or claims of unsuitability no
matter the reason(s).
All of that liability rests on you.
Feel free to share this code without fee or cost with others under the same disclaimer. --Jeff Moden, 16 Jun 2022.
-----------------------------------------------------------------------------------------------------------------------
Credits:
Itzik Ben-Gan for the original idea of cCTEs (casading CTEs) that are present in the dbo.fnTally() function.
https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
t-clausen.dk for the original ISO function that forms the basis of this function.
https://stackoverflow.com/questions/7330711/isoweek-in-sql-server-2005
and, myself for explaining how it works.
https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 13 Jun 2022 - Jeff Moden
- Proof of principle code to solve the question asked at the following URL:
https://www.sqlservercentral.com/forums/topic/datefirst-format
Rev 01 - 16 Jun 2022 - Jeff Moden
- Formalize the code as a high performance iTVF.
**********************************************************************************************************************/--=====================================================================================================================
-- Define the I/O for this function.
--=====================================================================================================================
(
@pLoDate DATE --Any legal date >= 07 Jan 0001 and <= 24 Dec 9999.
,@pHiDate DATE --Any legal date >= 07 Jan 0001 and <= 24 Dec 9999 and >= @pLoDate.
,@pDateFirst TINYINT --Follows ISO/T-SQL rules where "1" is Monday thru "7" is Sunday. Use "1" for ISO.
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
cteDates AS
(--==== Create the range of desired dates according to @pLoDate and @pHiDate.
SELECT Date = DATEADD(dd,t.N,@pLoDate)
FROM dbo.fnTally(0,DATEDIFF(dd,@pLoDate,@pHiDate))t
)
,cteCalendarRows AS
(--===== Create one row for each date returned above.
SELECT Date = dd.Date
,DWs = DATENAME(dw,dd.Date) --Day of the week String
,SoW = DATEADD (dd,-3,wc.MidWK) --Start date of week
,EoW = DATEADD (dd,+3,wc.MidWK) --End date of week (Accountants like this)
,SnW = DATEADD (dd,+4,wc.MidWK) --Start date of next week (I prefer this for computing)
,YY# = DATEPART(yy,wc.MidWK) --Year as an INT
,MM# = DATEPART(mm,wc.MidWK) --The month of the "midweek" date.
,WK# = (DATEPART(dy,wc.MidWK)+6)/7 --Week # as an INT
FROM cteDates dd
CROSS APPLY (VALUES(DATEFROMPARTS(1,1,@pDateFirst),DATEFROMPARTS(1,1,@pDateFirst+3)))k(BaseDT,MidWkDT)
CROSS APPLY (VALUES(CONVERT(DATE,DATEADD(dd,DATEDIFF(dd,k.BaseDT,dd.Date)/7*7,k.MidWkDT))))wc(MidWK)
)--===== Return everything above and create an "ISO-like" week number with a year.
-- Personally, I don't use such a thing because there are more effecient methods but others do.
SELECT cal.Date
,cal.DWs
,cal.SoW
,cal.EoW
,cal.SnW
,cal.YY#
,cal.MM#
,cal.WK#
,YYYYwWK = CONVERT(CHAR(8),CONCAT(YY#,'W',RIGHT(100+WK#,2)))
FROM cteCalendarRows cal
;
GO--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) -
Any suggestions or thoughts.
- LearnSQL wrote:
Any suggestions or thoughts.
The function seems to generate the correct calendar. Nicely done Jeff 🙂
select * from dbo.CalendarByDateFirst('20211201', '20220131', 7);Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thanks a lot Jeff for your wonderful work.
-
Here's Rev 02 with a shedload more documentation in the flower box and 3 extra columns that seem useful to me. I think I may turn this into an article for SQLServerCentral.com soon. Thanks for the support folks.
CREATE FUNCTION dbo.CalendarByDateFirst
/**********************************************************************************************************************
Purpose:
*** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING ***
DO NOT EXTRACT INDIVIDUAL FORMULAS FROM THIS FUNCTION AND EXPECT THEM TO WORK FOR DATES
PRIOR TO 14 JAN 1900 AND EXPECT THEM TO WORK CORRECTLY. SOME MAY NOT.
*** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING **** WARNING ***
Generates a calendar from a given inclusive date range where the starting day of the weeks is also provided.
The calendar works in a fashion similar to what the ISOWK date-part would generate but any day of the week can be
specified as the starting day for weeks.
1. Every week starts on the same day of the week as identified by the @pDateFirst parameter.
2. Each week is assigned to a given YEAR or MONTH based on the 4th day of the week ("MidWkDt") because it inherently
is an indication of which year or month has the most days in the week for weeks that "straddle" the 1st of a year
or the first of a month. This also means that, unlike the WK date-part in SQL Server/T-SQL, the week numbers are
NOT reset to "1" on the 1st of January.
3. Similar to ISOWK, the number of weeks in a year will always be 52 or 53.
4. This code is meant to be used only with the Gregorian Calendar but will work with dates earlier than 1753 according
to what is currently offered in Microsoft's version of T-SQL starting in the 2008 version.
-----------------------------------------------------------------------------------------------------------------------
Parameters:
All of the following parameters are required. There are no defaults.
@pLoDate - The inclusive start of the date range to be returned.
May be any legal date >= 07 Jan 0001 and <= 24 Dec 9999.
@pHiDate - The inclusive end of the date range to be returned.
May be ny legal date >= 07 Jan 0001 and <= 24 Dec 9999 and >= @pLoDate.
@pDateFirst - The numeric starting day of the weeks.
- This follows the same standard of the DATEFIRST setting in SQL Server and the ISO_8601 Standard.
- "Monday" = 1 ... "Sunday" = 7.
-----------------------------------------------------------------------------------------------------------------------
Output:
This function returns its output as a contstucted table like a view would.
The following is the list of columns, datatypes, and brief description
Column DateType Description
-------- ------------ ---------------------------------------------------------------------------------------
Date DATE One of the dates in the series of dates that were generated.
DWs NVARCHAR(60) Day of the week String
DWi INT Numbered 1 to 7 from first day to last day of week.
SoW DATE Start date of week
EoW DATE End date of week (Accountants like this)
SnW DATE Start date of next week (usually preferred when "time" is present in computing)
YY# INT Year number the week is part of according to the "midweek" date.
QQ# INT Quarter number the week is part of according to the MM# column
MM# INT Month number the week is part of according to the "midweek" date.
WK# INT Week number of the year according to the "midweek" date.
YYYYwWK CHAR(8) An 8 byte concatenation of the YY#, the letter "W", and the WK#.
-----------------------------------------------------------------------------------------------------------------------
Example usage:
--===== Syntax only
SELECT *
FROM dbo.CalendarByDateFirst(@pLoDate,@pHiDate,@pDateFirst) --No defaults assumed.
;
--===== Normal usage for "ISO" Monday Weeks
-- Use variable or literals.
SELECT *
FROM CalendarByDateFirst(@pLoDate,@pHiDate,1) -- "1" is Monday.
;
--===== Normal usage for weeks that start on Sundays, follow similar rules as ISO for week numbering but for Sundays.
-- Use variable or literals.
SELECT *
FROM CalendarByDateFirst(@pLoDate,@pHiDate,7) -- "7" is for "Sunday".
;
--===== Self-identifying weeks based on the setting of DATEFIRST.
-- Use variable or literals.
SELECT *
FROM CalendarByDateFirst(@pLoDate,@pHiDate,@@DATEFIRST) -- Language dependent! Be careful!
;
-----------------------------------------------------------------------------------------------------------------------
Dependencies:
This function uses as unit series generator function called "dbo.fnTally". That function can be downloaded from the
following URL: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
Of course, you can substitute your favorite function for that, instead.
At the time of this writing, SQL Server 2022 RTM had not yet hit the streets as an RTM. When it does and when you
upgrade, the new GNERATE_SERIES() function could be substituted for dbo.fnTally(). See the folluwing URL for the
documentation for that function: https://docs.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql
In the meantime, this will work in all versions of SQL Server from 2012 and on. It could be modified to work with
earlier versions but you really should upgrade. :D
-----------------------------------------------------------------------------------------------------------------------
Programmer's Notes:
1. To reduce possible implicit conversions during use, No attempt was made to reduce storage size by altering the
datatypes of the output columns.
2. The results from this code can be used as an inline derived Calendar table or can be used to create a physical
Calendar table either in TempDB or as part of a database.
3. If you don't like the names of the columns or variables being used, change them!
4. Feel free to remove and add columns according to your specific needs.
-----------------------------------------------------------------------------------------------------------------------
Disclaimer:
It's a shame that when someone provides something for free, that people think they can sue them. So, just to be up
front and although I've tested the heck out of it, I make NO claims as to the suitability for use of this code either
by itself or in the presence of other code. I also make no guarantees that it works correctly. I cannot and will not
be held liable in any way, shape, fashion, or form for any use, abuse, misuse, disuse, or claims of unsuitability no
matter the reason(s). Neither can I be held responsible for someone else that copies the code and offers it for use
whether they attribute it to me or not.
All of that liability rests on you.
Feel free to share this code without fee or cost to others under the same disclaimer.
-- Jeff Moden, 16 Jun 2022.
-----------------------------------------------------------------------------------------------------------------------
Credits:
Itzik Ben-Gan for the original idea of cCTEs (casading CTEs) that are present in the dbo.fnTally() function.
https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
t-clausen.dk (who started the post) and others for the original ISO function that forms the basis of this function.
https://stackoverflow.com/questions/7330711/isoweek-in-sql-server-2005
An additional explanation for how the basic principle of the code works can be found at the following link.
https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 13 Jun 2022 - Jeff Moden
- Proof of principle code to solve the question asked at the following URL:
https://www.sqlservercentral.com/forums/topic/datefirst-format
Rev 01 - 16 Jun 2022 - Jeff Moden
- Formalize the code as a high performance iTVF.
Rev 02 - 16 Jun 2022 - Jeff Moden
- Add MM# (month number) QQ# (quarter number) and DWi (day of week computed value) to the output.
- Add additional documentation especially to the flower box.
**********************************************************************************************************************/ (--===== Input Parameters
@pLoDate DATE
,@pHiDate DATE
,@pDateFirst TINYINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
cteDates AS
(--==== Create the range of desired dates according to @pLoDate and @pHiDate.
SELECT Date = DATEADD(dd,t.N,@pLoDate)
FROM dbo.fnTally(0,DATEDIFF(dd,@pLoDate,@pHiDate))t
)
,cteCalendarRows AS
(--===== Create one row with calendare related information for each date returned above.
SELECT Date = dd.Date
,DWs = DATENAME(dw,dd.Date) --Name of the day of the week as a Unicode String.
,SoW = DATEADD (dd,-3,wc.MidWK) --Start date of week.
,EoW = DATEADD (dd,+3,wc.MidWK) --End date of week (Accountants like this).
,SnW = DATEADD (dd,+4,wc.MidWK) --Start date of next week (I prefer this for computing).
,YY# = DATEPART(yy,wc.MidWK) --Year number of the week based on MidWk date.
,QQ# = DATEPART(qq,wc.MidWK) --Quarter number of the week based on MidWk date.
,MM# = DATEPART(mm,wc.MidWK) --Month number of the week based on MidWk date.
,WK# = (DATEPART(dy,wc.MidWK)+6)/7 --Week # number of the week based on MidWk date.
FROM cteDates dd
CROSS APPLY (VALUES(DATEFROMPARTS(1,1,@pDateFirst),DATEFROMPARTS(1,1,@pDateFirst+3)))k(BaseDT,MidWkDT)
CROSS APPLY (VALUES(CONVERT(DATE,DATEADD(dd,DATEDIFF(dd,k.BaseDT,dd.Date)/7*7,k.MidWkDT))))wc(MidWK)
)--===== Return everything above plus any dependent calculated columns.
-- This helps keep the code as "DRY" as possible.
-- Note that DWi was calculated in such a fashion as to make it completely independent of the DATEFIRST
-- configuration setting or any language dependancies.
-- The "odd" spacing of the column names is to make a vertical Copy'n'Paste of all the column names possible.
SELECT cal.Date
,cal.DWs
, DWi = DATEDIFF(dd,cal.SoW,cal.Date)+1 -- Numbered 1 to 7 from first day to last day of week.
,cal.SoW
,cal.EoW
,cal.SnW
,cal.YY#
,cal.QQ#
,cal.MM#
,cal.WK#
, YYYYwWK = CONVERT(CHAR(8),CONCAT(YY#,'W',RIGHT(100+WK#,2))) --"ISO-like" week number with a year.
FROM cteCalendarRows cal
;
GO--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) -
As a bit of a sidebar, man!... The supposed SQL Format for the code windows is absolutely horrible! 🙁 I wish they'd do something about it.
--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) - Steve Collins wrote:LearnSQL wrote:
Any suggestions or thoughts.
The function seems to generate the correct calendar. Nicely done Jeff 🙂
select * from dbo.CalendarByDateFirst('20211201', '20220131', 7);LearnSQL wrote:Thanks a lot Jeff for your wonderful work.
Thanks to both of you and thanks for the test, Steve. I appreciate it.
LearnSQL wrote:Any suggestions or thoughts.
Yes... post readily consumable data as a part of your original post in the future and you'll get good, solid answers much more quickly and without having to play "20 questions" and without exasperating the people that are trying to help you... especially those of us that have asked for the readily consumable data more than once 😀
--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) -
Not to could/would come up with it to begin with. Refactored to remove the CTE's. The test returns no rows
DROP FUNCTION IF EXISTS dbo.CalendarByDateFirstRefactored;
go
CREATE FUNCTION dbo.CalendarByDateFirstRefactored
(
@pLoDate DATE --Any legal date >= 07 Jan 0001 and <= 24 Dec 9999.
,@pHiDate DATE --Any legal date >= 07 Jan 0001 and <= 24 Dec 9999 and >= @pLoDate.
,@pDateFirst TINYINT --Follows ISO/T-SQL rules where "1" is Monday thru "7" is Sunday. Use "1" for ISO.
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT Date = dt.Date
,DWs = DATENAME(dw,dt.Date) --Day of the week String
,SoW = DATEADD (dd,-3,wc.MidWK) --Start date of week
,EoW = DATEADD (dd,+3,wc.MidWK) --End date of week (Accountants like this)
,SnW = DATEADD (dd,+4,wc.MidWK) --Start date of next week (I prefer this for computing)
,vc.YY# --Year as an INT
,MM# = DATEPART(mm,wc.MidWK) --The month of the "midweek" date.
,vc.WK# --Week # as an INT
,YYYYwWK = CONVERT(CHAR(8),CONCAT(vc.YY#,'W',RIGHT(100+vc.WK#,2)))
FROM dbo.fnTally(0,DATEDIFF(dd,@pLoDate,@pHiDate)) fn
CROSS APPLY (VALUES(DATEADD(dd,fn.N,@pLoDate)))dt([Date])
CROSS APPLY (VALUES(DATEFROMPARTS(1,1,@pDateFirst),DATEFROMPARTS(1,1,@pDateFirst+3)))k(BaseDT,MidWkDT)
CROSS APPLY (VALUES(CONVERT(DATE,DATEADD(dd,DATEDIFF(dd,k.BaseDT,dt.Date)/7*7,k.MidWkDT))))wc(MidWK)
CROSS APPLY (VALUES(DATEPART(yy,wc.MidWK),(DATEPART(dy,wc.MidWK)+6)/7))vc(YY#,WK#)
;
GO
select * from dbo.CalendarByDateFirst('20211201', '20220131', 7)
except
select * from dbo.CalendarByDateFirstRefactored('20211201', '20220131', 7)Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thanks for the code modifications, Steve. I'll take a deeper dive than just looking at it. Nice to see someone actually test both out a bit.
In the past, I've shyed away from replacing CTEs with CROSS APPLYs because of some performance issues in a couple of pieces of code. Unfortunately, I've not gone back for a deeper dive on such cascading CROSS APPLYs. Your good post reminds me that I actually need to go back for a deeper dive on those. So, MUCH appreciated for what you've done.
On that same note, I was doing some experimentation on the function I posted and was a bit ticked to see some implicit cast warnings in the Actual Execution plan around the two dates that really should be treated as if they were constants. Just on a total SWAG, I made that an OUTER APPLY and the warnings went away. When I changed them back to CROSS APPLY, the warning returned. Of course, it also changed the execution plan a bit and I've not yet done a performance test to see if getting rid of the warnings by using OUTER APPLY is a good thing or not.
It would be nice if MS came out with a better way to apply "constants" in iTVFs. It may also be that moving those two calculations to the SELECT might bless them as "run time constants" (which the OUTER APPLY seemed to do) but it would totally undo the DRYness of the code, which is also valuable.
--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) -
I'm also exploring a different method using the following formula for finding the week start, midweek, and end dates (the 1 needs to be changed to a 4 and 7 respectively) code where it's desired that DATEFIRST identifies the real start day of the week.
CONVERT(DATE,DATEADD(dd,1-DATEPART(dw,SomeDT),SomeDT))
The reason why is because it means that we don't need to find the offset days and can short-cut some other code. Perhaps that will also provide a bit of performance improvement. We'll find out.
--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) -
No joy on the "new" formula. It has about the same performance for the DATETIME datatype and is 50% slower for the DATE datatype. It's also not "symmetrical" with how the start of other periods are determined so I'm abandoning it in-place except maybe to demonstrate that "different" doesn't always mean "better".
--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) -
I'm doing some more checking but, just to be on the safe side, I added a warning to the flower box on the function I posted.
--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,
Sorry to get back you after long time.
Is there possible to get next week data as well. Since this needs to be prolonged until last week of current date.
create table #datatab(
EmpNo INT NOT NULL
,WeekNo CHAR(8) NOT NULL
,Rnge SMALLINT NOT NULL);
insert #datatab(EmpNo, WeekNo, Rnge) values
(111, '2021W50', 50)
,(111, '2021W51', 51)
,(111, '2021W52', 52)
,(111, '2022W02', 10)
,(111, '2022W04', 17)
,(210, '2022W01', 22)
,(210, '2022W04', 55)
,(210, '2022W08', 15)
,(210, '2022W09', 26);
--Select * from #datatab_arch
;with
lead_cte as (select *, lead(WeekNo) over(partition by EmpNo order by WeekNo) lead_wk from #DataTab d)
select l.*, nxt.nxt_wk, cal.YYYYwWk, expanded.CalcRnge,
lag(expanded.CalcRnge, 1, 0) over (partition by l.empno order by cal.YYYYwWk) NewRnge
from lead_cte l
cross apply (select top(1) cd.YYYYwWk from dbo.CalendarByDateFirst('20211201', '20221231', 7) cd
where cd.YYYYwWK>l.WeekNo order by cd.YYYYwWK) nxt(nxt_wk)
cross apply (select distinct YYYYwWK from dbo.CalendarByDateFirst('20211201', '20221231', 7) cd
where cd.YYYYwWK >= l.WeekNo and cd.YYYYwWK<coalesce(l.lead_wk, nxt.nxt_wk)) cal(YYYYwWk)
cross apply (values (iif(l.WeekNo=cal.YYYYwWk, l.Rnge, 0))) expanded(CalcRnge)
order by l.EmpNo, cal.YYYYwWk;
Is it is possible to get one week extra data as shown below even if there is no input data for the corresponding weeks(based on the max(current week of current date) ) as shown below. Current week is W35.
Thanks!
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply