SQL Server Add current week flag column

qyswt5oh  于 2023-04-10  发布在  其他
关注(0)|答案(3)|浏览(135)

How can I efficiently and elegantly add current week flag column using SQL query as shown in the picture attached. It has to be True or 1 for the week TODAY falls under. I was thinking of FIRST_VALUE and CASE . Am I in the right track?

n9vozmp4

n9vozmp41#

If your weeks start on Sundays you can choose an early Saturday that doesn't appear in your data and count the whole weeks (7days) since that date:

case when datediff(day, cast('1889-12-29' as date), getdate())/7
           =
         datediff(day, cast('1889-12-29' as date), [date])/7
    then 1 
    else 0 
end
wd2eg0qa

wd2eg0qa2#

You can use DATEPART to get the week number:

SELECT IIF(DATEPART(WEEK, GETUTCDATE()) = DATEPART(WEEK, '2023-04-01'), 1, 0)
      ,IIF(DATEPART(WEEK, GETUTCDATE()) = DATEPART(WEEK, '2023-04-03'), 1, 0)
2wnc66cl

2wnc66cl3#

This is a recursive example of using DATEPART() . The difference here is it runs DATEPART() only for that week, not your entire dataset. There is an inferred problem of identifying the same week (number) for the same year, and your sample data contains different years. The easiest way I can think of is to identify the MAX and MIN date for that week/year. I use Common Table Expressions (CTEs) and recursion below:

DECLARE @today date = GETDATE(); 
DECLARE @weeknum int = DATEPART(WEEK, @today);
DECLARE @mindate date;
DECLARE @maxdate date;

WITH CTE_date_up AS ( 
 SELECT @today as [dateVal] --base case
 UNION ALL --repetitive case of recursion going up
 SELECT DATEADD(DAY, 1, [dateVal])       
   FROM CTE_date_up
  WHERE DATEPART(WEEK, DATEADD(DAY, 1, [dateVal])) = @weeknum
)
,CTE_date_down AS ( 
 SELECT @today as [dateVal] --base case
 UNION ALL --repetitive case of recursion going down
 SELECT DATEADD(DAY, -1, [dateVal])
   FROM CTE_date_down
  WHERE DATEPART(WEEK, DATEADD(DAY, -1, [dateVal])) = @weeknum
)
,CTE_Select as ( --combining the results, and removing the startdate duplicate with UNION
SELECT * FROM CTE_date_up UNION SELECT * FROM CTE_date_down
)

--Setting the @mindate and @maxdate variables
SELECT @mindate = MIN(dateVal)
      ,@maxdate = MAX(dateVal)
  FROM CTE_Select

--At this point @mindate and @maxdate are saved to variables
--In Your query, use the variables in a case statement, ex.
SELECT [date], [week num]
      ,[current week flag] = CASE WHEN [date] BETWEEN @mindate AND @maxdate THEN 1 ELSE 0 END
  FROM mytable

相关问题