0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for scholars and enthusiasts of literature

SET DATEFIRST 1;
DECLARE @from_date date
DECLARE @to_date date;
DECLARE @noModificationYears nvarchar(max);
SET @from_date = '2020-01-01';
SET @to_date = '2020-12-31';
;with CTE As  
		(  
			SELECT DATEADD(day, 0, @from_date) AS the_date
			UNION ALL  
			SELECT DATEADD(day, 1, the_date) AS TheDate FROM CTE  
			WHERE the_date < @to_date

		)
		SELECT 
			@noModificationYears = (
				SELECT 
                CONCAT(',',DATEPART(year, the_date), ',')
				FROM CTE GROUP BY DATEPART(week, the_date), DATEPART(year, the_date) 
					HAVING COUNT(DATEPART(year, the_date)) = 7 AND DATEPART(week, the_date) = '1' 
					FOR XML PATH('')
			)
		OPTION (MAXRECURSION 0);
Select @noModificationYears as years;
Select count(@noModificationYears) as years1;
SELECT CHARINDEX(cast((DATEPART(year, @from_date) - 1) as varchar(8000)), @noModificationYears)

;with CTE1 As  
(  
    Select DATEADD(day, 0, @from_date) as TheDate, 'D' as PERIND
    UNION ALL  
    Select DATEADD(day, 1, TheDate) as TheDate, 'D' as PERIND from CTE1  
    where TheDate < @to_date
)
,CTE2 As
        (
        Select c1.TheDate, 'D' as PERIND from CTE1 c1
        UNION ALL
        Select DATEADD(day, 0, c2.TheDate) as TheDate, 'W' as PERIND from CTE1 c2
        )
SELECT
    c1.TheDate AS Date,
    DATEPART(year, c1.TheDate) AS DateYear,
    DATEPART(month, c1.TheDate) AS DateMonth,
    DATEPART(week, c1.TheDate) as DateWeek,
    datepart(year, dateadd(dd, -datepart(dw, c1.TheDate) + 1, c1.TheDate)) as CalculatedWeekYear,
    CASE 
      WHEN
        CHARINDEX(cast(DATEPART(year, TheDate) as varchar(8000)), @noModificationYears) > 0
      THEN
        DATEPART(week, c1.TheDate)
      ELSE
        CASE
          WHEN
            DATEPART(week, c1.TheDate) != 1
          THEN
            DATEPART(week, c1.TheDate) - 1
          ELSE
            CASE
              WHEN
              CHARINDEX(cast((DATEPART(year, TheDate) - 1) as varchar(8000)), @noModificationYears) = 0 
              OR
              CHARINDEX(cast((DATEPART(year, @from_date) - 1) as varchar(8000)), @noModificationYears) = ''
              THEN
                DATEPART(week, CONCAT(datepart(year, TheDate) - 1, '-12-31')) - 1
              ELSE
                DATEPART(week, CONCAT(datepart(year, TheDate) - 1, '-12-31'))
            END
        END
      END AS CalculatedWeek
FROM CTE2 AS c1
option (maxrecursion 0);

fork query

edited aug 6

created aug 1 anonymous