week startdate and enddate
-
hi,
looking for script to populate all the weeks in current year like below.
Any suggestions please.
-
Merhaba,
Asagidaki sekilde sanirim istediginiz olacaktir.
DECLARE @BASLAMA VARCHAR(11),@BITIS VARCHAR(11)
SET @BASLAMA = '01.01.2019'
SET @BITIS = '31.12.2019'
;WITH CTE AS
(
SELECT
CONVERT(DATETIME,@BASLAMA,104) AS Tarih
UNION ALL
SELECT
CONVERT(DATETIME,CTE.Tarih,104) + 1
FROM
CTE
WHERE
Tarih < CONVERT(DATETIME,@BITIS,104)
)
SELECT
YEAR(Tarih) AS Yil,
DATEPART(week,Tarih) AS Hafta,
MIN(Tarih) AS [Haftanin Ilk Gün],
MAX(Tarih) AS [Haftanin Son Gün]
FROM CTE
GROUP BY
YEAR(Tarih),DATEPART(week,Tarih)
ORDER BY 1,2
option (maxrecursion 0)Allah bize yeter, O ne güzel vekildir.
vedatoozer@gmail.com
- adisql wrote:
hi,
looking for script to populate all the weeks in current year like below.
Any suggestions please.
How often do you need to do this? Just once per year or???
--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) -
One key thing to note is that you only need to calc the first Sunday date. For future dates, you simply add 7 day intervals to that date. For example:
DECLARE @year date;
SET @year = GETDATE();
--SET @year = '20180101' /*hard-code to force different year if desired*/
;WITH
tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
tally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS weekno
FROM tally10 t1
CROSS JOIN tally10 t2
)
SELECT YEAR(jan_01) AS Year,
t.WeekNo,
REPLACE(CONVERT(varchar(9), DATEADD(DAY, (t.WeekNo - 1) * 7, first_sunday),
6), ' ', '-') AS WeekStart,
REPLACE(CONVERT(varchar(9), DATEADD(DAY, (t.WeekNo - 1) * 7 + 6, first_sunday),
6), ' ', '-') AS WeekEnd
FROM (
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 0) AS jan_01
) AS calc_jan_01
CROSS APPLY (
SELECT DATEADD(DAY, -DATEDIFF(DAY, 6, jan_01) % 7, jan_01) AS first_sunday
) AS calc_back_up_to_first_sunday
INNER JOIN tally100 t ON t.weekno BETWEEN 1 AND 53
WHERE DATEADD(DAY, (t.WeekNo - 1) * 7 + 6, first_sunday) < DATEADD(YEAR, 1, jan_01)
ORDER BY t.weeknoSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Speedy Pletcher beat me to it... More details about the requirement are in order. Here's my solution:
DECLARE @startDate DATE = '20181230', @endDate DATE = '20191230';
WITH
E1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(N)),
iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM E1 a, E1 b, E1 c)
SELECT TOP (DATEDIFF(WW,@startDate,@endDate)+1)
[Year] = YEAR(sd.Dt),
WeekNumber = t.N,
WeekStart = sd.Dt,
WeekEnd = DATEADD(DD,6,sd.Dt)
FROM iTally AS t
CROSS APPLY (VALUES(DATEADD(WW,-1,@startDate))) AS f(SD)
CROSS APPLY (VALUES(DATEADD(WW,t.N,f.SD))) AS sd(Dt)
ORDER BY WeekNumber;"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Some of the code requires knowing dates instead of generating them based on the current year like the OP posted.
Some of the code won't handle things if the given or GETDATE() is an edge case. For example, 30 and 31 Dec 2018 are actually a part of the first week of 2019 if I'm understanding the implied requirements (and I agree that we DO need clarification).
Other code posted so far uses a Recursive CTE and calculates a lot more days that actually needed before grouping for the final output and, I believe, the first and last weeks don't end up being whole weeks except by chance.
That being said, here's some code that does handle the edge cases. I didn't "DRY" it out all the way because I wanted clarity as well as performance.
DECLARE @Date DATETIME = '30 DEC 2018' --Actually a part of the first week of 2019. Change to GETDATE() if you want it automatic.
;
WITH cteWeekSpanDates AS
(--==== Determine the year of the Sunday of or before the given date and the Saturday that follows.
SELECT SundayDateYear = DATENAME(yy,DATEADD(dd,DATEDIFF(dd,-1,@Date)/7*7 ,-1))
,NextSaturdayYear = DATENAME(yy,DATEADD(dd,DATEDIFF(dd,-1,@Date)/7*7+6,-1))
)
, cteFirstOfYear AS
(--==== If the two years are equal, the week of the given date CANNOT be the start of the next year and SundayDateYear is the
-- target year. Otherwise, the week IS the start of the next year and we need to use NextSaturdayYear year as the target year.
SELECT FirstOfYear = DATEADD(yy,0,CASE WHEN SundayDateYear=NextSaturdayYear THEN SundayDateYear ELSE NextSaturdayYear END)
,FirstOfNextYear = DATEADD(yy,1,CASE WHEN SundayDateYear=NextSaturdayYear THEN SundayDateYear ELSE NextSaturdayYear END)
FROM cteWeekSpanDates
)
, cteStartSunday AS
(--==== Calculate the Sunday of the first week of the target year and next year
SELECT [Year] = YEAR(FirstOfYear)
,YearStartSunday = DATEADD(dd,DATEDIFF(dd,-1,FirstOfYear) /7*7,-1)
,NextYearStartSunday= DATEADD(dd,DATEDIFF(dd,-1,FirstOfNextYear)/7*7,-1)
FROM cteFirstOfYear
)
, cteBuildStartSundays AS
(--==== Build all of the week start dates and week numbers for the target year.
SELECT s.[Year]
,WeekNo = t.N+1
,WeekStart = DATEADD(dd,t.N*7,s.YearStartSunday)
FROM cteStartSunday s
CROSS APPLY dbo.fnTally(0,DATEDIFF(dd,YearStartSunday,NextYearStartSunday)/7-1) t
)--==== Calculate the date of the end of the week and a bonus column for each week and display it all
SELECT [Year]
,WeekNo
,WeekStart = REPLACE(CONVERT(CHAR(9),WeekStart,6),' ','-')
,WeekEnd = REPLACE(CONVERT(CHAR(9),DATEADD(dd,6,WeekStart),6),' ','-')
,NextWeekStart = REPLACE(CONVERT(CHAR(9),DATEADD(dd,7,WeekStart),6),' ','-') --Bonus column that I'd use instead of "WeekEnd"
FROM cteBuildStartSundays
;--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, function dbo.fnTally is a custom one, I suppose.
Could you provide a link to its definition?
- Andrey wrote:
Jeff, function dbo.fnTally is a custom one, I suppose.
Could you provide a link to its definition?
My apologies. I forgot to add that. I've attached it. And I continue to think it odd that an SQL Forum won't allow one to attach a .SQL file nor does it allow you to undo an uploaded file mistake.
--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)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply